 calling stored procedures
#31149
08 Sep 17 10:26 AM
|
Joined: Jun 2001
Posts: 410
Valli Information Systems
OP
Member
|
|
OP
Member
Joined: Jun 2001
Posts: 410 |
should you be able to use SQLOP_QUERY to CALL store procedures?
CALL create_w2_tables('2091');
cmdhdr.lastop = 9
cmdhdr.handle = 1
cmdhdr.rc = 2 [DBMS error - see cmdhdr.rcext]
cmdhdr.rcext = 1
cmdhdr.sqlstate = 0A000 (Unknown state)
<-----
Retrieving DBMS error message for # 1
DMBS error msg:
---->
cmdhdr.info1: 0 (n/a)
info2: 0 (n/a)
Hit any key to proceed:
|
|
|
|
Re: calling stored procedures
#31150
08 Sep 17 11:11 AM
|
Joined: Jun 2001
Posts: 11,645
Jack McGregor
Member
|
|
Member
Joined: Jun 2001
Posts: 11,645 |
As far as I can tell, there is no particular restriction set by ASQL on what an SQLOP_QUERY statement can consist of. It just passes the statement to the database and then reports the response.
Unfortunately, the error being returned isn't particularly helpful here, other than to suggest the possibility that maybe the database engine was expecting some prior action to have taken place (i.e. some prior 'state').
Typically when faced with this kind of situation, I would suggest trying to use another console utility (i.e. the MySQL text or GUI management consoles) to try the same set of query statements to see if you get a different response. (In my experience, the same sequence of queries will generate the same responses regardless of whether sent from ASQL or another client utility.)
|
|
|
|
Re: calling stored procedures
#31151
08 Sep 17 05:28 PM
|
Joined: Jun 2001
Posts: 410
Valli Information Systems
OP
Member
|
|
OP
Member
Joined: Jun 2001
Posts: 410 |
using heidisql or just mysql from the client seems to work just fine
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 290417
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CALL create_w2_tables('2091');
Query OK, 0 rows affected (0.55 sec)
here is a session log from ashlog.log
08-Sep-17 09:19:53 [p23090-1] SQL op=1
op=1, hdl=0, opflags=1, args=0,0, myhdl=0, res=0, pst=0
mysql_library_init >>> rc=0, rcext=0, info1=0, info2=0, state=, nest=1
08-Sep-17 09:19:53 [p23090-1] SQL op=4
op=4, hdl=0, opflags=1, args=0,0, myhdl=0, res=0, pst=0
mysql_init (969f588) >>> rc=0, rcext=0, info1=0, info2=0, state=, nest=1
08-Sep-17 09:19:53 [p23090-1] SQL op=6
op=6, hdl=1, opflags=1, args=0,0, myhdl=969f4b0, res=0, pst=0 -host=192.168.
0.82 -user=dev2admin -pw=Welcome!17...
ASQLOP_CONNECT...
mysql_real_connect (969f588) >>> rc=0, rcext=0, info1=0, info2=0, state=, nes
t=1
08-Sep-17 09:19:54 [p23090-1] SQL op=9
op=9, hdl=1, opflags=0, args=0,0, myhdl=969f4b0, res=0, pst=0 CALL create_w2
_tables('2091');
qry flags=0, mysql=969f588, reshdl=0, flddef[0]=0, cnt=0
rcext=1 >>> rc=2, rcext=1, info1=0, info2=0, state=, nest=1
08-Sep-17 09:19:54 [p23090-1] SQL op=3
op=3, hdl=1, opflags=0, args=0,0, myhdl=969f4b0, res=0, pst=0 >>> rc=2, rce
xt=1, info1=0, info2=0, state=, nest=1
i must be missing something obvious?
thanks
|
|
|
|
Re: calling stored procedures
#31152
11 Sep 17 01:10 PM
|
Joined: Jun 2001
Posts: 11,645
Jack McGregor
Member
|
|
Member
Joined: Jun 2001
Posts: 11,645 |
I don't see anything obviously missing, The return value from the SQLOP_QUERY (rcext=1) indicates an error, but the the SQLOP_GET_ERRMSG is apparently failing to translate it into anything. (Note that 1 is not the error code, just the indicator that an error occurred.)
Although both ASQL and the standard client routine are presumably calling the same underlying function - mysql_query() - but there could be some differences in flags either passed with the call, or in the initial connection flags. Offand I'm not sure how to enable tracing in the standard mysql client, but if you can do that, it might reveal the steps/flags that it is using prior to the the actual 'query'.
I don't think you mentioned which connector version you are using - that might be relevant information since there are several versioned pieces all trying to work together here. Aside from the ASQL connector version, using ldd on it would also reveal the version of the mysql client library.
I'll try to see if I can reproduce the issue here later today.
|
|
|
|
Re: calling stored procedures
#31153
11 Sep 17 01:39 PM
|
Joined: Jun 2001
Posts: 11,645
Jack McGregor
Member
|
|
Member
Joined: Jun 2001
Posts: 11,645 |
Are you sure that it didn't actually create the tables, i.e. that the stored procedure didn't actually work?
I just tried this using a stored procedure film_in_stock() that comes with the Sakila database, and although it does return rcext=1 (which implies an error), it also returns the same result set as I get when I execute it from the MySQL Workbench.
(This test was with ashmysql.dll 1.4.141 for Windows, MySQL server 5.7, and mysqlclient 6.1.9)
|
|
|
|
Re: calling stored procedures
#31154
11 Sep 17 02:45 PM
|
Joined: Jun 2001
Posts: 410
Valli Information Systems
OP
Member
|
|
OP
Member
Joined: Jun 2001
Posts: 410 |
sorry no it did not create anything. the mysql state of 0a000 seems to reference that procedure calls are not supported. i notice on the old post for stored procedures that windows version worked whereas linux does not which is what i am using. below is the ldd output from ashmysql
[root@valli-prod1 /usr/lib]# ldd libashmysql.so.1
linux-gate.so.1 => (0x00644000)
libc.so.6 => /lib/libc.so.6 (0x00d40000)
libmysqlclient.so.16 => /usr/lib/mysql/libmysqlclient.so.16 (0x007f5000)
/lib/ld-linux.so.2 (0x0068a000)
libcrypt.so.1 => /lib/libcrypt.so.1 (0x0011a000)
libnsl.so.1 => /lib/libnsl.so.1 (0x00bbe000)
libm.so.6 => /lib/libm.so.6 (0x0054e000)
libssl.so.10 => /usr/lib/libssl.so.10 (0x003dc000)
libcrypto.so.10 => /usr/lib/libcrypto.so.10 (0x0014a000)
libz.so.1 => /lib/libz.so.1 (0x00315000)
libfreebl3.so => /lib/libfreebl3.so (0x00ba9000)
libgssapi_krb5.so.2 => /lib/libgssapi_krb5.so.2 (0x00329000)
libkrb5.so.3 => /lib/libkrb5.so.3 (0x00445000)
libcom_err.so.2 => /lib/libcom_err.so.2 (0x003b3000)
libk5crypto.so.3 => /lib/libk5crypto.so.3 (0x00ca1000)
libresolv.so.2 => /lib/libresolv.so.2 (0x00369000)
libdl.so.2 => /lib/libdl.so.2 (0x00383000)
libkrb5support.so.0 => /lib/libkrb5support.so.0 (0x00a14000)
libkeyutils.so.1 => /lib/libkeyutils.so.1 (0x00388000)
libpthread.so.0 => /lib/libpthread.so.0 (0x0038c000)
libselinux.so.1 => /lib/libselinux.so.1 (0x003b8000)
[root@valli-prod1 /usr/lib]#
|
|
|
|
Re: calling stored procedures
#31155
11 Sep 17 02:50 PM
|
Joined: Jun 2001
Posts: 11,645
Jack McGregor
Member
|
|
Member
Joined: Jun 2001
Posts: 11,645 |
Ok, let me try to reproduce this under Linux. In addition to the above, can you give me the versions output by SQLTEST1 (client library version and the connector version).
|
|
|
|
Re: calling stored procedures
#31156
11 Sep 17 03:06 PM
|
Joined: Jun 2001
Posts: 11,645
Jack McGregor
Member
|
|
Member
Joined: Jun 2001
Posts: 11,645 |
Actually, I just tried the same stored procedure call from my CentOS 5 system and it works even better than it does under Windows! (That is, it doesn't return an error code; the result set is the same.) Here's the output from the query in SQLTEST2 ... Query (or 'END'): call film_in_stock('1','2',@count)
Query: call film_in_stock('1','2',@count)
cmdhdr.lastop = 9
cmdhdr.handle = 1
cmdhdr.rc = 0 [OK]
cmdhdr.rcext = 0
cmdhdr.info1: 1 (n/a)
info2: 3 (# rows returned/affected; 0=unknown)For this particular query, the result set should return 3 rows, each with just the one field in it. Here's what SQLTEST1 outputs: Select database connector (1=MySQL,2=ODBC) [1]
Using connector MySQL (native)
Loading connector and client library... [OK]
Initializing connection handle... [OK]
Client database library version: 5.7.17
A-Shell/SQL connector and DBMS client library successfully loaded
Connector ID (lib name) LIBASHMYSQL
Connector Version 1401410 (1.4.141.0)
Connector Description: MySQL 5.7 (native) Possibly relevant differences: a) Your MySQL server is 5.5; mine is 5.7 (or 50718 as shown in the SQLOP_GET_INFO / SQLINFO_SERVER call in SQLTEST2). My server is also actually running on Windows, although I'm connecting to it from Linux. Normally the server version is not very critical, but since stored procedures are stored on the server, I suppose that's a possibility. b) Our stored procedures are quite different. If you can install the Sakila sample database you can try the film_in_stock() call to see if it works any better, or if you want to strip yours down to something that I can try here, we might be able to eliminate that uncertainty. c) Our client library versions are different (mine is 5.7.17 (libmysqlclient.so.20; yours is .so.16) ... [jack@localhost bin]$ ldd /usr/lib/libashmysql.so.1
linux-gate.so.1 => (0x003be000)
libc.so.6 => /lib/libc.so.6 (0x006be000)
libmysqlclient.so.20 => /usr/lib/mysql/libmysqlclient.so.20 (0x0081a000)
/lib/ld-linux.so.2 (0x004e9000)
libpthread.so.0 => /lib/libpthread.so.0 (0x00153000)
libdl.so.2 => /lib/libdl.so.2 (0x001d7000)
librt.so.1 => /lib/librt.so.1 (0x00f0c000)
libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0x001dc000)
libm.so.6 => /lib/libm.so.6 (0x0016d000)
libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x00196000)d) Our connectors are almost certainly different versions as well. I'm not completely sure though if the new 1.4.141 connector will work with your version of the client library, but I am pretty confident that you can update your client library without needing to update the actual server version (either the OS or the MySQL server).
|
|
|
|
Re: calling stored procedures
#31157
11 Sep 17 03:41 PM
|
Joined: Jun 2001
Posts: 11,645
Jack McGregor
Member
|
|
Member
Joined: Jun 2001
Posts: 11,645 |
When I try the same test from CentOS 6.9 using the 5.1.73 client library (libmysqlclient.so.16) and 1.4.141 connector, and connecting to the same Windows/MySQL 5.7 server, I get a result similar to yours: Query (or 'END'): call film_in_stock('1','2',@count)
Query: call film_in_stock('1','2',@count)
cmdhdr.lastop = 9
cmdhdr.handle = 1
cmdhdr.rc = 2 [DBMS error - see cmdhdr.rcext]
cmdhdr.rcext = 1
cmdhdr.sqlstate = 0A000 (Unknown state)
<-----
Retrieving DBMS error message for # 1
DMBS error msg: PROCEDURE sakila.film_in_stock can't return a result set in the given context
---->
cmdhdr.info1: 0 (n/a)
info2: 0 (n/a)I then tried it from a CentOS 7 system, this time with the 5.7.17 client library, and it works correctly, just as it did under CentOS 5. Query (or 'END'): call film_in_stock('1','2',@count)
Query: call film_in_stock('1','2',@count)
cmdhdr.lastop = 9
cmdhdr.handle = 1
cmdhdr.rc = 0 [OK]
cmdhdr.rcext = 0
cmdhdr.info1: 1 (n/a)
info2: 3 (# rows returned/affected; 0=unknown)That implies that the issue is tied mainly to the client library version and not the connector or the server version. So I guess my advice would be to update the mysql-libs i686 package (and probably also update the connector.)
|
|
|
|
Re: calling stored procedures
#31158
11 Sep 17 04:22 PM
|
Joined: Jun 2001
Posts: 410
Valli Information Systems
OP
Member
|
|
OP
Member
Joined: Jun 2001
Posts: 410 |
i had such a good time getting the current one working... thanks
|
|
|
|
Re: calling stored procedures
#31159
13 Sep 17 12:53 PM
|
Joined: Jun 2001
Posts: 410
Valli Information Systems
OP
Member
|
|
OP
Member
Joined: Jun 2001
Posts: 410 |
back in business, thanks for all your help
|
|
|
|
Re: calling stored procedures
#31160
13 Sep 17 01:08 PM
|
Joined: Jun 2001
Posts: 11,645
Jack McGregor
Member
|
|
Member
Joined: Jun 2001
Posts: 11,645 |
That's a relief - thanks for the confirmation! (We have enough obscure technical issues to keep us busy without this one.)
|
|
|
|
|
