RE: [sqlite] NULL popping up in INSERTed strings
> -Original Message- > From: Nate Bargmann [mailto:[EMAIL PROTECTED] > > While rare there are nasties like this: > "81565","Why Not Reform the > ""Squeek-Box?""","Editorials","Young","Sumner B.","1CO",1917,9 > just how does a person handle this case? With the Text::CSV_XS module, available on CPAN. -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] NULL popping up in INSERTed strings
Nate said: >Okay, the double quotes come from the CD-ROM and the only way to change >them is with a bunch of string mangling. I'm reluctant to do so since >each data file is on the order of 2 MB or more. Since my script and >SQLite didn't seem to notice the difference except for the >aforementioned '?"' problem. > >The single quoted text came from the .dump command in sqlite as I was >trying to show a before and after of the data I have to work with. I >realize I should have been more clear. > >If I need to change the double quote characters to single quote >characters when processing the data file, I will then need to escape >every apostrophe that is embedded in the string as well, right? As long as you think each file is consistently formatted, I would do something like this: $insert_sql_str = read_from_cd_file(); $insert_sql_str =~ s/'/''/g; # escape existing single quotes $insert_sql_str =~ s/"/'/g; # convert doubles to singles execute_sql_against_db( $insert_sql_str ); That does your mangling concisely, then. -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] NULL popping up in INSERTed strings
* Darren Duncan <[EMAIL PROTECTED]> [2004 Jan 06 05:49 -0600]: > The "problem" you are having is specific to Perl/DBI and not to SQLite. > > Having a bareword "?" in a SQL string is something special to DBI; it is a variable > binding operator and will be replaced with the contents of the Perl variable that > you pick. SQLite only sees the resulting SQL string after this substitution has > taken place. If no associated variable is provided, then NULL is used (although my > version throws an exception instead, though that's probably because >I 'use strict' and 'use warnings', as every Perl user should do). Inside a string, >the '?' is taken as a literal character. Thanks, Darren. That makes sense. Yes, I am using the strict and warnings modules as well, byt Perl hasn't complained about this. I am also starting perl with the -w switch. > Have a look here for a proper explanation: > > http://search.cpan.org/dist/DBI/DBI.pm#Placeholders_and_Bind_Values I shall do that. > That said, I noticed you used both double-quotes and single-quotes in your examples. > You need to always use single-quotes when you have a string literal in SQL, but if > the string literal is provided by a bind variable then you don't. For example, > "select * from foo where bar = 'here' and baz = ?". Double-quotes have a different > meaning in SQL strings, for more advanced things, and beginners should avoid them > for simplicity. Okay, the double quotes come from the CD-ROM and the only way to change them is with a bunch of string mangling. I'm reluctant to do so since each data file is on the order of 2 MB or more. Since my script and SQLite didn't seem to notice the difference except for the aforementioned '?"' problem. The single quoted text came from the .dump command in sqlite as I was trying to show a before and after of the data I have to work with. I realize I should have been more clear. If I need to change the double quote characters to single quote characters when processing the data file, I will then need to escape every apostrophe that is embedded in the string as well, right? Thanks! - Nate >> -- Wireless | Amateur Radio Station N0NB | Successfully Microsoft Internet | [EMAIL PROTECTED] | free since January 1998. Location | Bremen, Kansas USA EM19ov | "Debian, the choice of Amateur radio exams; ham radio; Linux info @ | a GNU generation!" http://www.qsl.net/n0nb/ | http://www.debian.org - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] NULL popping up in INSERTed strings
At 9:02 PM -0600 1/5/04, Nate Bargmann wrote: >I'm working with SQLite through a Perl script to store some data files >on CD-ROM into tables. Everything has gone rather well considering I am >learning SQL and SQLite as I go along. The data files are CSV and >appear to be an SQL variant used by a proprietary SQL engine installed >by the CD's setup program on another OS. >Anyway, I am seeing the following behavior--when the following line is >put into the database with an INSERT command: >"How Do You Like Our QST, OM?", ... >SQLite gives me this >'How Do You Like Our QST, OMNULL', ... >Yet, a '?' inside a string followed by a space passed into the column >data just fine. The "problem" you are having is specific to Perl/DBI and not to SQLite. Having a bareword "?" in a SQL string is something special to DBI; it is a variable binding operator and will be replaced with the contents of the Perl variable that you pick. SQLite only sees the resulting SQL string after this substitution has taken place. If no associated variable is provided, then NULL is used (although my version throws an exception instead, though that's probably because I 'use strict' and 'use warnings', as every Perl user should do). Inside a string, the '?' is taken as a literal character. Have a look here for a proper explanation: http://search.cpan.org/dist/DBI/DBI.pm#Placeholders_and_Bind_Values That said, I noticed you used both double-quotes and single-quotes in your examples. You need to always use single-quotes when you have a string literal in SQL, but if the string literal is provided by a bind variable then you don't. For example, "select * from foo where bar = 'here' and baz = ?". Double-quotes have a different meaning in SQL strings, for more advanced things, and beginners should avoid them for simplicity. -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
