Category Archives: Clarion

Using the Dummy Temp file tf

Posted on by

(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.

Clarionet in Clarion v6.1

Posted on by

RTF Control in Clarionet.

I have a client who has been running a program across a WAN using Clarionet. System was written several years ago in Clarion for Windows 6.1 and has not been modified for several years.

Recently he wanted an additional feature so that the system would generate some documents.
I was apprehensive in making the changes so built a small additional standa alone program to test with Clarionet. The programs are all Legacy template initially.

I needed to add a window with an RTF text window so the system could open a template .RTF document, and replace some tokens, then produce a print preview of the letter at the client side PC.

I knew that the Clarion for Windows RTF control was not going to behave in a Clarionet screen, so this had to be built to run without showing the RTF window and without any Clarionet code. By starting with a generated procedure, I then placed several OMIT statements to exclude any Clarionet generated calls. Next I added all my INIT, Document Open and KILL statements to right after the OPEN(Window) statement. Then I did the token replacements and saved the finished document as an RTF file.

Back in the calling procedure, I then passed the document’s filename to a report procedure, and placed the RTF file into a detail/text (rtf) control. This report then generated and the preview was auto-magically transferred to the client side PC. Done.

Now that I had got it working in a stand alone EXE, and it all worked OK, I then moved it into the trusty DLL written about 6 years ago. This worked fine and nothing got broken.

John Griffiths

The Dreaded “Record Was Changed By Another Station”

Posted on by

The subject error message shows up sometimes when working with Clarion and SQL based data.

When working on a recent conversion project I received the message when testing. I had moved a database from .TPS files to a MS-SQL Server database.

I had seen this message many time before, when getting started with SQL and dealing with DateTime data fields. So, I knew exactly where to look this time. Looking through the table in question, I noticed that I had no DateTime fields in the table. So something else was causing the warning “Record Was Changed By Another Station”. But, as there were no other “Stations” active, I was now in search of the source of the message.

First thing I checked was the order of the fields in my Dictionary, and the order of the fields in the database. This should not cause the error, but who knows… This all checked out OK.

Next I checked the data types were aligned and this too was all OK.

That is when I noticed that I had a STRING field in my dictionary as STRING(12) and in the database it was declared as CHAR(16). Yep, I had deliberately made it wider when I designed the new table but I had forgotten to adjust my Dictionary. Once I adjusted my dictionary to match the database, and re-compiled. all worked as it should.

But why would it report the “Record Changed By Another Station” warning, and not some more relevant message? Probably because the Clarion SQL file driver was doing its job correctly, and comparing my initially saved file buffer (with the 12 bytes I hade in my string in my dictionary). Then, just as I go to save my changed to the database, the SQL File Driver re-reads the data from the database, this time not using my data declarations, but the data as defined in the SQL table. It then does a byte-by-byte compare with the saved buffer. They do no match, so the warning message is displayed.

Bottom line: Make sure your Clarion dictionary declarations match the database fields.

John.

SQL Programming in Clarion

Posted on by

I have posted several hints for working with MS-SQL2005 and get many requests for more.

If you are working with SQL-2005 Express or Standard, and have a hint you would like published, then please let me know and I will add it to the site.

The site I post to is www.sqlkey.com/

John Griffiths