Datadriven programming

[jesylv]

I am having trouble making this work:

I store SQL Statements in a memo field. Some are quite long (400-500 caracters). At run time users select a SQL statement for execution:

[tt]
&& Recup the SQL and strip CRLF
lcStatement = CLEANCOMMAND(cMySQLTable.mSQL)

&& Execute statement....
&lcStatement.
[/tt]
Works fine with short SQL statements but fails if statements longer than 255 caracters. Any ideas I could use to circumvent this problem????

Jean

 

[GriffMG]

I think this is due to the macro substitution having a 255 char limit - I'm not 100% sure.

one answer might be to try breaking it down into smaller macros and 'stitching them together' at run time, you would need to break them carefully to avoid spaces getting put in odd places though.

There have to be better ways to do this - anyone know if SQLEXECUTE is the right way?

Hope this helps
Griff

 

[ramani]

HI jean,
When you store the memo field for the SQL, there could be <CR> at the end of each physical line breaking your SQL command statements.

myCommand myCommand myCommand ; && note &quot;;&quot; used with <CR>
myCommand myCommand myCommand ; && note &quot;;&quot; used with <CR>
myCommand myCommand myCommand

probably this approach can solve the problem, if you are entering the SQL code in a memo window directly.

read on SET MEMOWIDTH command. You can set the width to upto 8192 characters. But display is limited to 256 characters only.

Hope this solves your problem ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[jesylv]

Hi Ramani and Griff,

I Resumed testing this morning after a good sleep and discovered my mistake. I had an error in my test SQL Statement. Once I solved that problem, it worked well. No need to break up the SQL Statement since it seems to work with my longest SQL commands (554 characters).

Thanks to all

Jean

 

[JARVIMA]

Jean,

this is really interesting.

Why and how do you store your Visual Foxpro SQL statement in a memo field ?

Very interested to understand more.

cheers/Martin.

 

[jesylv]

Hi Martin


I have a large system with 64 tables. Instead of hardcoding extraction requests or relying on a series of query files, I store SQL Statements in a memo field. My sql table has the following structure:


description c (60)
cSQL M(4)

In cSQL, I write SQL Statements separated by CRLF.

Users select a query by its description and the statements that are kept in cSQL are passed to an object cRunSQL that execute the query.

The results are then used to produce labels,mass faxing, mass email, word merge... etc...


Hope this helps

Jean