Bug #20025 | Cannot update reserved-word-named fields through recordset | ||
---|---|---|---|
Submitted: | 23 May 2006 19:33 | Modified: | 17 May 2007 20:24 |
Reporter: | Aaron Humphrey | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | Connector / ODBC | Severity: | S2 (Serious) |
Version: | 3.51.12 | OS: | Windows (Windows NT) |
Assigned to: | CPU Architecture: | Any |
[23 May 2006 19:33]
Aaron Humphrey
[29 May 2006 9:19]
Tonci Grgin
Hi Aaron, thanks for your problem report. VS2005 can not compile your code at all (doesn't allow Default word after !: "C:\TEMP\odbc18883\18883.vb(16) : error BC30516: Overload resolution failed because no accessible 'Fields' accepts this number of arguments." thus I'm unable to verify your test case... I tried with AddNew("Default","Test") but got exception: System.Runtime.InteropServices.COMException was unhandled ErrorCode=-2146825023 HelpLink="C:\WINDOWS\HELP\ADO270.CHM#1240649" Message="Item cannot be found in the collection corresponding to the requested name or ordinal." Source="ADODB.Recordset" StackTrace: at ADODB.RecordsetClass.AddNew(Object FieldList, Object Values) at _18883.mainModule.Main() in C:\TEMP\odbc18883\18883.vb:line 15 at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() which has nothing to do with MyODBC and error you reported... If you can provide any more input on this, please do. Also, see disscusion in http://bugs.mysql.com/bug.php?id=19875 and resolution given there.
[29 May 2006 16:05]
Aaron Humphrey
You can replace rsTest!Default = "Test" with rsTest.fields("Default").value = "Test" As I said, this is a bug with VB6; I can't vouch for it with anything later than that. We're not planning to move into .NET for a year or two yet. Chris Lockwood's workaround may not work well for us in general because field names are not hard-coded in most tables, so "SELECT *" is often more useful than hard-coded field lists that may go out of date. I know there are workarounds, but aliasing(as well as just quoting) every field names in SQL is a pain, and converting all recordset adds to INSERT statements ditto. I'd make a snarky comment about how the other three databases we support don't have this problem, but I'll restrain myself because none of them are open-source.
[29 May 2006 18:55]
Tonci Grgin
Hi Aaron. Actually error raised comes from "Microsoft OLE DB Provider for ODBC Drivers" according to my debugger. As for "other databases" you can recheck http://bugs.mysql.com/bug.php?id=20064 and play with google, among others you'll find IBM, Btrieve... having similar problems... Just try typing "SELECT Config, nValue FROM MSysConf" in google and check. Anyway, this is serious problem for you and I'll put some more work into this.
[30 May 2006 6:42]
Tonci Grgin
We have a conflict in behavior here. http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html: "A reserved word can be used as an identifier if you quote it." but if you try, for example, .AddNew("`Default`", "Chris") no statement reaches MyODBC since ADO stops execution... Verified as described by reporter. To be checked together with http://bugs.mysql.com/bug.php?id=19875.
[30 May 2006 6:44]
Tonci Grgin
Test case: Module mainModule Sub Main() Dim cnTest As ADODB.Connection Dim rsTest As ADODB.Recordset cnTest = New ADODB.Connection cnTest.Open("DRIVER={MySQL ODBC 3.51 Driver};SERVER=munja;DATABASE=test;UID=root;PWD=;PORT=3307,OPTION=3") rsTest = New ADODB.Recordset rsTest.Open("SELECT id, `default` FROM reserved", cnTest, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText) rsTest.AddNew() rsTest.Fields("default").Value = "Test" 'rsTest.AddNew("`default`", "Test") rsTest.Update() rsTest = Nothing cnTest = Nothing End Sub End Module
[11 May 2007 19:08]
Erica Moss
ODBC trace file
Attachment: SQL.LOG (application/octet-stream, text), 56.48 KiB.
[17 May 2007 20:24]
Jim Winstead
This is a duplicate of Bug #19875, even though this is about fields with keywords as names, and the other is about fields with spaces in their names. It's the same underlying problem of identifiers not being quoted.
[30 Jul 2007 5:25]
king li
I still got this problem when i try to update a field which contains chinese My email address: kingl_xp@126.com