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 

A few details about this SQLKey web site.

I have revised the SQLKey web site from using static html pages to now using a WordPress CMS.

I am still learning more about WordPress but have found it much better and easier to work with than Joomla! which I have tried.

I also tried Drupal, but that looked like a full-time job with a longer learning curve.

The site is designed as a place to post a few tips and tricks for programming with  Clarion For Windows  , and more importantly, with using Clarion with  MS-SQL   .

 

As of late 2017, I am now 100% retired. I have trained up a friend (Kerry Leighton of Genawise Australia) and he contacts me with a few questions from time to time.  Kerry works for Genawise Australia, the company that purchased all my clients from me.

Enjoy.

 John Griffiths