MySQL Query fails in loop

[Pizzadude]

This is a simple function that gets a count of selected rows from a List control, and then executes a SELECT statement against a MySQL table.

flag = cn.execute(sql) returns true on the first loop, but returns false on the second an subsequent loop.

I cannot figure out why it fails.

function test as c (e as p)
	
dim flag as l
dim sql as c
dim cn as sql::Connection
dim args as sql::Arguments
dim listCnt as n
dim i as n

flag = cn.open("::Name::ConnectionName")

listCnt = e.dataSubmitted.List.size()

for i = 1 to listCnt

sql = <<%a%
SELECT Posting_ID
FROM posting
WHERE Posting_ID = 1
%a%

flag = cn.execute(sql)

debug(1)
next i		

cn.Close()
			
end function

[Michael1954]

Re: MySQL Query fails in loop

I don't understand why you are doing it this way and not using the Alpha list filter.

However, should your select not use an argument Posting_ID it is always set to 1?

[Pizzadude]

Re: MySQL Query fails in loop

This is just a test function I made because a more complex function is having this problem. I am executing the same select statement every time.

Why should it not use Posting_ID = 1?

[GGandhi]

Re: MySQL Query fails in loop

listCnt = e.dataSubmitted.List.size()

for i = 1 to listCnt

sql = <<%a%
SELECT Posting_ID
FROM posting
WHERE Posting_ID = 1
%a%

flag = cn.execute(sql)
next i

you are not cycling thru i
so it should be
WHERE Posting_ID = i

better will be to use arguments
like

listCnt = e.dataSubmitted.List.size()
dim args as sql::arguments
for i = 1 to listCnt

sql = <<%a%
SELECT Posting_ID, and some_other_fields 
FROM posting
WHERE Posting_ID =:newPostId
%a%
args.set("newPostId", i)

flag = cn.execute(sql, args)

next i

[Pizzadude]

Re: MySQL Query fails in loop

OK. I can use an argument so the query is different in every loop.

That does not address the problem I am having.

The problem is that the query succeeds in the first loop, then fails in subsequent loops.

function test as c (e as p)
	
dim flag as l
dim sql as c
dim cn as sql::Connection
dim listCnt as n
dim i as n

flag = cn.open("::Name::ConnectionName")

listCnt = e.dataSubmitted.List.size()
dim args as sql::Arguments
for i = 1 to listCnt

sql = <<%a%
SELECT Posting_ID, originCity
FROM posting
WHERE Posting_ID = :newPostId
%a%
args.set("newPostId", i)

flag = cn.execute(sql,args)
debug(1)	

next i		

cn.Close()
			
end function

[Michael1954]

Re: MySQL Query fails in loop

Why are you re-inventing the wheel?

Alpha does it all for you with their action JavaScript.

[GGandhi]

Re: MySQL Query fails in loop

can you put your debug(1) at the beginning of your code right after function declaration and check if the listCnt is coming out with proper number
also it will show what error is after the first loop. if there is any error it will pop up.

[Davidk]

Re: MySQL Query fails in loop

It doesn't matter, for your test, if you use an argument... I can't see why that would affect anything. The issue is that you're not using the resultSet before attempting to get another one.

When you hit your debug statement you should examine cn and find cn.CallResult.Text which will give you the MySQL error you're getting... which is...

Commands out of sync; you can't run this command now

If you add...

dim rs as sql::ResultSet

to the section where you're dimming.. and then add

rs = cn.ResultSet

after your cn.execute, then the select will succeed for each loop.

[Pizzadude]

Re: MySQL Query fails in loop

David, I added the 2 lines of code as you suggest.

Prior to adding the 2 lines, cn.CallResult.Text returned "Success" on every loop.

flag = cn.execute(sql,args) now returns .T. on every loop, which is the result I was after.

But I don't understand why flag = cn.execute(sql,args) fails without attempting to populate a resultSet. It is just executing a query. Why would the resultSet affect whether the query was successful or unsuccessful?

[Davidk]

Re: MySQL Query fails in loop

cn.CallResult.Text doesn't return "Success" for your original code. Only the 1st time. It depends on when and how you're looking at cn. When your code breaks into the debugger, enter cn.CallResult.Text in the Expression column... and then continue to step through. You'll see the Text change to the error. If you always add cn as a new expression to check, it always shows success. But if you add it, and watch it... you'll see the error.

Another concern I saw was using listCnt = e.dataSubmitted.List.size() to get a List count. You don't say how your List is set up, or how you're selecting List rows, but .size() on a List returns an array that's much larger than the number of rows selected... at least for me... with a List set up for multi-select. This is because it's returning an array of all the data selected, not a count of the rows selected.

[GGandhi]

Re: MySQL Query fails in loop

i ran a quick test and it is the same if you run the query repeatedly without using the result set.
see the reference from mysql:
https://dev.mysql.com/doc/refman/5.7...t-of-sync.html

[Davidk]

Re: MySQL Query fails in loop

i ran a quick test and it is the same if you run the query repeatedly without using the result set.

I'm unsure what this means... but if you use the resultSet after each select, then every select runs without error... otherwise you get the MySQL error I posted.

If you're getting different results then post your full code and I'll run it.

[madtowng]

Re: MySQL Query fails in loop

Hi Joe,

I made some minor adjustments to your code to help you out.
Sometime sql statements fail for reasons you don't expect. It's always good to know what the system thinks is happening at
the time of the failure.
I don't know how many records you're dealing with so I set it up that debug only activates when you have a hard error (this will not help you with logical errors)
This should get you the answer of why your code is failing after the initial pass through the loop.

function test as c (e as p)
	
dim flag as l
dim sql as c
dim cn as sql::Connection
dim listCnt as n
dim i as n
dim successful as l = .f.
dim sqlerr as c
flag = cn.open("::Name::ConnectionName")
debug(0)
listCnt = e.dataSubmitted.List.size()
dim args as sql::Arguments

'I moved the sql section because as long as you're not changing
'it, there is no need to have it in the loop

sql = <<%a%
SELECT Posting_ID, originCity
FROM posting
WHERE Posting_ID = :newPostId
%a%

for i = 1 to listCnt
args.set("newPostId", i)

successful = cn.execute(sql,args)

if .not. successful then
debug(1)
'send an email to the appropriate person/people
'put it in a log file	
sqlerr = cn.callresult.text
'This tells you what caused the failure.

else
'put the stuff in here you want done after a successful
'call to your sql database

end if
debug(0)
next i		

cn.Close()
test="This function ended properly"			
end function

[GGandhi]

Re: MySQL Query fails in loop

I'm unsure what this means... but if you use the resultSet after each select, then every select runs without error... otherwise you get the MySQL error I posted.

If you're getting different results then post your full code and I'll run it.

i get the same false after the first, david. may be i wasn't very clear.
did you see the reference?

[Pizzadude]

Re: MySQL Query fails in loop

OK, I am becoming more educated. Thanks to everyone.

I am now seeingthe error 'Commands out of sync; you can't run this command now'.

listCnt = e.dataSubmitted.List.size() is returning just the number of rows selected. In my case I have a list control set to return the primary key - Posting_ID

Ultimately I am trying to troubleshoot a more complex function that is failing - seemingly randomly - during a loop.

In creating this test function I ran into this particular problem, which did not occur in the original function that I was troubleshooting, because (I have now learned) in that function I an obtaining a resultset.

Tomorrow I will work on the more complex function that is giving me trouble, and post an example.