Bug #38557 Using SPC from VB6 fails
Submitted: 5 Aug 2008 7:56 Modified: 30 Aug 2008 19:52
Reporter: Andy Binnie Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:MySQL 5.0.51b-community-nt-log OS:Windows (XP)
Assigned to: CPU Architecture:Any

[5 Aug 2008 7:56] Andy Binnie
Description:
Sample VB6 code...

Public Function GetNamesAll()
    ' Returns  Names table as an array
    Dim rs
    Set dbConn = New ADODB.Connection
    dbConn.Open "Driver={MySQL ODBC 5.1 Driver}; " _
        & "SERVER=localhost;" _
        & " DATABASE=lets_database;" _
        & "UID=LETS;PWD=well1es; OPTION=3"
    Set dbComm = New ADODB.Command
    Set dbComm.ActiveConnection = dbConn
    dbComm.CommandText = "sp_GetNames"
    dbComm.CommandType = adCmdStoredProc
    Set rs = dbComm.Execute
    GetNamesAll = rs.GetRows
End Function

example simple stored procedure is...

CREATE DEFINER=`LETS`@`localhost` PROCEDURE `sp_GetNames`()
BEGIN
    SELECT FirstName,
            LastName
            FROM Names;
END

When this is run, the error message box appears with...

The following SQL Server error occurred: [MySQL][ODBC 5.1 Driver][MySQL 5.0.51b-community-nt-log]Unknown system variable 'SPID'

and of course no table/array is returned.

How to repeat:
Fully repeatable on my system. The SPC works fine and correctly returns the table/array if VB6 code (part) replaced by...

dbComm.CommandText = "CALL sp_GetNames"
dbComm.CommandType = adCmdText

but I require to use parameters (with other SPC's) and adCmdStoredProc.

The code shown is an example - all my SPC's and VB6 code give the same error when trying adCmdStoredProc.

Suggested fix:
None...
[5 Aug 2008 15:21] Valeriy Kravchuk
Thank you for a problem report. What exact version of Connector/ODBC (5.1.x) do you use?
[5 Aug 2008 16:02] Andy Binnie
Hi Valeriy,

I have loaded mysql-connector-odbc-5.1.4-win32.msi

Thanks for you prompt attention..

Regards, Andy
[6 Aug 2008 7:42] Bogdan Degtyariov
I was not able to repeat the problem using given test case and SP source.
Can you check what is logged into the server query log when trying to execute SP from VB6?

At the first glance it looks as the wrong server config issue. It is possible that your my.ini/my.cnf configuration file contain wrong parameter. Please check your server configuration files (all that might be loaded, such as in C:\my.ini C:\my.cnf C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\<mysql_dir>\my.ini C:\<mysql_dir>\my.cnf ).

Thanks.
[6 Aug 2008 8:19] Andy Binnie
Query log for this error..

080806  9:04:43	     17 Connect     LETS@localhost on lets_database
		     17 Query       SET NAMES utf8
		     17 Query       SET character_set_results = NULL
		     17 Query       SET SQL_AUTO_IS_NULL = 0
		     17 Query       select database()
		     17 Query       select database()
		     18 Connect     LETS@localhost on lets_database
		     18 Query       SET NAMES utf8
		     18 Query       SET character_set_results = NULL
		     18 Query       SET SQL_AUTO_IS_NULL = 0
		     18 Query       select database()
		     18 Query       select database()
		     17 Query       SELECT @@tx_isolation
		     17 Query       select @@SPID
080806  9:04:46	     17 Query       call sp_GetNames
		     18 Quit       
		     17 Quit       

I have uploaded the my.ini configuration file loaded...

Thanks, Andy
[6 Aug 2008 9:02] Meiji KIMURA
I tried to reproduce your problem. I did these procedures as below.

(1) Create database lets_database.
(2) Create table names like this;
 mysql> create table names(FirstName varchar(10), LastName varchar(10));
 mysql> insert into names values('Meiji', 'Kimura');
 mysql> insert into names values('Ryusuke', 'Kajiyama');

(3) Create procedure like this;
 mysql> delimiter //
 mysql> CREATE PROCEDURE `sp_GetNames`()
     -> BEGIN
     ->     SELECT FirstName,
     ->             LastName
     ->             FROM Names;
     -> END;
     -> // 

 mysql> call sp_GetNames();
 +-----------+----------+
 | FirstName | LastName |
 +-----------+----------+
 | Meiji     | Kimura   |
 | Ryusuke   | Kajiyama |
 +-----------+----------+
 2 rows in set (0.03 sec)

(4) Create Project1 with Form1 using Visual Basic 6.0 SP6.
(5) Modify 'Private Sub Command1_Click()' like this;

Private Sub Command1_Click()
    ' Returns  Names table as an array
    Dim rs
    Set dbConn = New ADODB.Connection
    dbConn.Open "Driver={MySQL ODBC 5.1 Driver}; " _
        & "SERVER=localhost;" _
        & " DATABASE=lets_database;" _
        & "UID=root; OPTION=3"
    Set dbComm = New ADODB.Command
    Set dbComm.ActiveConnection = dbConn
    dbComm.CommandText = "sp_GetNames"
    dbComm.CommandType = adCmdStoredProc
    Set rs = dbComm.Execute
    GetNamesAll = rs.GetRows
End Sub

(6) Add reference to 'Microsoft ActiveX Data Objects 2.8 Library'.
(There are many version of it, but I choose latest one).

(7) It runs well with no error.

Your error is here.
The following SQL Server error occurred: [MySQL][ODBC 5.1 Driver][MySQL
5.0.51b-community-nt-log]Unknown system variable 'SPID'

And your log said like this;
17 Query       select @@SPID

I supposed that @@SPID is specific system variable for Microsoft SQL server.
Please confirm that your code contains @@SPID, or not for arguments of Stored Procedure.

@@SPID is valid only for Microsoft SQL Server (or Sybase).
See details about @@SPID, please read this link;
http://msdn.microsoft.com/en-us/library/ms189915.aspx
[6 Aug 2008 9:13] Andy Binnie
Hi again...

no I have not used @@SPID anywhere in my code... or any other system variables.

I can only assume that either VB6 or the connector is adding it in somehow... 
(guessing that SPID is 'stored procedure identity???)

Andy
[6 Aug 2008 9:35] Bogdan Degtyariov
Andy,

@@SPID is not a store procedure identity. I do not have such entry in my query.log file. In Transact SQL it is the session ID for the user ID process.
Can you check if your code gets anything of that sort?
[6 Aug 2008 9:45] Andy Binnie
Hi Bogdan,

No, I dont have anything like it anywhere in my code at all. 

BTW, I have referenced 'Microsoft ActiveX Data Objects 2.8 Library' in VB6, the same as Meiji.

Andy
[6 Aug 2008 10:03] Andy Binnie
Thanks all...

The mention of T-SQL got me looking and I had the T-SQL debugger enabled in the VB6 options (from a previous project using MSSQL server).

Now all working just fine...

Best Regards,

Andy