SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
"SQL Editor" procedure has multiple result sets re

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
"SQL Editor" procedure has multiple result sets re
Author Message
pfg1114



Joined: 30 Jun 2021
Posts: 29
Country: China

Post "SQL Editor" procedure has multiple result sets re Reply with quote
"SQL Editor" will report an error when the stored procedure has multiple result sets returned.




Wed Jun 30, 2021 10:42 pm View user's profile Send private message Send e-mail
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
This is a known compatibility issue with some versions of MySQL native clients, I believe it regressed after MySQL 8.x
As a workaround, please change your connection in the Connection dialog to use ADO.NET driver or ODBC driver.
Wed Jun 30, 2021 11:19 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
Just to add another two cents (or whatever it is in China ;): Depending on the version of MySQL (or MariaDB) and the connector you use, you can expect to run into all kinds of issues that come in different flavors (and levels of funny).

- native: command out of sync stored procedures with multiple result sets. Ahh, change the connection type to...

- ODBC: no command out of sync for stored procedures. Yeah! No multiple result sets returned either. What? You get the first one, the rest is silently swallowed somewhere, and that's all for you. You should be happy with that one, lonely result set. Keep it fed. Again, change the connection type to...

- ADO: you get multiple result sets. What you don't get is the ability to use user-defined variables (the ones with the @). Well, not unless you add "Allow User Variables=True;" to the raw connection string, but I guess SA has you covered there as it is automatically added to new connections (older versions did not do that). Otherwise, you only get an error message, stating: "Fatal error encountered during command execution." Another thing you also won't get is any kind of error messages or warnings if something fails after the first result set. If the fishy part is before the first result set, everything is fine, it fails and you get the error message. If it happens after, you get all of the results sets until the crash-and-burn, a message that all went well (a hideous lie, if you ask me), and an ever-increasing headache when trying to figure out where have all the other/missing results gone. In this case, you should execute SHOW WARNINGS immediately after the semi-failed call. It might tell something useful.


Last edited by gemisigo on Thu Jul 15, 2021 3:00 am; edited 1 time in total
Wed Jul 14, 2021 6:27 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Thank you for the very useful details. Just to complete the picture, the issue with multiple result sets from a stored procedure not showing when using native client library is going to be resolved in version 12.0. That fix is already in the queue and confirmed. But there is no confirmed release date yet for 12.0. So far we expect beta version available at the end of August / beginning of September.
Wed Jul 14, 2021 5:53 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
I knew this day had something beautiful coming! Can you spread some rumors, what is to be expected with v12? (also, I'm sorry for temporarily hijacking this thread with this question).
Thu Jul 15, 2021 2:55 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
There is no confirmed list yet for what is going to make to 12.0 or what would need to wait for later. What I've seen so far (big tickets, listed in no particular order)

Task-oriented SQL profilers for Oracle and PostgreSQL
Full featured report engine and designer
Reworking of source control interfaces supporting different projects for different databases and more flexibility
Significant intellisense updates, better support for all sorts of DDL operations, engine updated for all latest db versions
Preset load testing templates for testing performance, generating loads, and benchmarking various databases
Parquet files supported in data import/export operations
Richer set of ETL tools
Additional graphical database management tools
Better support for high resolution monitors and font scaling
Thu Jul 15, 2021 5:05 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.