INSERT INTO... fails

[ChrisRChamberlain]

Hi all

MySql newbie so please be gentle.

Am trying to migrate a VFP database to MySql by creating tables with the following typical code

CREATE TABLE FIND_STR (FIND_STR VARCHAR(10) NULL, DATE_STAMP DATETIME NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1

which succeeds.

When it comes to populating this table on a row by row basis with the following typical data, it fails

INSERT INTO FIND_STR (find_str,date_stamp) VALUES ('yeoma',1999-05-30 08:29:53)

The failure is consistent where there are MySql DATETIME fields in the table, otherwise all the tables are populated with the converted VFP data.

Clues as to the error(s) please?

TIA

FAQ184-2483​

Chris
PDFcommander.com
motrac.co.uk

 

[tsuji]

[0] I think you need to indicate the query is end by semi-colon.
[tt]
CREATE TABLE FIND_STR (FIND_STR VARCHAR(10) NULL, DATE_STAMP DATETIME NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1[red];[/red]
[/tt]
[0.1] But since it succeeds, so I guess there is a semi-colon there already.

[1] Datetime value has to be quoted as a string (with specific format requirement).
[tt]
INSERT INTO FIND_STR (find_str,date_stamp) VALUES ('yeoma',[red]'[/red]1999-05-30 08:29:53[red]'[/red]);
[/tt]

 

[ChrisRChamberlain]

tsuji

Thanks for your reply

Seems I missed the fact that the following statement from a well known author is specific to LOAD DATA INFILE ..., and does not apply to INSERT INTO statements.

Dates can be more troublesome, of course, but don’t have to be. Suppose you have a field in
your table, dob, defined simply as “DATE,” Not Null, and with a default value of ‘0000-00-
00’. In your text file, use the format YYYY-MM-DD, without surrounding the date in quotes
of any sort, like so:
19,Nolan,42,1980-10-1|
and the import will bring the date in as expected. However, you get an error if you
surround your date with single or double quotes, like so:
20,Olga,,’2001-10-20’|21,Petravich,,”2001-10-21”|

Amended code and all works as expected.


Thanks again

FAQ184-2483​

Chris
PDFcommander.com
motrac.co.uk