Welcome to SQLA Beta

Question

3

Whats the best way to get the content of an out or inout blob parameter of a procedure in embedded sql? I wanted to use "EXEC SQL EXECUTE", but the problem is that i don't know the (maximum) size of the blob parameters. I can't use GET DATA, cause it only works with cursors. Setting the sqltype to DT_VARIABLE (and creating a corresponding connection variable) didn't work, cause you can only use them to send Blobs to the db, according to the documentation. The only way i came up with (aside from using a select and a cursor), is to preallocate some memory and hope it's enough.

edited Dec 17 at 9:44

Breck Carter♦♦
1,015●1●11

asked Dec 16 at 18:58

Markus Dütting
96●4

Is there more to know, about the constraints you are facing on the client side? There may not be a general all-purpose "best way", but there may be a best way for your exact situation. Note that "I don't like cursors" is a perfectly valid constraint, it's one I personally apply all the time :) – Breck Carter♦ Dec 17 at 9:50

I am writing a component in Delphi to access SQL Anywhere and decided to use the Embedded SQL interface. In this case i want to implement a generic "ExecSQL" method, which only uses parameters. A cursor in Embedded SQL is quite a complex thing requiring prepare, describe, declare, open, fetch, close and drop. A few of these functionalities can be executed in one call, but there is obviously a lot of overhead, when you only need a single row. So i don't "know" the SQL expression to execute and can't rewrite it accordingly and a cursor is not even possible when you use out or inout parameters – Markus Dütting Dec 17 at 12:11

Breck Carter · Answer 1 · 2009-12-17 09:43:26Z UTC

You might be able to guarantee that "it's enough" by using SUBSTR ( column, 1, n ) in your SELECT.

Other possibilities might be to push some logic onto the server; e.g., send a SELECT whatever INTO connection-variable, then grab chunks of the connection-variable... or write a procedure or function.

And now... it's time for someone who has a clue about embedded sql to take a shot :)

Markus Dütting · Accepted Answer · 2009-12-17 12:58:40Z UTC

I think i have found a possible way.

Issue a savepoint statement when there are out or inout blob parameters.
Reserve a "typical" amount of memory for all these params
Do the "EXEC SQL EXECUTE :stmtnum USING DESCRIPTOR sqlda1 INTO DESCRIPTOR sqlda2"
Check if the memory for all blob parameters was enough:
if yes than "release savepoint" and all is good
otherwise "rollback savepoint", reserve enough memory and try it again.

Not nice but should do it. Does anyone have a better idea?

How do i get the content of an out or inout blob parameter in embedded SQL?

2 Answers