| Bug #6157 | BUG in the alias use with ADO's Object | ||
|---|---|---|---|
| Submitted: | 19 Oct 2004 13:05 | Modified: | 31 Dec 2007 13:48 |
| Reporter: | Roberto Mastrolinardo | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 3.51.20 | OS: | Windows (Windows 95 / 98 / 2000 / XP) |
| Assigned to: | Jim Winstead | CPU Architecture: | Any |
[19 Oct 2004 13:21]
Roberto Mastrolinardo
I have just installed new version 3.51.9 but the problem isn't resolved.
[19 Oct 2004 15:19]
MySQL Verification Team
Verified with Access 2003.
[6 Oct 2005 21:43]
Mark Matthews
Please reverify w/ 3.51.12 as there are fixes in there that most likely address this bug. (now linked w/ libmysql 4.1.x, which should return column information correctly).
[6 Oct 2005 21:43]
Mark Matthews
Please reverify w/ 3.51.12 as there are fixes in there that most likely address this bug. (now linked w/ libmysql 4.1.x, which should return column information correctly).
[9 Oct 2005 11:08]
Vasily Kishkin
I tested on myodbc 3.51.12 and mysqld 4.1.16. I've got the follow error: Additional information: Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
[23 Feb 2006 14:12]
Marcio Monego
I had the same problem with MySQL 5.0.18, MyODBC 3.51.12 and ADO 2.8. The Select statement that generates the recordset DIDN'T use a field alias included in a where clause BUT after executed a find command in ADO and it has found the record a future change in the field that has been aliased causes an identical error crying for a field not known in where clause... I think it is internal to ADO ou in MyODBC when the UPDATE metod passes something that distinguish the field to the provider.
[13 Jun 2007 6:19]
Erica Moss
Created a simpler test case for this:
SETUP
create database ado;
use ado;
create table foo (c1 INT, c2 char(20));
insert into foo values (1, 'eric');
VB CODE:
Private Sub AliasTest()
On Error GoTo EH
Dim strSQL As String
Dim strConn As String
Dim connection1 As ADODB.Connection
Dim recset1 As ADODB.Recordset
Dim fld As Field
Set connection1 = New ADODB.Connection
Set recset1 = New ADODB.Recordset
strSQL = "SELECT c1, c2 as bar FROM foo"
strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=localhost;DATABASE=ado" & _
";USER=root;PASSWORD=mypass;OPTION=3;"
'strConn = "DSN=SQL Server"
' open connection
connection1.ConnectionString = strConn
connection1.CursorLocation = adUseClient
connection1.Open
recset1.Open strSQL, connection1, adOpenStatic, adLockOptimistic, adCmdText
recset1.MoveFirst
For Each fld In recset1.Fields
If fld.Type = adChar Then
fld.Value = "test"
End If
Next
recset1.Update
recset1.Close
Set recset1 = Nothing
connection1.Close
Set connection1 = Nothing
Exit Sub
EH:
Debug.Print Err.Number & ":" & Err.Description
End Sub
Results in error:
-2147217900:[MySQL][ODBC 3.51 Driver][mysqld-5.0.37-community-nt-log]Unknown column 'bar' in 'where clause'
Tested against 3.5.15
Also ran this same test case against SQL Server and it ran fine. ODBC traces for both runs are attached:
SQL-mysql.LOG
SQL-sqlserver.LOG
[13 Jun 2007 6:19]
Erica Moss
ODBC trace against mysql
Attachment: SQL-mysql.LOG (application/octet-stream, text), 75.19 KiB.
[13 Jun 2007 6:20]
Erica Moss
ODBC trace against sql server
Attachment: SQL-sqlserver.LOG (application/octet-stream, text), 81.94 KiB.
[22 Aug 2007 23:11]
Kent Boortz
Looking at the logs and source "driver/results.c", ADO creates
an Update SQL statement. To get the column names, SQLColAttribute()
is run, and it will with the MySQL ODBC driver list an alias that
seems not to be valid any longer, with SQLServer the original
table name is used
"UPDATE `ado`.`foo` SET `bar`=? WHERE `c1`=? AND `bar`=?\ 0"
"UPDATE "ado".."foo" SET "c2"=? WHERE "c1"=? AND "c2"=?\ 0"
The alias in the MySQL driver comes from that we use the
struct MYSQL_FIELD field "name" instead of "org_name".
Now, why "name" lists an alias used in the previous select
and think it is an permanent alias I don't know, should it?
And in case it can be valid or not, I think the naming
of the MYSQL_FIELD fields could be more clear "possibly_alias"
and "real_name" maybe.
The small patch below likely fixes the problem in this case
(with some ifdef 5.0 and up maybe), but not sure it is the
right one, and if there are other places that should use
the "org_*" version, for tables and column names.
--- driver/results.c (revision 674)
+++ driver/results.c (working copy)
@@ -632,7 +632,7 @@
case SQL_DESC_NAME:
case SQL_COLUMN_NAME:
return copy_str_data(SQL_HANDLE_STMT, stmt, CharacterAttributePtr,
- BufferLength, StringLengthPtr, field->name);
+ BufferLength, StringLengthPtr, field->org_name);
case SQL_DESC_BASE_TABLE_NAME:
#if MYSQL_VERSION_ID >= 40100
[10 Sep 2007 10:28]
Tonci Grgin
This bug is still present in latest GA. 070910 12:26:03 3 Connect root@localhost on test 3 Query SET SQL_AUTO_IS_NULL = 0 3 Query select database() 3 Query SELECT @@tx_isolation 3 Query drop table if exists bug6157 3 Query create table bug6157(x SERIAL, VCField VARCHAR(30) DEFAULT NULL, DtField DATE) 3 Query INSERT INTO bug6157 VALUES (NULL, 'TEST', '2007-09-10') 3 Query SELECT x, VCField AS Fld2, DtField AS Fld3 FROM bug6157 3 Query UPDATE `test`.`bug6157` SET `Fld2`='testing' WHERE `x`=1 AND `Fld2`='TEST' 070910 12:26:08 3 Quit
[11 Sep 2007 17:28]
Jim Winstead
Looking at the two traces, ADO (or maybe the OLE-DB driver for ODBC) is querying for SQL_COLUMN_NAME from our driver, but SQL_BASE_COLUMN_NAME from SQL Server's driver. We also support SQL_BASE_COLUMN_NAME (which is the correct attribute to be using), and it's not clear why ADO is not trying to use that. As with Bug #2966, we're going to need to connect with someone at Microsoft to find out what ADO is trying to do here.
[17 Dec 2007 22:19]
Jim Winstead
Use original field name instead of alias in SQLSetPos w/ SQL_ADD
Attachment: bug6157.patch (text/plain), 2.27 KiB.
[17 Dec 2007 22:21]
Jim Winstead
Using 3.51, ADO generates this query itself, and behaves as described in my previous comment. With Connector/ODBC 5.1, it uses SQLSetPos(), which exposes a bug in the driver itself. With this patch, ADO works with the 5.1 driver. There does not appear to be a way to fix this for the 3.51 driver.
[17 Dec 2007 22:26]
Jim Winstead
Updated patch, which fixes SQLSetPos() w/ SQL_UPDATE, too
Attachment: bug6157.patch (text/plain), 2.73 KiB.
[18 Dec 2007 19:31]
Jim Winstead
Fixed in 5.1.2.
[31 Dec 2007 13:48]
MC Brown
A note has been added to the 5.1.2 changelog: Adding or updating a row using SQLSetPos() on a result set with aliased columns would fail.
[9 Feb 2008 18:22]
Diego Milazzo
I have the same problem. I test it with 3.51 and 5.1.1 Which is the release date for the ODBC 5.1.2 ?
[10 Feb 2008 8:35]
Lawrenty Novitsky
Diego, if nothing goes wrong, it will be released early this week.
Description: I use an alias in a select for the fields name. When I use the method Update, the program generate the error: "[MySql][ODBC 3.51 Driver][MySql-5.0.0-alpha-nt] Unknown column 'col1' in 'where clause'". Example Visual Basic 6: ' In a form with a command button named Command1 Option Explicit Dim filedb As New ADODB.Connection Dim tflag As New ADODB.Recordset Private Sub Command1_Click() If tflag.state And adstateopen Then tflag.Close tflag.cursorlocation = aduseclient tflag.Open "select campo1 as col1, campo2 as col2 from tab1", filedb_ adOpenKeyset, adLockOptimistic tflag!col1 = "AAAAA" tflag!col2 = 21 tflag.Update -> Error tflag.Close End Sub Private Sub Form_Load() filedb.Open "DSN=pippo" End Sub Private Sub Form_Unload(Cancel As Integer) filedb.Close End Sub DATABASE STRUCT: tab1 -> Table campo1 char(50) -> field select with alias col1 campo2 int(11) -> field select with alias col2 Sorry for my English. How to repeat: ' In a form with a command button named Command1 Option Explicit Dim filedb As New ADODB.Connection Dim tflag As New ADODB.Recordset Private Sub Command1_Click() If tflag.state And adstateopen Then tflag.Close tflag.cursorlocation = aduseclient tflag.Open "select campo1 as col1, campo2 as col2 from tab1", filedb_ adOpenKeyset, adLockOptimistic tflag!col1 = "AAAAA" tflag!col2 = 21 tflag.Update -> Error tflag.Close End Sub Private Sub Form_Load() filedb.Open "DSN=pippo" End Sub Private Sub Form_Unload(Cancel As Integer) filedb.Close End Sub DATABASE STRUCT: tab1 -> Table campo1 char(50) -> field select with alias col1 campo2 int(11) -> field select with alias col2