Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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
25.0k10142298

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.5k5417271050
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,723 times

last updated: 15 Mar '13, 19:00