|
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! |
|
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.. 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.
(18 Jul '17, 07:08)
Vlad
Replies hidden
2
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
|
|
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 1
Take my vote for the example. Now I know how to use server-side cursors with the rowgenerator.
(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
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
|
|
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:
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. 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:
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. 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
(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
|
|
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
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
|
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?
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
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
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
> 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
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.
Please stop making personal insults.