LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 07-01-2008, 11:56 PM   #1
rnturn
Member
 
Registered: Jan 2003
Location: Illinois (Chicago area)
Distribution: Red Hat (8.0), SuSE (10.x, 11.x, 12.2, 13.2), Solaris (8-10), Tru64
Posts: 982

Rep: Reputation: 53
PostgreSQL quoting problem


I'm working on a Perl script to generate SQL commands that will be used to load a table in a PostgreSQL 8.3 database. So far the SQL I'm generating is only being dumped into a file for use in a "psql -f filename" command; I'll get the DBI code added once I get past the problem I've run into when a string contains a particularly problematic character: commas.

The insert that's being generated will look something like:
Code:
INSERT INTO atable (name,model,options) VALUES ('Widget','Red','A,B,C');
(The "options" column will almost certainly need to contain a string that contains commas but I don't really need these separated when they're stored in the database.) The insert fails with an error message that indicates that there are more values than columns. Obviously, the quoting of the third value isn't working as one would think and the embedded commas are being interpreted as value delimiters. I've tried dealing with the commas in various ways:
Code:
'A,B,C' -> 'A\,B\,C'
Code:
'A,B,C' -> $$A,B,C$$
etc. to no avail. FWIW, an insert similar to the first code block above works on an 8.2 database.

The PostgreSQL documentation hasn't been any help. There's a fair amount of discussion of how to deal with certain special characters, how to deal with quotation marks in strings, etc. but haven't been able to find anything on how to handle commas in a string. (The closest thing I have found that offers any hope is to define any column as an array if it might contain a comma which seems like a serious over-complication not to mention a nightmare to code).

This seems like a common thing that one would encounter. I have to believe that there's an example of how to handle this situation. So far I haven't found anything in my reference library and Google's sure not been coming up with any good URLs.

Any ideas?

TIA

--
Rick
 
Old 07-02-2008, 02:37 AM   #2
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,005
Blog Entries: 11

Rep: Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903
Well, the code snippet with the insert works in psql.
Are you sure that this is the string you're throwing at
the DB in your perl code?

Code:
tink  =# create TABLE atable (name varchar(20), model varchar(20), options varchar(20));
CREATE TABLE
tink  =# INSERT INTO atable (name,model,options) VALUES ('Widget','Red','A,B,C');
INSERT 0 1
tink  =# SELECT * from atable ;
  name  | model | options 
--------+-------+---------
 Widget | Red   | A,B,C

Cheers,
Tink

Last edited by Tinkster; 07-02-2008 at 02:39 AM. Reason: added code snippet
 
Old 07-02-2008, 05:31 AM   #3
j-ray
Senior Member
 
Registered: Jan 2002
Location: germany
Distribution: ubuntu
Posts: 1,457

Rep: Reputation: 104Reputation: 104
this may depend on how the options field is defined in the database. It should be no problem if it is defined as char or varchar or similar. So is it an enumeration field or similar?

Perl provides you with two functions to quote strings "automatically", qq and qw. You can find documentation typing
perldoc -f qq
These functions circumvent the problem that variables dont get interpolated eventually ++.

if you simply want to erase the commas sth like that should work:
$value_str=~s/,/ /g;

Last edited by j-ray; 07-02-2008 at 05:35 AM. Reason: additional info
 
Old 07-02-2008, 09:27 AM   #4
rnturn
Member
 
Registered: Jan 2003
Location: Illinois (Chicago area)
Distribution: Red Hat (8.0), SuSE (10.x, 11.x, 12.2, 13.2), Solaris (8-10), Tru64
Posts: 982

Original Poster
Rep: Reputation: 53
Quote:
if you simply want to erase the commas sth like that should work:
$value_str=~s/,/ /g;
I considered that but then I thought that when it comes time to pull that information out of the database, I'd just have to put the commas back in. I wasn't too keen on having to do that.

--
Rick
 
Old 07-02-2008, 09:44 AM   #5
rnturn
Member
 
Registered: Jan 2003
Location: Illinois (Chicago area)
Distribution: Red Hat (8.0), SuSE (10.x, 11.x, 12.2, 13.2), Solaris (8-10), Tru64
Posts: 982

Original Poster
Rep: Reputation: 53
Aw jeez...

Quote:
Originally Posted by Tinkster View Post
Well, the code snippet with the insert works in psql.
Yah, it does when I manually insert data as well. So I took a look at the actual SQL that was being generated and found that I seem to have an error in the Perl source somewhere.

The problem turns out to be caused not by an extra comma in the values but rather a missing column name in the list. The list of columns and values are being generated dynamically as the Perl parses the data file. I have an error in the code that not putting one of the column names in the list. So instead of ten column names and ten values, I only have nine column names.

Time to turn 'strict' back on. I must have a typo in there somewhere. Now to find it. (Before the phone rings with the next fire alarm.)

Thanks for the mental nudge. Sometimes that's all it takes.

--
Rick
 
Old 07-02-2008, 02:19 PM   #6
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,005
Blog Entries: 11

Rep: Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903Reputation: 903
Quote:
Originally Posted by rnturn View Post
The list of columns and values are being generated dynamically as the Perl parses the data file. I have an error in the code that not putting one of the column names in the list. So instead of ten column names and ten values, I only have nine column names.
That would give that error, I guess :}

I didn't suspect the values in the single quotes (I might have
had you mentioned array types, but even then it would have been
quite unlikely to be the error you saw).



Cheers,
Tink
 
Old 07-02-2008, 09:05 PM   #7
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.6, Centos 5.10
Posts: 16,324

Rep: Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041Reputation: 2041
You should always use

#!/usr/bin/perl -w
use strict;

at the top of your progs. It'll save you a lot of hassle.
As for funny chars in strings, use this

dbh->quote($string)

http://search.cpan.org/~timb/DBI-1.605/DBI.pm#quote
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
bash script command substitution and quoting brian4xp Linux - Software 8 02-05-2008 12:43 PM
shell quoting within a function dazdaz Linux - Software 4 09-03-2007 12:01 PM
bash - quoting RGummi Linux - General 3 10-21-2006 04:06 PM
quoting web contents in the 'member area' of company homepage -- legal or not? baikonur General 11 06-14-2006 08:42 AM
Quick question: quoting text in sed overbored Linux - Software 0 06-24-2004 02:23 PM


All times are GMT -5. The time now is 05:11 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration