SQL query too long doesn´t work with SQLEXEC()

Everton81

Hi fellows!

IĄŻm doing a big sql query with 2 memory variables (cSQL and cWhere).

Something like this:

cSQL = "Select ....From.....Inner Join...."

cWhere = "Where field1 like '%" + upper(textbox1.value) + "%'" + "Or field2 like '%" + lower(textbox2.value) + "%'" ...and so on until I can check out several possibilities of writing. Something like 30 fields that I verify 5 possiblities for each one. A big query!

Then I do:

cSQL = cSQL + cWhere

SQLEXEC(myConnection, cSQL, "cursorName")

Man! It doesnĄŻt work! And i do the same query on console (I use both Firebird and SQL Server) and it returns my records.

I donĄŻt know what to do. I think itĄŻs related to 255 characters limitation per query, am I right

If so, how to concatenate this "monster" together and send to database !!!

Thanks a lot,

Everton

Naomi Nosonovsky

AFAIK the limit is 8192 chars, though I may be mistaken.

Also, what exactly doesn't work What error do you get

Adamus Turner

First off, not a Fox Pro guy but try this:

Code Snippet

cSQL = cSQL & " " & cWhere

SQLEXEC(myConnection, cSQL, "cursorName")

Does fox pro use "+" or "&" for concatenation

Adamus

dni

"+"

Everton81

ThereĄŻs no error message simply it donĄŻt returns my result records, cursor is not created, despite the query structure is correct (as I wrote in console it works!)

I working with VFP 8 SP1. I was wondering if "sliced" the whole query by using cWhere1, cWhere2, cWhere3...variables, and putting them all together at end.

Any other ideas fellows are accepted. Thanks!

CetinBasoz

255 characters limit in VFP only applies to string literals. String variables on the other hand can be large (16Mb per documentation). If you construct a query as you do it'd still work (you're concatenating multiple string literals). Why doesn't it work, have you checked What does aerror() show

It should work, however that's not the way to build an SQL string for passthrough (or RV, ADO etc). Use parameters instead. ie: (SQL server):

text to m.lcSQL noshow

select * from Northwind.dbo.customers

where CompanyName like m.company and ContactName like m.contact

and customerId in

-- just trying to create a long SQL sample

(select customerID from orders where orderDate = m.checkDate)

-- etc etc

endtext

company = "%computer%"

contact = "%s%"

checkDate = date(1997,1,1)

if SQLExec(m.lnHandle, m.lcSQL, 'myResult' ) < 0

aerror(aWhy)

disp memory like aWhy

endif

SQLDisconnect(m.lnHandle)

Adamus Turner

Everton81 wrote:

ThereĄŻs no error message simply it donĄŻt returns my result records, cursor is not created, despite the query structure is correct (as I wrote in console it works!) I working with VFP 8 SP1. I was wondering if "sliced" the whole query by using cWhere1, cWhere2, cWhere3...variables, and putting them all together at end. Any other ideas fellows are accepted. Thanks!

Here's my final guess which may be obvious to some but can be easily overlooked.

When concatenated strings into a query, a space betweent either the end of string or beginning of string is overlooked. So you end up having something like the following:

string1 = "SELECT * FROM"

string2 = "myTable"

string3 = string1 + string2

litertally string3 would be = SELECT * FROMmyTable

The solution is to alway just add a space in the middle:

string3 = string1 + " " + string2

Always willing to state the obvoius,

Adamus

Maurossi

I everyone, excused my English.

I have the some problem with insert, if my string is lower 1000 char no problem, but if 1001 char returned error with insert.

I must insert 52 field on server from one local table fox, therefore:

Strfield="(partnum, pnpulito --->>> more to 52 field

Strval="( localweb.partnum, localweb.pnpulito --->>> more to 52 field

cString="insert into web "+Strfield+" values "+ Strval

*!* the len of string is 2018 char

rst=SQLExec(lconn,stringa)

you have some idea thanks to all those that answer to me

Mauro

Naomi Nosonovsky

There was a discussion on the same topic few months back on www.tek-tips.com website. As far as I recall, it was found to be some VFP limitation. I'm going to try to find this discussion.

UPDATE. Unfortunately, it looks like Search functionality doesn't work on this forum properly now

Everton81

Fellows,

Really thanks for your ideas e guidesteps, however, iĄŻm using Firebird and I think when I use (question symbol) it doesnĄŻt work because Firebird uses ": " for parameters.

I donĄŻt know what to do yet, but if i find something usable u can be sure iĄŻll share with all of u.

Thanks a lot to everybody.

Everton

Everton81

Fellows,

I couldnĄŻt find the answer yet, but I found what is happening. Maybe someone has faced it before.

When IĄŻm using a varchar field with Like expression as below:

Select c.Name from Customer c Where c.City Like " %my-search-expression%"

where City is a varchar field, thatĄŻs fine. It works!

Although, if IĄŻm using a Memo field (in Firebird a Blob field) instead, it doesnĄŻt work even working in Firebird console!

Now the question is: Where I need to set to include Memo fields in my Where conditions

SQLSetProp(), CursorSetProp() or elsewhere

Thanks a lot to all!

CetinBasoz

mySearchCondition = '%' + m.mySearchExpression + '%'

select name from customer where city like m.mySearchCondition

Everton81

Foxes,

After a intense research I conclude that my problem here are related to Codepage. I'll explain.

I started this thread thinking my problem was a too long SQL query, but after performing several tests I perceived it worked fine without punctuated (accents) string expressions. For example maca (apple in English), caminhao (truck) relogio (clock) it doesn`t work. I thought that was automatically configured to a codepage "portuguese compatible" but it's not. What can I do to set an appropriate Codepage to Portuguese I need to do that in global range or can I just set it for a specific query moment

Honestly I never needed to deal with Codepages before...

Thanks for all,