Re: Quotes and apostrophes assistance

2007-10-29 Thread anders 
On 29 Okt, 16:14, [EMAIL PROTECTED] (Beginner) wrote:
 Hi,

 I am trying to insert a lots file paths into an SQLite table and am
 having trouble with filenames with apostrophes in.

 Below is the snippet I have been using. I have experimented with
 sprintf,  qq and a combination of both but can't get a quoting system
 that will get DBI to accept paths like q(/path/to/Camellia japonica
 'Mercury'2.tif).

 I don't want to transform the path. I want to maintain the true path
 without having to do substitiutions everytime the data gets read from
 or inserted into the table. It would be a maintenance headache
 otherwise.

 Is there a technique for this anyone or any advice anyone can offer
 (apart from shooting the users how uploaded the files in the first
 place).

 TIA,
 Dp.

 sub Check_if_exists {
  my $name = shift;
  my $str = qq($name);
  my $statement = sprintf(SELECT id FROM files where
 path='%s',$str);
 # my $statement = qq(SELECT id FROM files where path=$str);
  my $rows = $dbh-selectrow_array($statement) or die Can't
 selectrow_array statement=$statement: ,$DBI::errstr,\n;
  return $rows;

 }

 DBD::SQLite::db selectrow_array failed: near Mercury: syntax
 error(1) at dbdimp.c line 271 at myfile.pm line 158.
 Can't selectrow_array statement=SELECT id FROM images where
 tiffpath='/data/finished/GB 10484/Camellia japonica
 'Mercury'2.NEF.tif': near Mercury: syntax error(1) at dbdimp.c line
 271

I have the same problem but with C and SQLITE it is becurse the
database don't like single '
(and some other characters) you have to transform them,  to dubble
example
' transforms to '' ( ' x 2 not the char )
I don't have tro transform back just for saving..

// Anders


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/

Re: Quotes and apostrophes assistance

2007-10-29 Thread Rob Coops 
Since I never worked with an SQLite DB I am not sure how well this will work
but you might want to have a look at the following command in the DBI:
$dbh-quote($value);

This should make the whole thing SQL approved with escaped special
characters and everything... Now as I said I have no idea how this will work
on SQLite but it is worth a try.

See: http://search.cpan.org/~timb/DBI-1.601/DBI.pm#quote for more
information

Regards,

Rob



On 10/29/07, Beginner [EMAIL PROTECTED] wrote:

 Hi,

 I am trying to insert a lots file paths into an SQLite table and am
 having trouble with filenames with apostrophes in.

 Below is the snippet I have been using. I have experimented with
 sprintf,  qq and a combination of both but can't get a quoting system
 that will get DBI to accept paths like q(/path/to/Camellia japonica
 'Mercury'2.tif).

 I don't want to transform the path. I want to maintain the true path
 without having to do substitiutions everytime the data gets read from
 or inserted into the table. It would be a maintenance headache
 otherwise.

 Is there a technique for this anyone or any advice anyone can offer
 (apart from shooting the users how uploaded the files in the first
 place).

 TIA,
 Dp.

 sub Check_if_exists {
 my $name = shift;
 my $str = qq($name);
 my $statement = sprintf(SELECT id FROM files where
 path='%s',$str);
 # my $statement = qq(SELECT id FROM files where path=$str);
 my $rows = $dbh-selectrow_array($statement) or die Can't
 selectrow_array statement=$statement: ,$DBI::errstr,\n;
 return $rows;
 }


 DBD::SQLite::db selectrow_array failed: near Mercury: syntax
 error(1) at dbdimp.c line 271 at myfile.pm line 158.
 Can't selectrow_array statement=SELECT id FROM images where
 tiffpath='/data/finished/GB 10484/Camellia japonica
 'Mercury'2.NEF.tif': near Mercury: syntax error(1) at dbdimp.c line
 271


 --
 To unsubscribe, e-mail: [EMAIL PROTECTED]
 For additional commands, e-mail: [EMAIL PROTECTED]
 http://learn.perl.org/

Re: Quotes and apostrophes assistance

2007-10-29 Thread Dr.Ruud 
Beginner schreef:

 I am trying to insert a lots file paths into an SQLite table and am
 having trouble with filenames with apostrophes in.

Use placeholders, so prepare/execute. 

-- 
Affijn, Ruud

Gewoon is een tijger.

-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/

Re: Quotes and apostrophes assistance

2007-10-29 Thread Dr.Ruud 
Rob Coops schreef:

 See: http://search.cpan.org/~timb/DBI-1.601/DBI.pm#quote for more
 information

Read
http://search.cpan.org/~timb/DBI-1.601/DBI.pm#Placeholders_and_Bind_Values
first.

-- 
Affijn, Ruud

Gewoon is een tijger.


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/

Re: Quotes and apostrophes assistance

2007-10-29 Thread Beginner 
On 29 Oct 2007 at 10:55, Paul Lalli wrote:

 On Oct 29, 12:31 pm, [EMAIL PROTECTED] (Dr.Ruud) wrote:
  Beginner schreef:
 
   I am trying to insert a lots file paths into an SQLite table and am
   having trouble with filenames with apostrophes in.
 
  Use placeholders,
 
 Good advice.
 
  so prepare/execute.
 
 irrelevant advice, nothing to do with the problem at hand.  There is
 no reason to alter the OP's method of using selectrow_array.
 
 my ($val) = $dbh-selectrow_array(SELECT foo FROM bar WHERE id = ?,
 undef, q{stuff'with'quotes'});
 
 http://search.cpan.org/~timb/DBI-1.601/DBI.pm#selectrow_array
 
Being new to DBI (and not much of expert in perl anyway) so I can't 
add much to the debate. 

What I can say is that this always dies

my $statement = sprintf(SELECT foo FROM bar where path=%s,$dbh-
quote($name));
my $rows = $dbh-selectrow_array($statement) or 
die Can't selectrow_array statement=$statement: ,$DBI::errstr, 
NO:,$DBI::err,$DBI::state\n;


or this:

my ($rows) = $dbh-selectrow_array(SELECT foo FROM bar where 
path=?,undef,q($name)) or die snip


whereas if I am do 

my $statement = sprintf(SELECT foo FROM bar where path=%s,
$dbh-quote($name));
my $sth = $dbh-prepare($statement) or 
die PREPARE:statement=$statement: ,$DBI::errstr,\n;
 $sth-execute or die EXECUTE: statement=$statement: 
,$DBI::errstr, NO:,$DBI::err,\n;
 my @rows;
 while (defined(my $i = $sth-fetchrow_array)) {
push(@rows,$i);
 }


It will process. What I want is to check if a fully qualified file 
path exists before attempting to insert a new record so I opted for 
the shorthand notation.

I didn't understand the 2 argument list in Paul's selectrow_array 
either.

I am currently fighting with 
$dbh-last_insert_id(undef,undef,undef,undef)

which constantly returns 0 so I can't get any further with my 
original query except to say the first one always returns a code to 
indicate a problem whereas the more verbose latter method will work.

BTW: SQLite 3.5.1 
DBI: 1.6.1.

Any further advice appreciated.
Dp.


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/

Re: Quotes and apostrophes assistance

2007-10-29 Thread Paul Lalli 
On Oct 29, 12:31 pm, [EMAIL PROTECTED] (Dr.Ruud) wrote:
 Beginner schreef:

  I am trying to insert a lots file paths into an SQLite table and am
  having trouble with filenames with apostrophes in.

 Use placeholders,

Good advice.

 so prepare/execute.

irrelevant advice, nothing to do with the problem at hand.  There is
no reason to alter the OP's method of using selectrow_array.

my ($val) = $dbh-selectrow_array(SELECT foo FROM bar WHERE id = ?,
undef, q{stuff'with'quotes'});

http://search.cpan.org/~timb/DBI-1.601/DBI.pm#selectrow_array

Paul Lalli


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/

Re: Quotes and apostrophes assistance

2007-10-29 Thread Dr.Ruud 
Beginner schreef:

 my ($rows) = $dbh-selectrow_array(SELECT foo FROM bar where
 path=?,undef,q($name)) or die snip

Your q($name) creates the literal string $name.

  my ($rows) =
  $dbh-selectrow_array(
  SELECT foo FROM bar where path=?,
  undef,
  $name,
  );

No need for die() if you set up the $dbh with 'RaiseError' on, 
see perldoc DBI. Also look for 'trace'. 

-- 
Affijn, Ruud

Gewoon is een tijger.

-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/