Using TURBOSQL driver string for quick SQL data access

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.  

Have more questions? Submit a request

6 Comments

  • 0
    Avatar
    Colin Wynn

    Been using this myself this past week, very powerful :)

  • 0
    Avatar
    John T Hickey

    I noticed you have:

    Create(TurboSQLTable)

    but I didn't see the CREATE attribute on the file definition.  Is it needed?  Is the table actually created on the server?

  • 0
    Avatar
    Arnor Baldvinsson

    John:  You do not need the CREATE attribute on the file declaration and it is only created as a structure in memory.  The only server access is done by the PROP:SQL that then returns the dataset into this structure in memory.  Think of the TURBOSQL table as a queue  that you can fill directly from the server with a SELECT statement:)

    Arnor

  • 0
    Avatar
    Colin Wynn

    There is a great article regarding this on ClaronMag. I've only just started using it, I've taken over an app that converts our TPS data to SQL and for some reason the previous developer has EVERY SQL table used defined in the dictionary! Using this method I only need this SQL table as I'm constantly either issuing SELECT or EXECUTE statements :)

  • 0
    Avatar
    Roland Gauna

    Arnor:
    Good article. Just wanting to tell you that you need to declare the table owner (Owner Name).

    Greetings.

  • 0
    Avatar
    Arnor Baldvinsson

    Roland: Thanks for the comment. I have been using TurboSQL tables for years and have never needed to add the OWNER attribute. I'm not sure in what conditions it is needed. I have used this with MSSQL and mySQL and I think SQL Anywhere. In a project I'm working on I have 82 TurboSQL tables and none of them uses the OWNER attribute. I'm curious what problems you run into with out it? What backend are you using?

Please sign in to leave a comment.
Powered by Zendesk