PowerBuilder SQL vs SA12 SQL?

[former_SQLA_member1694869]

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 ;

[chris_keating]

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.

[former_SQLA_member1694869]

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

[reimer_pods]

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

Can you describe, what you want to achieve?

[former_SQLA_member1694869]

Ideally, I want this in a procedure.

[VolkerBarth]

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

[former_SQLA_member1694869]

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

[VolkerBarth]

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.

[former_SQLA_member1694869]

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.

[VolkerBarth]

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;