I know it’s a very old thread, but the answer doesn’t seem to be here.
The “trick” is to use CALL and NORESULTCALL for regular statements: they don’t have to be real procedure calls. So you can do something like:
bind('nc',q:num)
bind('sc',q:str)
loop i# = 1 to records(myq)
get(myq,i#)
test_binding{PROP:SQL} = 'NORESULTCALL INSERT into test_binding values(&nc ,&sc)'
if error() then message(error() & fileerror()).
end ! loop
Without the keyword NORESULTCALL, Clarion will just pass the statement with the ampersands etc. With the keyword clarion will parse the string you are sending to look for bind variables, and will substitute them.
The question is, does the Clarion file driver just do a string replace within the sql statement or does it actually parameterize them in the sp_cursorprepexec?
They are binds, not string replacements. I would think that for MSSQL the whole point of using the prepare/execute method of doing stuff is that even regular clarion statements (like ADD) result in stored prepared statements that can be run multiple times with different bound values, so I would think that the benefit of using a PROP:SQL with bound variables would really not speed anything up using the MSSQL driver. The trace (for Oracle) looks like this:
02624H(1) 09:34:03.567 Parsing T9 Cursor 78949836 : BEGIN INSERT into test_binding values(:1,:2);END; Return Code: 0 Time Taken:0.00 secs
02624H(1) 09:34:03.567 Binding field 1 as type INT T9 Cursor 78949836 Return Code: 0 Time Taken:0.00 secs
02624H(1) 09:34:03.567 Binding field 2 as type VARCHAR(41) T9 Cursor 78949836 Return Code: 0 Time Taken:0.00 secs
02624H(1) 09:34:03.568 Executing T9 Cursor 78949836 Return Code: 0 Modified 1 row Time Taken:0.00 secs
but you can’t use it for “normal” select statements…it will end up in “ora-24333 zero iteration count” and without “CALL” in “ora-01008 not all variables bound” even if they are bounded by bind.
what would be the trick for select statements?
construct your SQL statement concatenating in whatever clarion variables you want (risks SQL injection).
Call a procedure first with bound parameters that fills a GTT and then just select from the GTT like you would a regular table (declare the GTT with on commit preserve rows)
Return a cursor from your procedure. Not as easy as it is in MSSQL but works fine.
An hybrid option of parameterizing a PROP:SQL with SQLCALLBACKs and ODBC Apis is possible but it is complex, much more than just binding fields and calling a store procedure. It would be very usesful if SoftVelocity could extend that option to non Store Procedure calls PROP:SQLs
I tried the third one but it works only for files not for views. The trace instead looks good, but I get a runtime error which ends in ClaRun.dll / ClaOra.dll…we need more transparency to help us ourself.
I use the native Oracle driver, so ODBC would not solve the problem.
The used SQL commands uses Oracle commands, don’t think that non ANSI-selects would work probably
If errorcode is 90:
“The file driver has detected some other error reported by the file system. You can use the FILEERRORCODE and FILEERROR procedures to determine exactly what native error the file system is reporting.”
no, as you said the error wasn’t clear, that gives a more clear description, good that you already looked at it. The previous comment the other day was ODBC related.