The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.

asked 16 Dec '09, 18:58

Markus%20D%C3%BCtting's gravatar image

Markus Dütting
53641220
accept rate: 30%

edited 15 Mar '13, 19:00

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262

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 :)

(17 Dec '09, 09:50) Breck Carter

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

(17 Dec '09, 12:11) Markus Dütting

I think i have found a possible way.

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

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

permanent link

answered 17 Dec '09, 12:58

Markus%20D%C3%BCtting's gravatar image

Markus Dütting
53641220
accept rate: 30%

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 :)

permanent link

answered 17 Dec '09, 09:43

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×21
×18
×8

question asked: 16 Dec '09, 18:58

question was seen: 916 times

last updated: 15 Mar '13, 19:00