sql Select statement

[steve745]

I'm trying to select from an sql table but my args aren't passing for some reason.

DIM args as sql::arguments
if a5_eval_valid_expression("=Var->ext",local_variables()) then 
	args.add("extn",convert_type(a5_eval_expression("=Var->ext",local_variables()),"C"))
end If

'Specify if you are using Portable SQL syntax, or not
cn.PortableSQLEnabled = .f. 
dim sqlStatement as c 
sqlStatement = <<%txt%
SELECT *  FROM callbacks  WHERE (cback = curdate()) AND (repid = :extn)
%txt%

ext is a global variable and it's showing value in the interactive window. if i take out the AND (repid= :extn) the code fires. but i need the AND argument in there. Anyone know where i am going wrong here?

[Steve Wood]

Re: sql Select statement

Add save_to_file(sqlStatement,"c:\test.txt") so you can see what your select statement actually says at runtime.

also save cn.callresult.txt to a file to show the exact message produced when you try to execute that statement.

I also take the output from my select statement (the one you get in the test.txt file above) and try to execute it directly against the database using their command line syntax to see what the database kicks back.

[steve745]

Re: sql Select statement

SELECT * FROM callbacks WHERE (cback = curdate()) AND (repid = :extn)

Thats what i get on the txt file it's no passing my var as an argument.

Here is the Full code

'Execute a query on a SQL database
'DIM a connection variable
DIM cn as SQL::Connection
dim flagResult as l 
flagResult = cn.open("::Name::SeeC")
if flagResult = .f. then 
	ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
	end 
end if
DIM args as sql::arguments
if a5_eval_valid_expression("=Var->ext",local_variables()) then 
	args.add("extn",convert_type(a5_eval_expression("=Var->ext",local_variables()),"C"))
end If

'Specify if you are using Portable SQL syntax, or not
cn.PortableSQLEnabled = .f. 
dim sqlStatement as c 
sqlStatement = <<%txt%
SELECT *  FROM callbacks  WHERE (cback = curdate()) AND (repid = :extn)
%txt%
save_to_file(sqlStatement,"c:\test.txt")
'Execute the Query
'flagResult = cn.Execute(sqlStatement)
'if flagResult = .f. then 
	'ui_msg_box("Error",cn.CallResult.text)
	'cn.close()
	'end 
'end if 
dim rs as sql::resultset
rs = cn.ResultSet
resultP = SQL_QueryImport(cn,sqlStatement,"C:\Database\dialersql\tstcb.DBF")
cn.close()

[Steve Wood]

Re: sql Select statement

Ahh, S/b cn.execute(selectstmt,args)

[steve745]

Re: sql Select statement

resultP = SQL_QueryImport(cn,sqlStatement,args,"C:\Database\dialersql\tstcb.DBF")

error says variable is of different type. I'm really confused

[Steve Wood]

Re: sql Select statement

I'm in a park with my kids so don't have resources, but its just syntax. Yo must have a DIM args as SQL::Arguments as well, but could be other syntax issues.

[steve745]

Re: sql Select statement

you must have a kewl iphone or something...
I have

DIM args as sql::arguments
if a5_eval_valid_expression("=Var->ext",local_variables()) then 
	args.add("extn",convert_type(a5_eval_expression("=Var->ext",local_variables()),"C"))
end If

[steve745]

Re: sql Select statement

Ok so i have found a way to make my sqlstatement work. I was reading the help file and seen something about using and expression as sqlstatement

DIM cn as SQL::Connection
dim flagResult as l 
flagResult = cn.open("::Name::SeeC")
if flagResult = .f. then 
	ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
	end 
end if
'DIM args as sql::arguments
'DIM variables
'if a5_eval_valid_expression("=Var->ext",local_variables()) then 
	'args.add("repid",convert_type(a5_eval_expression("=Var->ext",local_variables()),"C"))
'end If
'Specify if you are using Portable SQL syntax, or not
cn.PortableSQLEnabled = .f. 
dim sqlStatement as C 
sqlStatement = "SELECT *  FROM callbacks  WHERE cback =curdate() AND repid="+ext  

'Execute the Query
'flagResult = cn.Execute(sqlStatement)
'if flagResult = .f. then 
	'ui_msg_box("Error",cn.CallResult.text)
	'cn.close()
	'end 
'end if 
dim rs as sql::resultset
rs = cn.ResultSet
resultP = SQL_QueryImport(cn,sqlStatement,"C:\Database\dialersql\tstcb.DBF")
cn.close()

works great this way not sure if i went about it the correct way but seems to work for my needs.