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: | |
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
[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