MySQL and Datetime

Databases
1

I would like to put a timestamp in a mysql in the database and I’m using the following code:

dim d as new date dim rec as new DatabaseRecord rec.Column("When") =d.SQLDateTime Session.My_Database.InsertRecord "MY_Table", rec //Session hold the DB reference session.My_Database.Commit

But SQL throw the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'When,) values ('2014-01-21 13:06:26')' at line 1

I can pass straight strings in but not Datetime.

What am I missing?

2

[quote=60474:@Jay Menna]I would like to put a timestamp in a mysql in the database and I’m using the following code:

dim d as new date dim rec as new DatabaseRecord rec.Column("When") =d.SQLDateTime Session.My_Database.InsertRecord "MY_Table", rec //Session hold the DB reference session.My_Database.Commit

But SQL throw the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'When,) values ('2014-01-21 13:06:26')' at line 1

I can pass straight strings in but not Datetime.

What am I missing?[/quote]
Try

dim d as new date dim rec as new DatabaseRecord rec.DateColumn("When") = d Session.My_Database.InsertRecord "MY_Table", rec //Session hold the DB reference session.My_Database.Commit

3

I assume that ‘When’ is a reserved word and is not allowed as a ColumnName?

4

the “WHEN” got me…Doh!

5

You should be able to get around this by including ticks.

rec.Column("`When`") =d.SQLDateTime
6

[quote=60489:@Brock Nash]You should be able to get around this by including ticks.

rec.Column("`When`") =d.SQLDateTime

Seems like something the framework should be doing automatically.

7

[quote=60489:@Brock Nash]You should be able to get around this by including ticks.

rec.Column("`When`") =d.SQLDateTime

Better avoid reserved words, to avoid further possible sources of issues, or? :slight_smile:

8

ANSI SQL lets you do some really silly things like

select from from from where where = 1

if you have a table called FROM that has columns named FROM and WHERE

But ICK !

I’d avoid sql keywords as table & column names