There is a lot of information published on using Dummy Temporary tables in Clarion with SQL.

The /TURBOSQL = TRUE   driver string is the magic bullet.

I define my dummy files with the name  tf (indicating a TempFile and quick to type and easy to find) in my local data in any procedure/function where I need it.

I set each data column as a CSTRING of at least the max size I might need for any data coming back from the SQL-Server. The number of columns I define varies as needed and can easily be changed when more are needed.

There is NO actual table needed in the Server Server database to match this dummy table, so long as I use the ‘/TURBOSQL = TRUE’   MSSQL driver switch. It is just a local table buffer (within the procedure).

I take care of opening and closing the file (well, it’s really only a file buffer that gets created and instantiated with the OPEN() command).

Here is a typical  declaration:-

tf    file,driver('mssql', '/TURBOSQL = TRUE'), PRE(tf),owner( TblOwner )
record  record
f1  cstring(201)  
f2  cstring(201)  
   end
        end

I also put my SQL command into the local cstring-

SQLstr  CSTRING(801)

Whilst this is not absolutely necessary, it makes things neater. If I wanted to retrieve a couple of fields, say using

SELECT lname, fname from Client where sysid = 1234

So, I could do

clear(tf)
tf{prop:sql}= 'SELECT lname, fname from Client where sysid = 1234'

but I like to use the local SQLstr variable….

clear(tf)
SQLstr =  'SELECT lname, fname from Client where sysid = 1234'
tf{prop:sql}= SQLstr

Now, the above shows a simplified version of what is actually used in the source code. Reason being, to prevent any SQL injection or user nastiness. That is the subject of a whole other forthcoming article.

Two things to remember to do:

  1. At start of procedure    OPEN(tf) 
  2. and before returning,    CLOSE(tf) 

Add as many fields to the tf file declaration as you may need.

Comment or send any questions to me at questions  @  sqlkey.com

 

 John Griffiths