Bug #7501 Unknown column "XXX" in 'field list'
Submitted: 23 Dec 2004 5:35 Modified: 13 Jun 2013 12:38
Reporter: weetat yeo Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:4.1.2 .alpha OS:Microsoft Windows (Window Professional)
Assigned to: Bogdan Degtyariov CPU Architecture:Any

[23 Dec 2004 5:35] weetat yeo
Description:
Hi all ,

  I have problem in MYSQL database , it always give me "unknown column "XXX" in 'field list' error. Eventhough the column are exists in my database.
This problem occurs when i deploy my application to the Window Pro PC which using MyODBC to connect to MYSQL database which in the Window 2000 PC.

Execute the VB code in W2K , it did not have the error message but it occurs when executed in the Window Professional PC.

Below is the VB code

Public Sub InitMYSQLDatabase()

Dim l_Database As String
Dim l_Server As String
Dim l_User As String
Dim l_Password As String

Set fileProperties = New clsNLBFileInit
l_Database = fileProperties.readSetting("MYSQL", "DATABASE")
l_Server = fileProperties.readSetting("MYSQL", "SERVER")
l_User = fileProperties.readSetting("MYSQL", "USER")
l_Password = fileProperties.readSetting("MYSQL", "PASSWORD")
    
On Error GoTo Handle
Set objConnection = New ADODB.Connection
    With objConnection
        .CursorLocation = adUseClient
        .ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                 "SERVER=" & l_Server & ";" & _
                  "DATABASE=" & l_Database & ";" & _
                  "USER=" & l_User & ";" & _
                  "PASSWORD=" & l_Password & ";"
        
        .Open
   End With

If objConnection.State = adStateOpen Then
    DoEvents
    'MsgBox "Connection was successfully opened...", vbInformation + vbOKOnly + vbSystemModal, "Successful"
End If

Handle:
If Err.Number = -2147467259 Then
     Err.Raise vbObjectError, "InitMYSQLDatabase()", _
            "No connection to the Database , the program will be close"
     Err.Clear
End If
If Err.Number <> 0 Then

     Err.Raise DATABASE_ERR
     Err.Clear
     
End If

End Sub

Public Sub InsertCCLGateTable(m_branch_code As String, m_gate_id As String, m_alarm_count As Integer, _
  m_visitor_count As Integer, is_newDay As Boolean)
 
 Dim SQL As String
 Dim cmdADODB As ADODB.Command
 Dim rstByADODB As ADODB.Recordset
 
 Dim branchCodeprm As ADODB.Parameter
 Dim gateIdprm As ADODB.Parameter
 Dim alarmCountprm As ADODB.Parameter
 Dim visitorCountprm As ADODB.Parameter
 Dim dateprm As ADODB.Parameter
 
 
  On Error GoTo Handle
  
    objConnection.BeginTrans

    If is_newDay Then
     SQL = "INSERT INTO gate_table(branch_code,gate_id,alarm_count,visitor_count,date) VALUES(?,?,?,?,?)"
    Else
     SQL = "UPDATE gate_table set branch_code = ? , gate_id = ?, alarm_count = ? , visitor_count = ? WHERE DATE = ?"
    End If
   
    Set cmdADODB = New ADODB.Command
    
    cmdADODB.CommandText = SQL
    cmdADODB.CommandType = adCmdText
    
    'Call SelectMinRecord
    
    'Notes:
    'add "'" Single quote to solve MYSQL bug : unknown column errors
    'for e.g : "'" & m_branch_code & "'"
    'Cause : maybe OS platform , in W2k no need single quotes but WINpro need to
    
    Set branchCodeprm = cmdADODB.CreateParameter("branch_code", adBSTR, adParamInput, 10, m_branch_code)
    Set gateIdprm = cmdADODB.CreateParameter("gate_id", adBSTR, adParamInput, 10,m_gate_id)
    Set alarmCountprm = cmdADODB.CreateParameter("alarm_count", adInteger, adParamInput, 1, m_alarm_count)
    Set visitorCountprm = cmdADODB.CreateParameter("visitor_count", adInteger, adParamInput, 1, m_visitor_count)
    Set dateprm = cmdADODB.CreateParameter("date", adDBDate, adParamInput, 1, Now)
    
    cmdADODB.Parameters.Append branchCodeprm
    cmdADODB.Parameters.Append gateIdprm
    cmdADODB.Parameters.Append alarmCountprm
    cmdADODB.Parameters.Append visitorCountprm
    cmdADODB.Parameters.Append dateprm
    
    Set cmdADODB.ActiveConnection = objConnection
    Set rstByADODB = cmdADODB.Execute
    
    If Not rstByADODB Is Nothing Then
     If rstByADODB.State = adStateOpen Then
        rstByADODB.Close
     End If
     Set rstByADODB = Nothing

    End If
      
  objConnection.CommitTrans
  
Handle:

If Err.Number = 3704 Then
    objConnection.RollbackTrans
    Err.Raise DATABASE_ERR
    Err.Clear
End If

If Err.Number <> 0 Then

     objConnection.RollbackTrans
     Err.Raise DATABASE_ERR
     Err.Clear
     
End If
  
  'cleaning up
  
  Set cmdADODB = Nothing
  Set rstByADODB = Nothing
  Cleaning branchCodeprm, gateIdprm, alarmCountprm, visitorCountprm, dateprm
  
End Sub

How to repeat:

This my table structure :

| gate_table | CREATE TABLE `gate_table` (
  `rid` bigint(20) NOT NULL auto_increment,
  `branch_code` varchar(10) default NULL,
  `gate_id` varchar(10) default NULL,
  `alarm_count` int(10) default NULL,
  `visitor_count` int(10) default NULL,
  `date` date default NULL,
  PRIMARY KEY  (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
[30 Dec 2004 1:19] MySQL Verification Team
Sorry if I am doing a stupid question but what it is the OS version
of Window Professional ?

Win95, CE, W2003, XP Pro ?

Thanks in advance.
[30 Dec 2004 1:31] weetat yeo
Window XP Professional SP 6
[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[28 May 2013 8:44] Bogdan Degtyariov
I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.
[28 May 2013 8:45] Bogdan Degtyariov
sorry, a mistake. This bug has to be re-verified.
[13 Jun 2013 12:38] Bogdan Degtyariov
I could not repeat the problem with the latest versions of Connector/ODBC 5.2 and 5.1. If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.