SQL in GRID does not work - must be a function

[MichaelCarroll]

If you try to run a genie developed or a hand coded SQL statement inside "events", or inside the xbasic function declarations section of a grid, it does not work.

You have to write it as a function and compile it into a AEX file.

Why is this?

I can run it manually in my query editor, I can run it as a function, but not in the GRID.

Is there something that I am not understanding. All xbasic code runs on the server engine and not in the browser. Correct?

Thanks,

Michael Carroll

www.redrocksbluesky.com
[email protected]
435-275-5175

[RodM]

Re: SQL in GRID does not work - must be a function

Michael,

I run mySQL statements in Grid events all of the time. I have had my code bomb in a custom field but otherwise, it works fine.

Rod

[MichaelCarroll]

Re: SQL in GRID does not work - must be a function

Update - Ok getting this to work now! :)

Cannot use the <<%sql% .... %sql% notation that the genie creates. However if I keep everything inline between " " quotes then it works.

[frankbicknell]

Re: SQL in GRID does not work - must be a function

Update - Ok getting this to work now! :)

Cannot use the <<%sql% .... %sql% notation that the genie creates. However if I keep everything inline between " " quotes then it works.

Michael here are a couple of syntax examples that work well;

"select sum(NumericField) as Identifier from SqlTable where TableField = '"+CharacterVariable+"'"

"update SqlTable set TableField =(select sum(NumericField) from SqlTable where TableField = "+NumericVariable+") where TableField = "+NumericVariable+""

You can subtitute the character variables and numeric variables by not using hyphens and visa versa.

[MichaelCarroll]

Re: SQL in GRID does not work - must be a function

I am now getting this to work. Solution:

Build and test SQL statement inside of MySQL Workbench SQL Editor and then copy it to my xbasic code. So I write:

Sql = <<%a%

%a%

And then put my cursor on the line between Sql and %a% and paste the SQL Statement from the editor here. For some reason when I copy it over from the genie it causes freak out.

Also Jerry Brightbill showed me how to write the results to a txt file to further enhance my troubleshooting. These are the lines below cn.Execute(sql,args). One caveat is that if you are going to use cn.ResultSet rem out the test line as you can only use ResultSet once as it dumps its data. You would have to open the connection again.


So the code looks like this:

dim Sql as c
dim args as sql::arguments

dim xart as c

xart=e.DataSubmitted.ARTIST_ID
args.set("userid",xart)

[COLOR="blue"]Sql = <<%a%[/COLOR]
[COLOR="SeaGreen"]SELECT artist.id_staff, artist.first, artist.last, mobiletxt.suffix 
 FROM artist artist
 	 INNER JOIN mobiletxt mobiletxt
 		 ON  artist.cell_co = mobiletxt.id_mobiletxt
                        Where artist.id_staff = :Userid[/COLOR]
[COLOR="blue"]%a%[/COLOR]

dim cn as sql::connection

if cn.open("::Name::ConnectionName")
	cn.PortableSQLEnabled = .t.
	if cn.Execute(sql,args)
		'file.from_string("C:\sqltest_"+time("0m0s",now())+".txt",cn.ResultSet.toString(-1,-1))
	else
		'file.from_string("C:\sqltest_"+time("0m0s",now())+".txt",cn.CallResult.text)
	end if 
end if

Thanks for all the help from Jerry, and Others,

Michael Carroll