HomeClarion Live! UniversityUsing Dummy Tables with MSSQL Driver
 
  • PDF

Using Dummy Tables with MSSQL Driver

Mark Sarson: Can I do dummy tables with the MSSQL driver?
Think I have it working now, was supposed to put /TURBOSQL=TRUE
Thanks all

Arnor Baldvinsson: Mark:  You only need that on the dummy table.  What it does is that it bypasses the process of testing if the file exists on the server.  Here is an example from a project I'm working on:

PBLastDates             FILE,DRIVER('MSSQL','/TURBOSQL=True'),Pre(PBLD)
Record                    RECORD,PRE()
StartDT                     STRING(8)
StartDTGroup                GROUP,OVER(StartDT)
StartDate                     DATE
StartTime                     TIME
                            END
EndDT                       STRING(8)
EndDTGroup                  GROUP,OVER(EndDT)
EndDate                       DATE
EndTime                       TIME
                            END
BillDT                      STRING(8)
BillDTGroup                 GROUP,OVER(EndDT)
BillDate                      DATE
BillTime                      TIME
                            END
SysID                       Long
                          END
                        END

To access this you just do:

Open(PBLastDates)
PBLastDates {PROP:SQL} = TheSQLQueryString
Loop
  Next(PBLastDates)
  If ErrorCode()
    Break
  End
  ...
End
Close(PBLastDates)

The PROP:SQL needs to match the file and in your case if you have just one field, then you just use thatSmile 

You can declare this in the dct or in procedures or classes or whatever.  Very powerful.

Here is a sample code using this file structure in a class method:

SQL  CString(1025)
Ret  Byte
 Code
 Create(PBLastDates)
 Open(PBLastDates)
 SQL = 'SELECT ProBegDate, ProEndDate, BillingDate, SysID FROM dbo.PropertyBilling WHERE prop_ID = "' & pPropID & '" AND SysID = ' & pPeriodID & ' ORDER BY TranDate ASC'
 SQL = SELF.TranslateSQL(SQL)
 PBLastDates {PROP:SQL} = SQL
 Next(PBLastDates)
 If Not ErrorCode()
   pStartDate = PBLD:StartDate
   pEndDate   = PBLD:EndDate
   If Not Omitted(pBillingDate)
     pBillingDate = PBLD:BillDate
   End
  
   Ret = True
 Else
   pStartDate = Date(1,1,1900)
   pEndDate   = Date(1,1,1900)
   If Not Omitted(pBillingDate)
     pBillingDate = Date(1,1,1900)
   End
   Ret = False
 End
 Close(PBLastDates)
 Return(Ret)
Joomla Templates: from JoomlaShack