Re: [eclipse-clp-users] Dbi prepare query issue.

Dear Kish,

I have read the document and tried all combinations possible. Even Result2 was replaced by an integer. I know cursor execute would just run the query. But even that wasn't happening. Sqlprepare had an issue on windows version some time back so I thought I should confirm.

Thanks,
Shrirang

On 09-Mar-2016, at 5:37 AM, Kish Shen <kish.shen_at_...6...<mailto:kish.shen_at_...183.....6...>> wrote:

Shrirang,

>Kindly let me know what could be wrong.

It looks like you are not using cursor_next_execute/2 correctly. Have you read the documentation for lib(dbi) -- both the chapter in the embedding and interface manual

http://eclipseclp.org/doc/embedding/embroot059.html

and the reference documentations for the library predicates, such as cursor_next_execute/2:

http://eclipseclp.org/doc/bips/lib/dbi/cursor_next_execute-2.html

both these explains how to use prepared SQL statements, and should have the information on your problem..

I think the reason you are getting the bad template error is because you are calling cursor_next_execute(Cursor, Result1) with Result1 being count(Result2) where Result2 being a variable. Judging from your code, you seem to be expecting cursor_next_execute/2 to be returning results from some results to be returned from the prepared SQL query. This is incorrect-- cursor_execute/2 is actually used to *execute* the prepared SQL statement from session_sql_prepare/4: this should be clear from the summary of the reference documentation:

cursor_next_execute(++Cursor, +Tuple)
Executes the parametrised prepared SQL statement represented by Cursor.

Cursor
A cursor handle
Tuple
A tuple of parameter values matching the template for this cursor (structure)

The second argument, which you call Resuts1, is actually there to supply the parameter value for the prepared SQL statement represented by Cursor - the ? in your  "select count(column) from table where column > ?" so in your case, if you call cursor_next_execute(Cursor, count(7), that will execute the SQL query

"select count(column) from table where column > 7".

Your code is also missing a predicate such as cursor_all_tuples/2 to collect the results from the SQL query. Again, this is stated clearly in the reference documentation:

>If the SQL statement is a query, and was prepared as a query using session_sql_prepare_query/5, results can be obtained from the query by the cursor_*_tuple family of predicates.

Cheers,

Kish



On Tue, Mar 8, 2016 at 4:28 AM, Edgaonkar, Shrirang <Shrirang.Edgaonkar_at_...344...390...<mailto:Shrirang.Edgaonkar_at_...390...>> wrote:

Dear clp users,



  I am facing a problem with prepare statement in 32 bit as well as 64 bit Windows OS using respective ECLipSe.

Eclipse version is 6.1 217. Kindly let me know what could be wrong. Please find the console output at the end of the email.



:- lib(dbi).
:- lib(ic).
solveDB(Result):-
    Result2 :: 1..10,
    Template = count(24),
    session_start("blah"_at_"blah<mailto:%22blah%22_at_%22blah>", "blah", [dbname:"blah"], Session),
 writeln("TEST"),
    SQL = "select count(column) from table where column > ?",
 writeln("TEST1"),
    session_sql_prepare(Session, Template, SQL, Cursor),
 writeln([Cursor,"TEST2"]),
 Result1 = count(Result2),
    cursor_next_execute(Cursor, Result1),
 writeln(Result1),
    cursor_close(Cursor).



----------------------------------------------------------------------------------------------------------------



console output:-



TEST
TEST1
[cursor('MySQLC'(16'327ee0), 'MySQLS'(16'327e40)), TEST2]
DBI General exception Code:-5 in cursor_next_exec(cursor('MySQLC'(16'327ee0), 'M
ySQLS'(16'327e40)), count(_138309), options(_138404, _138405, "client", "read_on
ly"))
DBI-005: bad template
Aborting execution ...
abort

Thanks and Regards,

Shrirang Edgaonkar



______________________________________________________________________
Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.

---------------------------------------------------------------------------

______________________________________________________________________
Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.
---
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://makebettercode.com/inteldaal-eval
_______________________________________________
ECLiPSe-CLP-Users mailing list
ECLiPSe-CLP-Users_at_lists.sourceforge.net<mailto:ECLiPSe-CLP-Users_at_...404...ceforge.net>
https://lists.sourceforge.net/lists/listinfo/eclipse-clp-users




______________________________________________________________________
Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.