List:MySQL ODBC« Previous MessageNext Message »
From:Michael Widenius Date:October 20 1999 6:37pm
Subject:Re: Problem updating/inserting DATETIME data
View as plain text  
>>>>> "Gary" == Gary Wong <gary.wong@stripped> writes:

Gary> I could be way off base and I don't know enough about writing ODBC 
Gary> drivers, but I think that the SQL statement referred to by the TCX 
Gary> error message is either created by the ODBC driver, or by the ADO
Gary> object.  It isn't created by my code.  Here's a sample of how the
Gary> problem was generated:

Gary> <CODE>
Gary> set xConn = Server.CreateObject("ADODB.Connection")
Gary> xConn.Open "myDSNname" 		' Open connection using correct DSN 
Gary> Set res =  Server.CreateObject("ADODB.Recordset")
Gary> ' 2 for Open Dynamic, 3 for Optimistic Locking, 2 for Table
Gary> res.Open "MysqlTable", xConn, 2, 3, 2   ' Opens the table "MysqlTAble"
Gary> into a recordset
Gary> res.AddNew			' Add a new row
Gary> res.Fields(0) = 100		' Set first field (integer) to 100
Gary> res.Fields(1) = "Line Number 1"	' Set second field (text) to some text
Gary> value
Gary> res.Fields(2) = date()		' Set third field (datetime) named "EndDate" to
Gary> current date
Gary> res.Update			' Call update to commit the data
Gary> res.Close			' Close the recordset
Gary> set rec = nothing
Gary> xConn.close
Gary> </CODE>

Gary> This would create an error similar to the one below, except that
Gary> there is no WHERE clause in selecting the recordset.  The 
Gary> "EndDate=1999-10-10 00:00:00" comes from the line "res.Fields(2) =
Gary> date()".  
Gary> I cannot do this: "res.Fields(2) = "'" & date() & "'" because
Gary> that column is defined to be a date field.  I have to give it an
Gary> actual date variant, and either ADO or MyODBC converts that into
Gary> "1999-10-10 00:00:00", and then adds it to the SQL statement
Gary> which is sent to MySQL for processing.

Hi!

MyODBC should add " around all date columns.

Can you please verify this with an MyODBC trace ?

Regards,
Monty
Thread
Problem updating/inserting DATETIME dataGary Wong20 Oct
  • Re: Problem updating/inserting DATETIME dataHendrik M.J. Arnoldus20 Oct
  • Re: Problem updating/inserting DATETIME dataGary Wong20 Oct
    • Re: Problem updating/inserting DATETIME dataMichael Widenius20 Oct
  • Problem updating/inserting DATETIME dataMichael Widenius20 Oct
  • Re: Problem updating/inserting DATETIME dataGary Wong21 Oct
    • Re: Problem updating/inserting DATETIME dataMichael Widenius21 Oct
  • Re: Problem updating/inserting DATETIME dataHendrik M.J. Arnoldus21 Oct
  • Re: Problem updating/inserting DATETIME dataGary Wong21 Oct
  • Re: Problem updating/inserting DATETIME dataGary Wong25 Oct
Re: Problem updating/inserting DATETIME dataMichael Widenius23 Oct