messy LOAD DATA INFILE query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

yarons
Forum Newbie
Posts: 24
Joined: Mon Sep 11, 2006 7:25 am

Post by yarons »

GM wrote:I can't make any guarantees, but for me the query I posted seemed to work in MySQL 5.0.2 (albeit with a single line of input).

Upgrading may affect other databases you've got, so proceed with caution if you choose this route.

Maybe someone else out there will be good enough to run a couple of tests in a MySQL 4 environment? As onion2k says, I find it strange that it isn't supported.

What is the exact error that you get when you try to run the query I posted?
I get: syntax error near '@dummy, col1, col2, col3, @dummy, col4, @dummy' on Line 1

Once I get rid of @dummy then it works fine, but obviously with the columns i don't want too.
I will try upgrading to 5 and see if it makes any difference.

Thanks a lot for all your help :)
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

hold on...

You have replaced my col1, col2, col3 etc. with your table column names haven't you???

EDIT:
Although thinking about it, I doubt this is the problem. If it were that, you'd be getting an unknown column error instead of a syntax error.
yarons
Forum Newbie
Posts: 24
Joined: Mon Sep 11, 2006 7:25 am

yes

Post by yarons »

I have done that :)

Still didn't upgrade to MySQL5.
did you have my 2nd problem while running on 5? I.e on the last column you go bot the first and last value?
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

No, because I assigned the first field to the @dummy variable, so I didn't get the first field at all. The last field I got from the data you posted, and it was entered correctly into the table.

I set up a dummy table:

Code: Select all

CREATE TABLE `input_test` (
  `col1` varchar(15) NOT NULL,
  `col2` varchar(15) NOT NULL,
  `col3` varchar(15) NOT NULL,
  `col4` varchar(15) NOT NULL,
  `col5` varchar(15) NOT NULL,
  `col6` varchar(15) NOT NULL,
  `col7` varchar(15) NOT NULL,
  `col8` varchar(15) NOT NULL,
  `col9` varchar(15) NOT NULL,
  `col10` varchar(15) NOT NULL,
  `col11` varchar(15) NOT NULL,
  `col12` varchar(15) NOT NULL,
  `col13` varchar(15) NOT NULL,
  `col14` varchar(15) NOT NULL,
  `col15` varchar(15) NOT NULL,
  `col16` varchar(15) NOT NULL,
  `col17` varchar(15) NOT NULL,
  `col18` varchar(15) NOT NULL,
  `col19` varchar(15) NOT NULL,
  `col20` varchar(15) NOT NULL,
  PRIMARY KEY  (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
and loaded data in with the query I posted earlier. No errors given, and the data appeared to be fine. (Note I only loaded 1 row of data, so I'm not sure what happens with a multi-row file.

If you are still getting errors, I suppose they are due to the MySQL version difference.
Post Reply