I have a PowerBuilder code that I would make about the work in SA12. I've tried to do two "fetch" but it may nevertheless not work. Anyone know how this PB code looks like in SA?

Ex:

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
integer Emp_id_var
string Emp_state_var = "MA"
string sqlstatement

sqlstatement = "SELECT emp_id FROM employee "&
        +"WHERE state = ?"
PREPARE SQLSA FROM :sqlstatement ;
OPEN DYNAMIC my_cursor using :Emp_state_var ;
FETCH my_cursor INTO :Emp_id_var ;
CLOSE my_cursor ;

asked 04 Dec '16, 13:31

Rolle's gravatar image

Rolle
379283342
accept rate: 0%

1

This would depend on the programming interface being used to access SQL Anywhere. The PowerBuilder code you have provided is PB Embedded SQL but it is effectively a prepared statement with a fetch under the covers (based on -zr SQL logging).

SQL Anywhere has an embedded SQL interface that matches closely with the PB code. It would be implemented C/C++ that needs to be preprocessed. However, most if not all programming interfaces support the concept of a prepared statement and row fetches. You can also achieve this in a stored procedure using similar code.

(05 Dec '16, 10:27) Chris Keating

Ok, tanks for the answer. If I do like this in SA, do you have any example?

(05 Dec '16, 10:54) Rolle

What does 'do like this in SA' mean exactly? Build a SELECT statement in ISQL?

Can you describe, what you want to achieve?

(06 Dec '16, 08:44) Reimer Pods

Ideally, I want this in a procedure.

(06 Dec '16, 08:46) Rolle
Replies hidden

So based on your sample, you want to have a procedure that returns a result set with one single column (so here a set of IDs) based on one single parameter (here the state)?

(06 Dec '16, 09:11) Volker Barth

Yes, that's enough. I just want to see the principle in SA. If you want, feel free to add more columns

(06 Dec '16, 09:31) Rolle
Replies hidden

Have a look at those DCX samples:

Information returned in result sets from procedures

The second example is quite similar to your original sample - basically you define the required parameters and their data types, and then you define the result set and supply the according SELECT statement.

You can then call the procedure via

CALL MyProcName(MyParam1, MyParam2);

or use it within a FROM clause or whatever.

(06 Dec '16, 10:24) Volker Barth

OK thanks. But I had probably been a little fuzzy. What I want is a similar example in SA with the cursor. That is, how do I do this in the same way with the cursor and open dynamic.

(06 Dec '16, 14:07) Rolle
3

Hm, I don't really understand what you are trying to achieve...

However, of course you can make use of an explicit cursor in SQL Anywhere, and with a dynamically built query, too - here's a sample from the docs with the USING clause:

CREATE PROCEDURE get_table_name(
  IN id_value INT, OUT tabname CHAR(128)
)
BEGIN
  DECLARE qry LONG VARCHAR;

  SET qry = 'SELECT table_name FROM SYS.SYSTAB ' ||
            'WHERE table_id=' || string(id_value);
  BEGIN
    DECLARE crsr CURSOR USING qry;
    OPEN crsr;
    FETCH crsr INTO tabname;
    CLOSE crsr;
  END
END;
(07 Dec '16, 00:49) Volker Barth
More comments hidden
showing 5 of 9 show all flat view
Be the first one to answer this question!
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:

×35

question asked: 04 Dec '16, 13:31

question was seen: 300 times

last updated: 07 Dec '16, 00:49