The /TURBOSQL driver string for the SQL drivers in Clarion is probably one of the least used of the very powerful features that Clarion has! I've seen very few developer mention it. I have been using it for several years and it is extremely powerful. With it you can declare a table structure in your procedure with the /TURBOSQL=TRUE attribute and fill it with data from a SELECT statement with only a few lines of code. It works with all the SQL drivers except Oracle, including the ODBC driver.
First you need to declare the file. I use the procedure data embed to do that. Here is an example of a table used to get data from a MSSQL database.
!! TurboSQL table declaration, using MSSQL driver
TurboSQLTable FILE,DRIVER('MSSQL','/TURBOSQL=True'), pre(TurboSQL)
Record RECORD
Variable CSTRING(4)
DailyDT STRING(8)
DailyDTGroup GROUP,OVER(DailyDT)
DailyDTDate DATE
DailyDTTime TIME
END
END
END
Note that the /TURBOSQL must be set to True, not 1. If you are accessing a column with DATETIME data you need to use a String(8) and Group,Over like you do in normal Clarion file declarations. If you are accessing data from DATE or TIME columns in the database, you can just use DATE or TIME data types in Clarion.
Now we can create the table - which happens only in memory - and access the data.
Code
!! Code to access TurboSQL table
Open(TurboSQLTable)
Buffer(TurboSQLTable,100)
TurboSQLTable {PROP:SQL} = 'SELECT ... FROM ' & Name(TableName) &|
' WHERE ...
If ErrorCode()
Message('Error in PROP:SQL (' & ErrorCode() & ') ' & Clip(Error()) & Choose(ErrorCode()=90,|
'||Server error (' & FileErrorCode() & ') ' & Clip(FileError(),''), ICON:Exclamation)
Return
End
Loop
Next(TurboSQLTable)
If ErrorCode()
Break
End
End
Close(TurboSQLTable)
This is a very simple example an dyou may need to tweak the Buffer() statement to find the best performance, but for me 100 has been a good number to start with. The SELECT statement must match the columns that you declared in the TURBOSQL table structure or you will get SQL errors about truncated columns or some other type mismatch errors.
This method of data retrival is very fast! I have tested this against both queues and IMDD tables and the queues tend to be a bit faster. Since IMDD has to keep track of sorting etc. it is a little bit slower, but if performance is critical for you, test both. One may perform better under some circumstances where the other works better in other situations. This allows you to set up complex queries and present the results very quickly.
Addendum, October 7, 2013
The Create() statement is superfluous and should not be used. Even with the /TURBOSQL driver string it seems that CREATE will create the file in the back end database as a regular table if the CREATE attribute is present on the file structure.
6 Comments