Extended strings

[jimsterS]

I need to create a sql string that will be longer then 256 characters. I am pretty sure this will cause a problem but how can I get by this.

In visual basic i would just cancantenate a bunch of strings will that tecnique work in foxpro as well

like this
sqlstr="SELECT sum(add96) as add96, sum(dis96) as dis96, sum(wo96) as wo96, "
sqlstr=sqlstr+"sum(adjdis96) as adjdis96, sum(add97) as add97, sum(dis97) as dis97, "
sqlstr=sqlstr+"sum(wo97) as wo97, sum(markout98) as markout98, sum(add98) as add98, "
sqlstr=sqlstr+"sum(dis98) as dis98, sum(adjdis98) as adjdis98, sum(add99) as add99, "
sqlstr=sqlstr+"sum(dis99) as dis99, sum(adjdis99) as adjdis99, sum(add00) as add00, "
sqlstr=sqlstr+"sum(dis00) as dis00, sum(adjdis00) as adjdis00 from xxxxxxx "
sqlstr=sqlstr+"into table finalBill group by VAL(store)"

And if I can use the same technique how do I execute the string.

Thanks
jimsterS

 

[jimoo]

You can help reduce the size by using the first 4 characters of VFP commands. In other words, SELECT would be SELE and GROUP BY would be GROU BY

To execute the string:

&sqlstr



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[baltman]

Here's the limit you need to worry about from HELP:

Maximum # of characters per command line 8,192
Maximum # of characters per macro substituted line 8,192

Brian

 

[Mike Lewis]

JimsterS,

When you say a "SQL string", do you mean a SELECT statement? If so, there is no 256-character. You might be thinking of the limit on a quoted[/i] string, but it's easy to get round that.

In fact, you can do exactly the same as you are doing in VB. Or, if you want to send the command to SQL Server, you can do this:

TEXT TO SQLStr NOSHOW

SELECT sum(add96) as add96, sum(dis96) as dis96,
sum(wo96) as wo96, sum(wo97) as wo97,
sum(markout98) as markout98, sum(add98) as add98,
etc. etc.
into table finalBill group by VAL(store)"

ENDTEXT

The above won't work if you want to execute the SELECT within VFP because it won't line the line-endings, but that won't be a problem for SQL Server.

To execute the SELECT within VFP, just do this:

&SQLStr

Hope that helps.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[jimsterS]

I took out everything like sum(add96) as add96 and replaced it with sum(add96)which reduced the length considerably but still well over 256 characters and decided to try to see what errors I would get and it worked. So the limit is not 256. I did not have time to experiment but I will - thanks for all the help.

JimsterS

 

[Mike Lewis]

JimsterS,

If you would read the above messages from Baltman and myself, you will see that the supposed 256-char limit is not an issue.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[wgcs]

The method I use is a multi-line concatenation:

sqlstr = "SELECT sum(add96) as add96, sum(dis96) as dis96, sum(wo96) as wo96, " ;
 +"sum(adjdis96) as adjdis96, sum(add97) as add97, sum(dis97) as dis97, ";
 +"sum(wo97) as wo97, sum(markout98) as markout98, sum(add98) as add98, ";
 +"sum(dis98) as dis98, sum(adjdis98) as adjdis98, sum(add99) as add99, ";
 +"sum(dis99) as dis99, sum(adjdis99) as adjdis99, sum(add00) as add00, ";
 +"sum(dis00) as dis00, sum(adjdis00) as adjdis00 from xxxxxxx ";
 +"into table finalBill group by VAL(store)"

* and now, as mentioned, execute this using:
&sqlstr

Of course, this also works:

SELECT sum(add96) as add96, sum(dis96) as dis96, sum(wo96) as wo96, ;
       sum(adjdis96) as adjdis96, sum(add97) as add97, sum(dis97) as dis97, ;
       sum(wo97) as wo97, sum(markout98) as markout98, sum(add98) as add98, ;
       sum(dis98) as dis98, sum(adjdis98) as adjdis98, sum(add99) as add99, ;
       sum(dis99) as dis99, sum(adjdis99) as adjdis99, sum(add00) as add00, ;
       sum(dis00) as dis00, sum(adjdis00) as adjdis00 from xxxxxxx ;
 into table finalBill group by VAL(store)

 

[JRB-Bldr]

You might consider the following approach for debugging the problem:

SQLStr96 = " SUM(add96) AS add96, SUM(dis96) AS dis96, SUM(wo96) AS wo96, SUM(adjdis96) AS adjdis96,"
SQLStr97 = " SUM(add97) AS add97, SUM(dis97) AS dis97, SUM(wo97) AS wo97,"
SQLStr98 = " SUM(markout98) AS markout98, SUM(add98) AS add98, SUM(dis98) AS dis98, SUM(adjdis98) AS adjdis98,"
SQLStr99 = " SUM(add99) AS add99, SUM(dis99) AS dis99, SUM(adjdis99) AS adjdis99,"
SQLStr00 = " SUM(add00) AS add00, SUM(dis00) AS dis00, SUM(adjdis00) AS adjdis00"
SQLStrRest = " FROM xxxxxxx INTO TABLE finalBill GROUP BY VAL(store)"

SQLStr = "SELECT" + SQLStr96 + SQLStr97 + SQLStr98 + SQLStr99 + SQLStr00 + SQLStrRest
&SQLStr

In this manner you can easily eliminate portions of the over-all SQL String in order to debug its execution.

NOTE - you might want to look into your GROUP BY expression since the value "Store" does not appear anywhere within the SQL String.

Good Luck,
JRB-Bldr

 

[ramani]

HI

I am wondering.. is there a limit of 256 characters in an SQL command ?. I am pretty sure .. NO..

Many of my SQL commands go to dozens of lines.

The length of a single line is limited. So you have to use ";" to continue in next line.
Example..

SELECT ;
field1, field2, field3, ;
field4, field5, field6 ;
FROM myTable ;
....

Having said this, there is some limit in certain conditions.. example.. limit to the number of joins.. i believe is 9 etc.
The maximum length for a command line is 8,192 bytes.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/