| Bug #29702 | MySQl Incorrectly translating ADO numeric parameters | ||
|---|---|---|---|
| Submitted: | 10 Jul 2007 22:32 | Modified: | 11 Jul 2007 0:50 |
| Reporter: | David Boccabella | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.0.41 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | ADO, Parameter, stored procedures, translation | ||
[11 Jul 2007 0:15]
David Boccabella
Changed Version from ODBC version to MySQL version
[11 Jul 2007 0:17]
Jess Balint
David, The correct code is:
cmd.CreateParameter("PARAM1", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, 0, 300)
Notice that the fourth parameter is the size, but the fifth is the actual value.
[11 Jul 2007 0:50]
David Boccabella
ACK!! ACK!! ACK!!.. My Bad... User/Keyboard Interface error Many thanks Jess. Your a lifesaver. the Intellisense is sometimes difficult to read when one has a lot of parameters. I take it the Size parameter is when one is using strings etc. Again Thanks Dave
Description: I have a simple stored procedure with an input INT parameter. If I call this from Visual Basic 6, or Ms-Access I can get 2 different values passed in depending on how I pass the parameter. I am using ADO V 2.8, MySQl 5.0.41, MyODBC 3.51.16 How to repeat: Create a simple stored procedure like this DELIMITER | CREATE PROCEDURE `test1` ( IN PARAM1 int(6) ) NO SQL BEGIN select PARAM1; END| DELIMITER ; Now call it from Visual Basic 6 MyCmd.CommandText = "call test1(?);" MyCmd.Parameters.Append MyCmd.CreateParameter("PARAM1", adInteger, adParamInput, 300) Set MyRS = MyCmd.Execute debug.print MyRS(0).Value When this runs I get a returned value of 131107 If I call the SP like this MyCmd.CommandText = "call test1(300);" Set MyRS = MyCmd.Execute debug.print MyRS(0).Value I see 300. I can confirm that the value is being translated whilst send INTO the stored procedure because if I modify the SP to save the value into a table I can see the value is 131107 I have experimented with adInteger, adBigInt, adNumeric (Caused crash), as well as trying various combinations of the ODBC flag re Big to Int to no avail. I also have not tested to see if Date, Time, String ot other non numeric values are translated correctly. Please help as I have a signicant project that will use MySQl SP's extensively to pass the processing to the DB for a lot of the business rules. As the application will need to run Client/Server over the internet the less traffic that is passed the better.. this SP and intelligence in the DB are critical.