.SQL dump File Syntax

Hey guys! so… could use a bit of help.

I have an SQL dump file I want to import to SQLITE and or MySQL. It’s from MariaDB. but MySQL is saying syntax error, and SQLite is saying invalid format.

Basically. If a record has an apostrophe in the text, the entire file is screwed up.

INSERT INTO companies VALUES (1,994674,'Weber\\'s Water & Dozer Service, Inc.',5,'','Water Hauling, Oilfield Construction','',0,1438794320,'lap',1410976501,'lap',1382634317,'lap',1,1,'',0,'',0,'',999,0,'','',0,0); Query OK, 1 row affected (0.00 sec)

I have a million records.

anyone know a solution I don’t?

the proper syntax should be

INSERT INTO MAIN.fred (column1) VALUES('Webers'' Dozer Service, Inc.');

ok, so how would I convert the file. or do you think there should be an export option on Maria’s platform?

and for something like “O’brian”

should be Ob''rian

what am i missing?

I cannot speak to MariaDB, but the syntax I mentioned above is what SQLite will accept (I tested it with Tadpole before I posted it as an answer)

this is what Tadpole said about the "escape " syntax

you could use a text editor to

  • change all ’ to " - single quotes changed to double quotes
  • change all " to ‘’ - slash double to a pair of singles

of course make a backup of the file before you make those kind of changes!!

Prepared statements!

Thanks brotha.

So. just to make sure I have the right understanding here…

INSERT INTO companies VALUES (1,994674,'Weber\\'s Water & Dozer Service, Inc.',5,'','Water Hauling, Oilfield Construction','',0,1438794320,'lap',1410976501,'lap',1382634317,'lap',1,1,'',0,'',0,'',999,0,'','',0,0);

should be

INSERT INTO companies VALUES (1,994674,"Weber''s Water & Dozer Service, Inc.",5,"","Water Hauling, Oilfield Construction","",0,1438794320,"lap",1410976501,"lap",1382634317,"lap",1,1,"",0,"",0,"",999,0,"","",0,0);

using the find and replace all values options for slash - single

\\'

making it

a pair of singles

' '

and all current singles throughout the syntax to quotation marks

"

won’t do any good, since he has a text file of SQL statements exported from another database engine

Oh :frowning:

[quote=354561:@Robert Vanleeuwen]Thanks brotha.

So. just to make sure I have the right understanding here…

INSERT INTO companies VALUES (1,994674,'Weber\\'s Water & Dozer Service, Inc.',5,'','Water Hauling, Oilfield Construction','',0,1438794320,'lap',1410976501,'lap',1382634317,'lap',1,1,'',0,'',0,'',999,0,'','',0,0);

should be

INSERT INTO companies VALUES (1,994674,"Weber''s Water & Dozer Service, Inc.",5,"","Water Hauling, Oilfield Construction","",0,1438794320,"lap",1410976501,"lap",1382634317,"lap",1,1,"",0,"",0,"",999,0,"","",0,0);

using the find and replace all values options for slash - single

\\'

making it

a pair of singles

' '

and all current singles throughout the syntax to quotation marks

"

No, the double quote does not work in SQLite. Just change \’ to ‘’ and leave the single quote ’ as a single quote.

Simon, I beg to disagree… double quote DOES work and in this case allows him to search and replace in a way to end up with viable SQL Code

INSERT INTO MAIN.fred ( column1 ) VALUES ("Webers''Dozer Service , Inc.")

works perfectly fine

[quote=354572:@Dave S]Simon, I beg to disagree… double quote DOES work and in this case allows him to search and replace in a way to end up with viable SQL Code

INSERT INTO MAIN.fred ( column1 ) VALUES ("Webers''Dozer Service , Inc.")

works perfectly fine[/quote]
I bow to you, Dave, as you know better than me.

All I can say in my defence is that the double quotes do not work in my SQLite Control ! It must be something to do with my parsing of the SQL code!

No worries (and no need to bow :slight_smile: )
but that being said… I don’t want anyone to think that DOUBLE quotes will work for other database engines, because they may not… ORACLE is a prime example where DOUBLE quotes have a very specific function and cannot be used this way (but then Oracle recognizes an escaped single quote where it seems SQLite does not)

I made sure that Tadpole observed as much as possible “legal” SQLite syntax

Replacing single with double shouldn’t be necessary.

INSERT INTO MAIN.fred ( column1 ) VALUES ('Weber''s Dozer Service , Inc.')

should work as well.