(a tip for Clarion programmers using MSSQL).
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 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 future article.