Result set fails on 2nd callback

[Peter.Greulich]

This code works:

''Select Dimensions records
''-------------------------------
SQL_Select = "SELECT * FROM Dimensions WHERE LDDIM_ID = :Load_id"
flag = conn.execute(SQL_Select, args)
vText = conn.callResult.text
rs = conn.ResultSet


rowFlag = rs.nextRow()
while rowFlag
    vDim_Id = rs.data("DIM_ID") 
    vText = xbDupeDims(conn,args,vDim_Id,vNewLoad_Id)
    rowFlag = rs.nextRow()
end while

But the same code (i.e. Dimensions) fails after running the first result set (Stops - which works) shown as here:

''Select Stops records
''-------------------------------
SQL_Select = "SELECT * FROM Stops WHERE LDSTP_ID = :Load_id"
flag = conn.execute(SQL_Select, args)
vText = conn.callResult.text
rs = conn.ResultSet


rowFlag = rs.nextRow()
while rowFlag
    vStop_Id = rs.data("STP_ID") 
    vText = xbDupeStops(conn,args,vStop_Id,vNewLoad_Id)
    rowFlag = rs.nextRow()
end while


''Select Dimensions records
''-------------------------------
SQL_Select = "SELECT * FROM Dimensions WHERE LDDIM_ID = :Load_id"
flag = conn.execute(SQL_Select, args)
vText = conn.callResult.text
rs = conn.ResultSet


rowFlag = rs.nextRow()
while rowFlag
    vDim_Id = rs.data("DIM_ID") 
    vText = xbDupeDims(conn,args,vDim_Id,vNewLoad_Id)
    rowFlag = rs.nextRow()
end while

If I reverse them - i.e. run Dimensions first, that works but then Stops fails. I tried putting in conn.FreeResult() but that has no effect. I also tried renaming the 2nd result set using rs2 but that doesn't help either.

Seems to be some kind of bug. Any ideas out there on a workaround?

[Peter.Greulich]

Re: Result set fails on 2nd callback

I also tried the following between the 2 rs

Delete SQL_Select
Delete rs
Delete rowFlag

No dice.

[Peter.Greulich]

Re: Result set fails on 2nd callback

Oh. The error returned is "Not a valid result set" or "MySql server has gone away".

Hint: it's not gone.

[-Jinx-]

Re: Result set fails on 2nd callback

Is it possible you indexing past the next row since it indexes each time it sees rs.NextRow() ? It can be a little finicky. I do it differently than you just so I don't have to worry about how many times it might be invoked. Here's an example from some of my code:

while rs.NextRow()
	cnt = cnt + 1
	'These are the repeating section controls for CONTAINER_2
	eval("j.LAYOUTORD_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("layoutord")
	eval("j.POSITION_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("position")
	eval("j.DETAILSEQUENCE_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("sequence")
	eval("j.TOOL_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("tool")
	eval("j.TOOLINFO_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("toolinfo")
	eval("j.DETAILNOTES_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("notes")
	eval("j.DESC_2_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("desc2")
end while

With my code, you don't need to set the flag (which indexes it from zero to the first row) since the while call will do the same thing.

Also, I don't think you don't need to do this in your while loop:

rowFlag = rs.nextRow()

I think that may be your problem. If you only have that in the beginning but not in the loop I think it will work. I think just doing it my way without setting a flag is easier though.

On the other hand, I may be smoking crack and I've misunderstood the problem.

[Peter.Greulich]

Re: Result set fails on 2nd callback

Good point. You remove an extra step. Unfortunately that doesn't help. Same problem.

[-Jinx-]

Re: Result set fails on 2nd callback

Where is your error checking for the connection? Could you try changing it up a bit more and seeing if the error returned is more useful from code something like the below:

Once again, ditching flags....

''Select Dimensions records
''-------------------------------
dim jscmd as C
if conn.open("::Name::yourconnectionname") then
	SQL_Select = "SELECT * FROM Dimensions WHERE LDDIM_ID = :Load_id"
	if conn.execute(SQL_Select, args) then
		vText = conn.callResult.text
		rs = conn.ResultSet
		while rs.nextRow()
		    vDim_Id = rs.data("DIM_ID") 
		    vText = xbDupeDims(conn,args,vDim_Id,vNewLoad_Id)
		end while
	else
	jscmd = "alert('Execute Error: " + js_escape(conn.CallResult.text) + "');"
	end if
else
jscmd = "alert('Connection Error: " + js_escape(conn.CallResult.text) + "');"
end if

YourFunctionName = jscmd

[Peter.Greulich]

Re: Result set fails on 2nd callback

Thanks. Problem solved, it seems.

The conn remains open. So that's not the issue. But it is related somehow. Rather than pass in the connection & args, I removed that from the functions and explicitly reopened/closed the conn within the functions themselves, and that seems to work. Lot of extra overhead though. I think I'll just bag the functions altogether and runs it as a straight loop. That way I won't have to close/open conns continuously.

[-Jinx-]

Re: Result set fails on 2nd callback

Ah, IC. In looking at some of my code, I wasn't seeing it as I pretty much always close my connections either after an execute error (just after the first end if in the code I posted above) or just before ending my functions.

[Peter.Greulich]

Re: Result set fails on 2nd callback

Funny stuff, Jinx. Had to stick w. the functions and the repeating open/close conns. That works well. When i removed the functions and just ran the code in a loop I got the same error. Really strange.

[-Jinx-]

Re: Result set fails on 2nd callback

Hold on..... Are you making a call, and then changing your select statement/args to make another call to the same connection? All in the same callback? Because that should work fine. For some reason I confused that.

I can post some code I have that I know works, it actually makes three separate calls. I just delete my result set in between.

ETA: I think you also may have to dim rs as SQL::ResultSet after every delete. I have it that way in my code anyway. Must be some reason I did it. LOL

[Peter.Greulich]

Re: Result set fails on 2nd callback

Are you making a call, and then changing your select statement/args to make another call to the same connection? All in the same callback?

Yeah, basically.

conn.open

select
rs

seelct
rs

...
conn close

[-Jinx-]

Re: Result set fails on 2nd callback

Try something like this maybe?

''Select Stops records
''-------------------------------
SQL_Select = "SELECT * FROM Stops WHERE LDSTP_ID = :Load_id"
if conn.execute(SQL_Select, args) then
	vText = conn.callResult.text
	dim rs as SQL::ResultSet
	rs = conn.ResultSet
	while rs.nextRow()
	    vStop_Id = rs.data("STP_ID") 
	    vText = xbDupeStops(conn,args,vStop_Id,vNewLoad_Id)
	end while
	
	delete rs
	
	''Select Dimensions records
	''-------------------------------
	SQL_Select = "SELECT * FROM Dimensions WHERE LDDIM_ID = :Load_id"
	if conn.execute(SQL_Select, args) then
		vText = conn.callResult.text
		dim rs as SQL::ResultSet
		rs = conn.ResultSet
		while rs.nextRow()
		    vDim_Id = rs.data("DIM_ID") 
		    vText = xbDupeDims(conn,args,vDim_Id,vNewLoad_Id)
		end while
	end if
end if

Sorry for my format changes, I'm just anal that way....

Anyway, that's closer to what I do. Maybe the key is dim-ing the result set again.


Here's my actual working code that I'm using to copy a record in a dialog. It makes three separate calls and still works fine on the latest pre-release build as of today:

function getRecordData as c (e as p)
	dim oldid as c
	oldid = e.dataSubmitted.UNIQUE_ID
	dim masterid as n
	masterid = oldid
	dim revtest as n = int(convert_type(e.nextrev,"N"))
	dim vjscmd as c
	dim vjsprg as c
	dim j as p
	dim cn as SQL::Connection
		if cn.open("::Name::MVWKS")	then
			dim sqlCommand as c = "SELECT * FROM machlayoutmaster WHERE unique_id = :masteruid"
			dim args as SQL::Arguments
			args.add("masteruid",masterid)
				if cn.Execute(sqlCommand,args) then
					dim rs as SQL::ResultSet
					rs = cn.ResultSet
						if rs.NextRow() then
							'These are the header controls
							j.MACHNO = rs.Data("machno")
							j.PARTNO = rs.Data("partno")
							j.DWGNO = rs.Data("dwgno")
							j.STATUS = rs.Data("status")
							j.MACHTYPE = rs.Data("machtype")
							j.NUMPARTS = rs.Data("numparts")
							j.OPNO = rs.Data("opno")
							if revtest < 1 then
								j.LAYOUTREV = rs.Data("layoutrev")
							else
								j.LAYOUTREV = rs.Data("layoutrev")+1
							end if
							j.MAINPRGNO = rs.Data("mainprgno")
							j.HASSUBS = rs.Data("hassubs")
							j.NOTES = rs.Data("notes")
							j.PROGRAMCALL = rs.Data("prgcall")
								sqlCommand = "SELECT * FROM machlayoutdetail WHERE layoutmaster_id = :masteruid"
									delete rs
								dim cnt as n = 0
									if cn.Execute(sqlCommand,args) then
										dim rs as SQL::ResultSet
										rs = cn.ResultSet
										while rs.NextRow()
											cnt = cnt + 1
											'These are the repeating section controls for CONTAINER_2
											eval("j.LAYOUTORD_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("layoutord")
											eval("j.POSITION_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("position")
											eval("j.DETAILSEQUENCE_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("sequence")
											eval("j.TOOL_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("tool")
											eval("j.TOOLINFO_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("toolinfo")
											eval("j.DETAILNOTES_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("notes")
											eval("j.DESC_2_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("desc2")
										end while
											sqlCommand = "SELECT * FROM machlayoutsubprograms WHERE layoutmaster_id = :masteruid"
												delete rs
													dim cnt1 as n = 0
														cn.Execute(sqlCommand,args)
														dim rs as SQL::ResultSet
														rs = cn.ResultSet
														while rs.NextRow()
															cnt1 = cnt1 + 1
															'These are the repeating section controls for CONTAINER_1
															eval("j.MAINPRG_A5INSTANCE" + alltrim(str(cnt1))) = rs.Data("mainprg")
															eval("j.SEQUENCE_A5INSTANCE" + alltrim(str(cnt1))) = rs.Data("sequence")
															eval("j.SUBPRG_A5INSTANCE" + alltrim(str(cnt1))) = rs.Data("subprg")
															eval("j.DESCRIPTION_A5INSTANCE" + alltrim(str(cnt1))) = rs.Data("description")
														end while
										'JavaScript returned to dialog
										vjscmd = "{dialog.object}._setRepeatingSectionRowCount('CONTAINER_2'," + cnt + ",true,true);"
										vjscmd = vjscmd + "{dialog.object}._setRepeatingSectionRowCount('CONTAINER_1'," + cnt1 + ",true,true);"
										vjscmd = vjscmd + "{dialog.object}.populate(" + varToJSON(j) + ",1,false,true);"
										'this is a test to add it as a new record right and wait four seconds before populating it
										vjscmd = "{Dialog.Object}.newRecord();setTimeout(function(){" + vjscmd + "},1000);"
									else
										vjscmd = "alert('No details found for this layout');"
									end if	
						else
							vjscmd = "alert('No header record found.');"
						end if
				else
					vjscmd = "alert('Execute Error: Error returned was: " + js_escape(cn.CallResult.text) + "');"
				end if
				cn.Close()
		else
			vjscmd = "alert('Connection Error: Error returned was: " + js_escape(cn.CallResult.text) + "');"
		end if
	debug(2)		
getRecordData = vjscmd
end function

[Peter.Greulich]

Re: Result set fails on 2nd callback

Thanks Jinx. At this point I've run the clock up and the client may be running out of patience.

[-Jinx-]

Re: Result set fails on 2nd callback

I love not having clients!....well, paying ones anyway.