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





Re: SQL query too long doesnĄ¯t work with SQLEXEC()

Naomi Nosonovsky


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

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






Re: SQL query too long doesnĄ¯t work with SQLEXEC()

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







Re: SQL query too long doesnĄ¯t work with SQLEXEC()

dni

"+"




Re: SQL query too long doesnĄ¯t work with SQLEXEC()

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!






Re: SQL query too long doesnĄ¯t work with SQLEXEC()

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)





Re: SQL query too long doesnĄ¯t work with SQLEXEC()

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






Re: SQL query too long doesnĄ¯t work with SQLEXEC()

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





Re: SQL query too long doesnĄ¯t work with SQLEXEC()

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 Sad





Re: SQL query too long doesnĄ¯t work with SQLEXEC()

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






Re: SQL query too long doesnĄ¯t work with SQLEXEC()

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!






Re: SQL query too long doesnĄ¯t work with SQLEXEC()

CetinBasoz

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

select name from customer where city like m.mySearchCondition





Re: SQL query too long doesnĄ¯t work with SQLEXEC()

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,