Page 1 of 1
sqlexec question
Posted: Mon Oct 19, 2020 5:21 pm
by kevclark64
I'm getting an error with an SqlExec call. I think it was stated before that rather than using AError as in VFP, the exception would be contained in RuntimeState.LastRddError. However, when I try to get the error it appears to be null. My code is:
Code: Select all
local iReturn as int
iReturn=SQLEXEC(g_sqlhandle,tmpline,"tmpfilPlaceStatus",aCountInfo)
if iReturn>0 then
...
ELSE
local lException as exception
lException=RuntimeState.LastRddError
// lException is null at this point
ENDIF
How can I get the error generated from the SQL call?
sqlexec question
Posted: Mon Oct 19, 2020 5:58 pm
by robert
Kevin,
The error should indeed be visible in the LastRddError (for now).
What is the value of tmpline ?
Robert
sqlexec question
Posted: Mon Oct 19, 2020 7:50 pm
by kevclark64
The query I am running is:
Code: Select all
select si.*,sr.*,id.*,es.studentnum,sr.datecreated as submitdate from invlinedata id,setonassessmentinstance si,setonassessmentresult sr, enrollmentstudent es where usertype='invline.uniqueid' and id.invlineid=si.userid::int and si.setonassessmentinstanceid=sr.setonassessmentinstanceid and es.uniqueid=id.enrollmentstudentid and si.deleted<>'TRUE' and emailsent is null and (sr.status<$$63$$ or sr.status is null) and (right(si.stocknum,1)=$$1$$ or right(si.stocknum,1)=$$3$$) and sr.datecreated>='09/04/2020'
This is being run against a Postgres database. From within Foxpro using SqlExec it is valid and returns records.
sqlexec question
Posted: Mon Oct 19, 2020 9:03 pm
by robert
Kevin,
Well, when this works with FoxPro then it should also work with X#, assuming everything else the same, so you use the same connection string etc.
What we/you could do is link the Sql..() functions and their support classes in sourcecode and run the query through the debugger.
Or you create an example that I can look at over here.
Or we setup a shared session (MsTeams, Zoom or something like that ) and you let me debug your app from a distance.
Just out of curiosity:
Why are there $$ characters in the string. Should these be replaced with a double quote ? And did you do that ? Or does Postgress do that ?
And should there be single quotes around invline.uniqueid ?
And is ::int the syntax for Postgress to cast a value to an int ?
Robert
sqlexec question
Posted: Tue Oct 20, 2020 2:41 am
by lumberjack
Robert,
robert wrote:
Why are there $$ characters in the string. Should these be replaced with a double quote ? And did you do that ? Or does Postgress do that ?
PG has what is called Dollar Quotes, the $$ can be replaced by a Single Quote in his example.
And should there be single quotes around invline.uniqueid ?
If usertype is a string then yes single quotes are required.
And is ::int the syntax for Postgress to cast a value to an int ?
Correct, PG uses ::TypeToCastTo, for casting of values to another type.
sqlexec question
Posted: Wed Oct 21, 2020 2:56 pm
by kevclark64
After doing more research on this, any query I try which includes a cast fails to execute. I tried casting as int, text, and char and they all fail.