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
24.8k10139296

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
32.5k5407241050
accept rate: 20%

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:

×34
×23
×9

question asked: 16 Dec '09, 18:58

question was seen: 2,462 times

last updated: 15 Mar '13, 19:00