does sybase 12 to 17 not support multiple active result sets? like other can do in mysql or postgresql!? USING ODBC!!!!

can have multiple default result sets open and can interleave reading from them without closing the first opened? by example using JAva or Php? USING ODBC!

I CITED AS EXAMPLE FOR WINDOSERS USERS: in Mocosoft SQLserver you can open a cursor and then iterate thorught results, and before close it, make another query and then iterate in second cursor result!

in sybase using odbc i having troubles with that! there's no support for "sub cursor fetch" in sybase n this way?

i can belevie that software libre projects have more avanced in this like postgresql and mysql.. seems opensource are more powered today!

edited: clarify that its using ODBC due its not windoze related, its OS agnostic! ok!

asked 17 Jul '17, 10:28

mckaygerhard's gravatar image

mckaygerhard
13127
accept rate: 12%

edited 19 Jul '17, 08:19

AFAIK support for multiple active result sets on a single connection is unique to Microsoft SQL Server (which isn't open source the last time I checked)...

I can't find support for it in postgresql or mysql or anything else, which might be an indication of its popularity or lack thereof.

Have you tried opening a second connection from your application? ...or are you trying to migrate from SQL Server to SQL Anywhere?

(17 Jul '17, 14:32) Breck Carter
Replies hidden

1) support for multiple active result sets are not unique to mocosoft sqlserver, postgres mariadb and mysql supports easyle..

2) sure u are not a linux users avocate.. it can easyle corroborate making a few lines of code.. that's why i put this question here!

3) i OPENED A SECOND CONNECTION and does not permit making query only with sybase (either Anywhere or SAP) , i'm not migrate from mocosoft sqlserver.. and i never will use mocosoft for a serius proyect

(17 Jul '17, 17:11) mckaygerhard
3

Please show us an example of the "few lines of code" that support MARS in anything other than SQL Server... I am very interested!

Please provide links showing that PostgreSQL and MySQL support multiple active result sets.

All I can find in Google are a few indirect complaints...

"Even in the longer term, PostgreSQL doesn't really provide facilities for implementing MARS." - Implement MARS (Multiple Active Result Set)

"I assume MySql connector doesn't support MARS (Multiple active result sets)." - Mysql connector - MultipleActiveResultSets issue

(18 Jul '17, 02:23) Breck Carter

i dont use mocosoft sqlserver, i cited the "mars" as a point to all of windosers users understand.. but taking in consideration your shot eyes see it: https://github.com/FreeTDS/freetds/issues/131

(18 Jul '17, 06:55) mckaygerhard
Replies hidden
1

> AFAIK support for multiple active result sets on a single connection is unique to Microsoft SQL Server

I blame lack of coffee for that comment... AFAIK SQL Anywhere has always let you have multiple cursors open on a single connection (see the example code posted as an answer to this question).

The subject of "multiple result sets returned by a single procedure call" is a whole other kettle of fish. As far as I know, those result sets can't be interleaved or nested, the best that can happen is part or all of an early result set can be abandoned or skipped before starting to fetch a later result set... but it is still sequential... or am I wrong again? :)

As Volker said, this is an interesting topic, too bad mckaygerhard doesn't have much to say that's useful

(18 Jul '17, 07:46) Breck Carter

I don't think FreeTDS works with SQL Anywhere very well (or at all, after SQL Anywhere 7).

Try using an interface that is actually supported... FreeTDS is not.

(18 Jul '17, 08:29) Breck Carter
2

Please stop making personal insults.

(18 Jul '17, 08:30) Breck Carter
showing 2 of 7 show all flat view

based on all the comments here seems the conclusion its that are not supported! THE QUESTION WAS IF THERE ARE SUPPORT FOR OPEN SIMULTANEOS TWO OR MORE CURSORS ONE BETWEEN OTHER, not if something can give "tricks" to workaround some that are not supported!

until now! no one reponse can give me a clear anwers and the ASE / sybase or anywhere seems does not support any of the commmont great features of mysql?

CITED: not in powerbuilder and not with the sahred builder lib, i mean USING ODBC WITH COMMON STANDAR LANGUAGES LIKE Php, JAVA, BASIC, not the crappy paid only powerbuilder that does not support sqlite and other modern features!

how great its the free software..

permanent link

answered 18 Jul '17, 06:59

mckaygerhard's gravatar image

mckaygerhard
13127
accept rate: 12%

edited 19 Jul '17, 08:25

2

I like that you have selected a wrong answer and it suits your needs. Probably you don't know how to use the open source software correctly. That is why I am helping you for free: MySql doesn't support MARS.
But you can always use this feature regardless the MySql has it or not.
And yes, I don't like the way how you talk with us. That is why I have given you a negative vote to your wrong answer.

(18 Jul '17, 07:08) Vlad
Replies hidden
2

I don't like the way how you talk with us.

Well, I don't think mckay is "talking with us" at all, it's more like "to us", and probably not "talking" at all... - and certainly not giving responses to our questions... But of course ranting is free here, too.

(Besides that, even such a question can trigger interesting discussions.)

(18 Jul '17, 07:33) Volker Barth
1

I have gone through some of his answers or questions. It seems to me that he is just writing something that slightly resembles sentences. When I am trying to understand the task to help with, I always come to conclusion that he is trying to run SqlAnywhere on a unpowered washing machine sunk deep in the ocean :)

(18 Jul '17, 07:42) Vlad
Comment Text Removed

mysql does not support mar4s due mysql and MARS are not related! please men

(18 Jul '17, 13:53) mckaygerhard

i need a solution to the problem but i dont see any working for that, its very frustrating trying to provide a piece of software that solves some problem when the original software are so close to solutions..

(18 Jul '17, 13:58) mckaygerhard
return (your_answer != your_comment); // TODO: expected false, have to write tests and put assert(...)
(18 Jul '17, 14:09) Vlad

mysql and mars are not related, i ask if syabase suport sub-cursor openeds, and does not as demostrate two anwers here! with cited document in easysoft write !

(18 Jul '17, 14:10) mckaygerhard

@Vlad as i posted in edit, the MARS was as example, not a feature of mysql.. of course its common on those that has no vision...

(18 Jul '17, 16:41) mckaygerhard

To emphasis what Vlad said: THIS ANSWER IS WRONG WRONG WRONG WRONG WRONG.

There are two other answers that contain actual code showing SQL Anywhere can process multiple open cursors on a single ODBC connection, both interleaved (open1, open2, fetch1, fetch2, fetch1, fetch2, close1, close2) and nested (open1, fetch1, open2, fetch2, close2, fetch1, open2, fetch2, close2, close1).

This ability has existed in SQL Anywhere for many years, and claims to contrary are false.

(19 Jul '17, 08:07) Breck Carter
Replies hidden

u are wrong, the code use as example in powerbuilder or shell always use OWN shared lib, not a real odbc.. and ITS NOT WITH POWERBUILDER OR ODBC

you must read carefullt the cuestion! i cites in other languajes as php, java, not the crapy closed paid software of powerbuilder..

(19 Jul '17, 08:21) mckaygerhard
2

> powerbuilder ... always use OWN shared lib

Yes, PowerBuilder application IDE uses its "own" ODBC API, that's how ODBC works.

Every application IDE provides its own "ODBC API", and every RDBMS provides its own "ODBC Driver", and the (theoretical) beauty of ODBC was that any application could then connect to any RDBMS without having to build a separate "database interface module" for each IDE/RDBMS pair.

ODBC was created by proprietary software vendors: primarily Microsoft, plus Tandem, DEC, Sybase and others.

In the examples provided here, PowerBuilder uses PBODB115.DLL for the "ODBC API", together with the pbodb115.ini configuration file, and SQL Anywhere uses "DBODBC16.DLL" for the ODBC Driver.

> i cites in other languajes as ... java

If you are trying to use ODBC to connect between java and SQL Anywhere, you may not have much success... SQL Anywhere 16 offers JDBC support instead of ODBC for Java, via two drivers: the recommended "SQL Anywhere JDBC 4.0 driver" which uses the Command Sequence protocol, and the pure Java "jConnect" driver which uses TDS.

> paid software

SQL Anywhere falls into that category. Do you even have a copy, or are you just pretending to have a problem? :)

References...

Understanding ODBC Architecture - https://msdn.microsoft.com/en-us/library/aa266933(v=vs.60).aspx

Open Database Connectivity - https://en.wikipedia.org/wiki/Open_Database_Connectivity

SQL Anywhere 16 ODBC support - http://dcx.sybase.com/index.html#sa160/en/dbprogramming/pg-odbcdev.html

SQL Anywhere 16 JDBC support - http://dcx.sybase.com/index.html#sa160/en/dbprogramming/pg-jdbc.html

(20 Jul '17, 03:35) Breck Carter

Ahaha, you said "crapy closed paid software". How funny?!

Eh...

I am sorry, but I couldn't pass this sentence. Especially when I saw a project that you should know. ;-) a tasteless clone of the crapy closed paid software...

Be careful with what you write in the forum.

(21 Jul '17, 11:36) Vlad
showing 2 of 12 show all flat view

I don't know if this qualifies for an acronym as fancy as "MARS", but here's some code that implements two parallel interleaved FETCH loops on one single connection (SQLCA) to SQL Anywhere 16...

(as far as I know this has always been supported)


Update: This script uses only one single "SQL communication area" named SQLCA, and only has one CONNECT statement, which means it only has one connection to SQL Anywhere. This fact is emphasized by all the messages "...the SQL Anywhere connection number is...".
// Interleaved cursors in PowerBuilder 11.5.2506 ODBC and SQL Anywhere 16.0.0.2344

Integer li_row_num = 0 String ls_result_sets = "" String ls_sql = ""

SQLCA.DBMS = 'ODB'

SQLCA.DBParm & = "ConnectString='Driver=SQL Anywhere 16;" & + "UID=dba; PWD=sql; DBN=ddd16; ENG=ddd16'," & + "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"

CONNECT USING SQLCA; EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 1 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

DECLARE cursor1 CURSOR FOR SELECT row_num FROM rowgenerator WHERE row_num BETWEEN 1 AND 10 USING SQLCA; EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 2 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

OPEN cursor1; EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 3 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

FETCH cursor1 INTO :li_row_num; ls_result_sets = String ( li_row_num ) EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 4 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

DECLARE cursor2 CURSOR FOR SELECT row_num FROM rowgenerator WHERE row_num BETWEEN 101 AND 110 USING SQLCA; EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 5 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

OPEN cursor2; EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 6 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

FETCH cursor2 INTO :li_row_num; ls_result_sets = ls_result_sets + ', ' + String ( li_row_num ) EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 7 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

DO WHILE li_row_num < 110

FETCH cursor1 INTO :li_row_num;
ls_result_sets = ls_result_sets + ', ' + String ( li_row_num ) 
EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 8 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

FETCH cursor2 INTO :li_row_num;
ls_result_sets = ls_result_sets + ', ' + String ( li_row_num ) 
EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 9 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

LOOP EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 10 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

CLOSE cursor1; EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 11 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

CLOSE cursor2; EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 12 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

ls_sql = "MESSAGE '" + ls_result_sets + "' TO CONSOLE" EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 13 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

EXECUTE IMMEDIATE :ls_sql USING SQLCA; EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 14 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

MessageBox ( 'ls_result_sets', ls_result_sets ); EXECUTE IMMEDIATE "MESSAGE STRING ( 'At script location 15 the SQL Anywhere connection number is ', @@SPID ) TO CONSOLE" USING SQLCA;

At script location 1 the SQL Anywhere connection number is 5 At script location 2 the SQL Anywhere connection number is 5 At script location 3 the SQL Anywhere connection number is 5 At script location 4 the SQL Anywhere connection number is 5 At script location 5 the SQL Anywhere connection number is 5 At script location 6 the SQL Anywhere connection number is 5 At script location 7 the SQL Anywhere connection number is 5 At script location 8 the SQL Anywhere connection number is 5 At script location 9 the SQL Anywhere connection number is 5 At script location 8 the SQL Anywhere connection number is 5 At script location 9 the SQL Anywhere connection number is 5 At script location 8 the SQL Anywhere connection number is 5 At script location 9 the SQL Anywhere connection number is 5 At script location 8 the SQL Anywhere connection number is 5 At script location 9 the SQL Anywhere connection number is 5 At script location 8 the SQL Anywhere connection number is 5 At script location 9 the SQL Anywhere connection number is 5 At script location 8 the SQL Anywhere connection number is 5 At script location 9 the SQL Anywhere connection number is 5 At script location 8 the SQL Anywhere connection number is 5 At script location 9 the SQL Anywhere connection number is 5 At script location 8 the SQL Anywhere connection number is 5 At script location 9 the SQL Anywhere connection number is 5 At script location 8 the SQL Anywhere connection number is 5 At script location 9 the SQL Anywhere connection number is 5 At script location 10 the SQL Anywhere connection number is 5 At script location 11 the SQL Anywhere connection number is 5 At script location 12 the SQL Anywhere connection number is 5 At script location 13 the SQL Anywhere connection number is 5 1, 101, 2, 102, 3, 103, 4, 104, 5, 105, 6, 106, 7, 107, 8, 108, 9, 109, 10, 110 At script location 14 the SQL Anywhere connection number is 5 At script location 15 the SQL Anywhere connection number is 5

permanent link

answered 18 Jul '17, 07:34

Breck%20Carter's gravatar image

Breck Carter
32.5k5407241050
accept rate: 20%

edited 19 Jul '17, 07:17

1

Take my vote for the example. Now I know how to use server-side cursors with the rowgenerator.
To be honest, I was always told that cursors are a bad practice that I have never used in the software I barely develop :)
when I have read this question I though - what?! nested cursors?! but why?!

(18 Jul '17, 07:46) Vlad
Replies hidden

> nested cursors

Wait, you want to see nested cursors? SQL Anywhere can do that too! :)

(18 Jul '17, 08:03) Breck Carter

Enough for today. Thanks. I have to digest what I have just seen :)

(18 Jul '17, 08:08) Vlad
1

> server-side cursors

Just to clarify: The example is entirely coded in PowerScript embedded SQL on the client side, not Watcom SQL inside the server.

(18 Jul '17, 08:14) Breck Carter

that I have never used in the software

AFAIK, if you consume some kind of result set, it does almost always mean you are using a cursor (although the API might hide that fact). - It's just that they are more "visible" within SQL code blocks, aka server-side code...:)

(18 Jul '17, 08:32) Volker Barth
Comment Text Removed

@Breck: So does that code run against a MS SQL Server database, too? Or only with MARS enabled?

That might really answer the OP's question, if that is a question at all:)

(18 Jul '17, 08:36) Volker Barth

oh, I have missed this. Thanks.

(18 Jul '17, 08:56) Vlad

I haven't looked at the API for a long time. Yes, you are correct. Here is the help reference: DCX: Cursor principles. I have found two types of the result set that you can return from SA: either the cursor or the whole result set that behaves as an in-memory table.

(18 Jul '17, 09:09) Vlad

finally someting that apport some really work! many thanks!

i dont use powerbuilder due are very spensive but the code make sense so great, thanks, i noted that cursor1 and cursor2 seems (based on technical info very vage due are not open) its like handle different connections..

(18 Jul '17, 14:02) mckaygerhard
showing 3 of 9 show all flat view

UPDATE: That was originally posted as an comment on Breck's second answer and mckay's reply.

While I might get down-voted again for not offering any "real code", I think it's important to supply an answer to the original question - in contrast to the accepted wrong answer. Take your own choice:)

END UPDATE

mckay replied to Breck's first sample:

i dont use powerbuilder due are very spensive but the code make sense so great, thanks, i noted that cursor1 and cursor2 seems (based on technical info very vage due are not open) its like handle different connections..

That's what you commented on Breck's other answer. And now he has taken the time and effort to show you that your assumption (that SQL Anywhere uses different connections to handle nested cursors) is wrong: It does that within the same connection (as proved by the same connection number used in both cursor loops), something MS SQL Server AFAIK can only do with the mentioned MARS enabled.


So my conclusion is: SQL Anywhere CAN DO what you seem to be asking for, out of the box.

In my world, I would give a big thanks for all folks here who tried to answer your (rather vague) question.

permanent link

answered 19 Jul '17, 09:00

Volker%20Barth's gravatar image

Volker Barth
39.7k357545815
accept rate: 34%

edited 19 Jul '17, 11:25

thans for your answer Volker, but i not used powerbuilder, i edited the main question to clarify the issue..

i tried the code as teh example and if there not do as easysoft said, the app code hangs.. in the case of gambas raised and error, in the case of VS .lnet 2008 hangs the computer (wel li don not use guindows so many) in mac with gambas or php code raised and error..

(19 Jul '17, 15:16) mckaygerhard
Replies hidden
1

Show us JUST the code that doesn't work AND the exact error message(s). NOTHING ELSE, please.

Te quejas demasiado.

(19 Jul '17, 16:05) Breck Carter

"To whom it may concern":

(19 Jul '17, 17:51) Volker Barth

Here's another link to that issue. To cite from that:

Although this is a generic document about using Multiple Active Statements (MAS) with DBD::ODBC, the principal focus is on using MAS with Microsoft SQL Server, as ODBC drivers for most other databases support MAS out of the box.

Note, I don't use EasySoft, so I can't validate that claim.

However, in my experience (mainly with MS SQL Server, SQL Anywhere and - years ago - with ASE), I've come across such restrictions (particularly the famous "Connection is busy with results for another hstmt" error message) only with MS SQL Server, not SQL Anywhere... - the latter allows several active statements per connection, whereas MS SQL Server seems to allow that only when MARS is enabled.

So in my understanding, you can have parallel active result sets on the same connection with SQL Anywhere as long as you use different queries/HSTMTs/whatever your API uses for a "statememnt handle".


I don't have any technical knowledge about database driver design but I think the limitation has to do with the fact that SQL Server (at least when using "default result sets") expects the client to consume the whole result set whereas SQL Anywhere excepts the client to only read as much of a result set as it desires. Mark has explained those differences in detail here.

permanent link

answered 18 Jul '17, 03:38

Volker%20Barth's gravatar image

Volker Barth
39.7k357545815
accept rate: 34%

edited 18 Jul '17, 04:12

The question Mark answered dealt with multiple sequential result sets, not multiple active (nested) result sets.

In particular, it didn't deal with interleaved fetches from two result sets, which is what MARS supports... I think... :)

(18 Jul '17, 05:51) Breck Carter
Replies hidden

That reply of Mark's (to the question Volker linked to) is very interesting. I certainly didn't know that MSSQL made you consume the entire result set whatever. Sounds quite unpleasant!

(18 Jul '17, 05:54) Justin Willey
Replies hidden

Breck, I'm aware of that. Nevertheless, reading the cited EasySoft article (including the explanation of MS SQL Server "firehose" cursors), I was reminded of Mark's answer, and - re-stating my claim "I don't have any technical knowledge about database driver design" - I very wildly guessed that it might have to do with that.

As stated, the message "Connection is busy with results for another hstmt" has met me often with SQL Server but never with SQL Anywhere.


And now I hope some expert like Mark or Jack are about to really answer the question.:)

(18 Jul '17, 06:11) Volker Barth

I guess you can change that behaviour by using a fitting cursor type on the client side. In case you really need the entire result set, it is probably a very efficient approach, - otherwise not so:)

(18 Jul '17, 06:14) Volker Barth

"I certainly didn't know that MSSQL made you consume the entire result set whatever" please sybase anywhere does the same! that's why i asking here its there any support or not! due seems mysql have a good support of..

(18 Jul '17, 06:57) mckaygerhard
Replies hidden

Put up, or shut up: show us a working example of the code that SQL Anywhere won't support.

...or are you all hat and no cattle?

(18 Jul '17, 07:48) Breck Carter
showing 3 of 6 show all flat view

Here's another example, this time with "cursor2" nested inside "cursor1"; i.e., cursor2 is declared, opened, fetched and closed for each row fetched from cursor1. The DECLARE for cursor2 uses the value fetched from cursor1 in the WHERE clause.

Note that this code is written in PowerScript and runs on the client side, not inside the SQL Anywhere server.


Update: This script uses only one single "SQL communication area" named SQLCA, and only has one CONNECT statement, which means it only has one connection to SQL Anywhere. This fact is emphasized by all the messages "...the SQL Anywhere connection number is...".

// Nested cursors in PowerBuilder 11.5.2506 ODBC and SQL Anywhere 16.0.0.2344

Integer li_row_num1 = 0
Integer li_row_num2 = 0
String ls_result_sets = ""
String ls_sql = ""

SQLCA.DBMS = 'ODB'

SQLCA.DBParm &
    = "ConnectString='Driver=SQL Anywhere 16;" &
    + "UID=dba; PWD=sql; DBN=ddd16; ENG=ddd16'," &
    + "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"

CONNECT USING SQLCA;
EXECUTE IMMEDIATE 
    "MESSAGE STRING ( 'At location 1 the connection number is ', @@SPID ) TO CONSOLE" 
    USING SQLCA;

DECLARE cursor1 CURSOR FOR
    SELECT row_num
    FROM rowgenerator
     WHERE row_num BETWEEN 1 AND 5
     USING SQLCA;
EXECUTE IMMEDIATE 
    "MESSAGE STRING ( 'At location 2 the connection number is ', @@SPID ) TO CONSOLE" 
    USING SQLCA;

OPEN cursor1;
EXECUTE IMMEDIATE 
    "MESSAGE STRING ( 'At location 3 the connection number is ', @@SPID ) TO CONSOLE" 
    USING SQLCA;

DO WHILE li_row_num1 < 5

    FETCH cursor1 INTO :li_row_num1;
    EXECUTE IMMEDIATE 
        "MESSAGE STRING ( 'At location 4 the connection number is ', @@SPID ) TO CONSOLE" 
        USING SQLCA;

    IF ls_result_sets = "" THEN
        ls_result_sets = String ( li_row_num1 )
    ELSE
        ls_result_sets = ls_result_sets + ', ' + String ( li_row_num1 ) 
    END IF

    DECLARE cursor2 CURSOR FOR
         SELECT row_num
         FROM rowgenerator
         WHERE row_num BETWEEN :li_row_num1 + 100 AND :li_row_num1 + 102
         USING SQLCA;
    EXECUTE IMMEDIATE 
    "MESSAGE STRING ( 'At location 5 the connection number is ', @@SPID ) TO CONSOLE" 
    USING SQLCA;

    OPEN cursor2;
    EXECUTE IMMEDIATE 
    "MESSAGE STRING ( 'At location 6 the connection number is ', @@SPID ) TO CONSOLE" 
    USING SQLCA;

    FETCH cursor2 INTO :li_row_num2;
    ls_result_sets = ls_result_sets + ', ' + String ( li_row_num2 ) 
    EXECUTE IMMEDIATE 
    "MESSAGE STRING ( 'At location 7 the connection number is ', @@SPID ) TO CONSOLE" 
    USING SQLCA;

    DO WHILE li_row_num2 < li_row_num1 + 102

        FETCH cursor2 INTO :li_row_num2;
        ls_result_sets = ls_result_sets + ', ' + String ( li_row_num2 ) 
        EXECUTE IMMEDIATE 
        "MESSAGE STRING ( 'At location 8 the connection number is ', @@SPID ) TO CONSOLE" 
        USING SQLCA;

    LOOP 
    EXECUTE IMMEDIATE 
    "MESSAGE STRING ( 'At location 9 the connection number is ', @@SPID ) TO CONSOLE" 
    USING SQLCA;

    CLOSE cursor2;
    EXECUTE IMMEDIATE 
    "MESSAGE STRING ( 'At location 10 the connection number is ', @@SPID ) TO CONSOLE" 
    USING SQLCA;

LOOP 
EXECUTE IMMEDIATE 
    "MESSAGE STRING ( 'At location 11 the connection number is ', @@SPID ) TO CONSOLE" 
    USING SQLCA;

CLOSE cursor1;
EXECUTE IMMEDIATE 
    "MESSAGE STRING ( 'At location 12 the connection number is ', @@SPID ) TO CONSOLE" 
    USING SQLCA;

ls_sql = "MESSAGE '" + ls_result_sets + "' TO CONSOLE" 
EXECUTE IMMEDIATE 
    "MESSAGE STRING ( 'At location 13 the connection number is ', @@SPID ) TO CONSOLE" 
    USING SQLCA;

EXECUTE IMMEDIATE :ls_sql USING SQLCA;
EXECUTE IMMEDIATE 
    "MESSAGE STRING ( 'At location 14 the connection number is ', @@SPID ) TO CONSOLE" 
    USING SQLCA;

MessageBox ( 'ls_result_sets', ls_result_sets );
EXECUTE IMMEDIATE 
    "MESSAGE STRING ( 'At location 15 the connection number is ', @@SPID ) TO CONSOLE" 
    USING SQLCA;

At location 1 the connection number is 7
At location 2 the connection number is 7
At location 3 the connection number is 7
At location 4 the connection number is 7
At location 5 the connection number is 7
At location 6 the connection number is 7
At location 7 the connection number is 7
At location 8 the connection number is 7
At location 8 the connection number is 7
At location 9 the connection number is 7
At location 10 the connection number is 7
At location 4 the connection number is 7
At location 5 the connection number is 7
At location 6 the connection number is 7
At location 7 the connection number is 7
At location 8 the connection number is 7
At location 8 the connection number is 7
At location 9 the connection number is 7
At location 10 the connection number is 7
At location 4 the connection number is 7
At location 5 the connection number is 7
At location 6 the connection number is 7
At location 7 the connection number is 7
At location 8 the connection number is 7
At location 8 the connection number is 7
At location 9 the connection number is 7
At location 10 the connection number is 7
At location 4 the connection number is 7
At location 5 the connection number is 7
At location 6 the connection number is 7
At location 7 the connection number is 7
At location 8 the connection number is 7
At location 8 the connection number is 7
At location 9 the connection number is 7
At location 10 the connection number is 7
At location 4 the connection number is 7
At location 5 the connection number is 7
At location 6 the connection number is 7
At location 7 the connection number is 7
At location 8 the connection number is 7
At location 8 the connection number is 7
At location 9 the connection number is 7
At location 10 the connection number is 7
At location 11 the connection number is 7
At location 12 the connection number is 7
At location 13 the connection number is 7
1, 101, 102, 103, 2, 102, 103, 104, 3, 103, 104, 105, 4, 104, 105, 106, 5, 105, 106, 107
At location 14 the connection number is 7
At location 15 the connection number is 7
permanent link

answered 18 Jul '17, 18:08

Breck%20Carter's gravatar image

Breck Carter
32.5k5407241050
accept rate: 20%

edited 19 Jul '17, 07:43

Breck, I don't use PowerShell, so I can't do that - but could you add something like a MESSAGE statement with connection_property('Number') within both loops to prove that this does make use of only one connection for both cursor loops, as there still seems to be a doubt about that on the pathway?

(19 Jul '17, 05:18) Volker Barth
Replies hidden

Not PowerShell (which I don't use either), but PowerBuilder's PowerScript scripting language (which is now an Appeon product).

But yes, I will add that... to both examples... sigh. Plus make the use of the default SQLCA object explicit on all embedded SQL statements where it applies (DECLARE and EXECUTE).

(19 Jul '17, 06:41) Breck Carter
2

...done.

And now we wait for the next turd to appear on the pathway :)

(19 Jul '17, 07:49) Breck Carter

maybe i must clarify that i must use ODBC and not the lib! and this example are a variation of the previusly posted yet!

(19 Jul '17, 08:18) mckaygerhard
Replies hidden

*A very big thanks, very good guy you are:)

BTW: I meant PowerScript, not PowerShell - it's quite easy to do not get messed up by this F**:)

(19 Jul '17, 09:06) Volker Barth
1

Both examples use ODBC.

(19 Jul '17, 11:57) Breck Carter
showing 2 of 6 show all flat view
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:

×144
×23
×17

question asked: 17 Jul '17, 10:28

question was seen: 3,009 times

last updated: 21 Jul '17, 11:36