DBI SQL statement 
Author Message
 DBI SQL statement

Hello,
I have created a ODBC web Database. The user inputs a city which is then
used as part of the following select statement to return information.

$sqlStatement= qq!SELECT * FROM PLACE WHERE W_CITY = '$city';!;
$dataObject = executeSQLStatement($sqlStatement);

For most cities entered it works fine. The exception is when a city is
enetered like "St. John's" which contains an apostrophe. In this case I
receive the message...

Software error:
Can't call method "execute" without a package or object reference at
c:\users\cpp\cgi-shl\cpdb.pl line 138.

Now I beleive it has something to do with the fact that the apostrophe is
used as a delimeter in the select statement. What I would like to know is
how I can get around this. DBI select statements seem to require the
apostrophe as a delimeter. I have tried replacing the apostrophe with double
quotes but the statement then doesn't work at all.

Any help would be greatly apreciated.

Thanks,
Rick.



Sat, 22 Dec 2001 03:00:00 GMT  
 DBI SQL statement
You, yes you, Rick. Stop writing things like this:

: Now I beleive it has something to do with the fact that the apostrophe is
: used as a delimeter in the select statement. What I would like to know is
: how I can get around this. DBI select statements seem to require the
: apostrophe as a delimeter. I have tried replacing the apostrophe with double
: quotes but the statement then doesn't work at all.

 I believe that it is the SQL that requires this. I think you can
 escape it by doubling it up i.e 'St. John''s'

        You may have to do a little checking and cleaning on all of your
 variables that will end up in SQL statements.

/tex
--
Warning: Dates on Calendar are closer than they appear.



Sat, 22 Dec 2001 03:00:00 GMT  
 DBI SQL statement
Rick,

   I had similar problems with this at one point with a form that had about 20
text fields in it! My solution was to come up with a subroutine that I called
dbStringFix. It would parse through the entry and add an extra ' if one was
found, i.e., "St. John's" would have become "St. John''s". This fixes the
problem. In your case, I would call it like this:

$sqlStatement=<SQL goes here>
$data=&dbStringFix($sqlStatement);
$dataObject=executeSQLStatement($data);

In the spirit of open source code, here is my dbStringFix routine. It was
written for Perl 5 on a UNIX system, but I believe that it should work on your
MS system as well. I have put this routine in a seperate .pl file so that it is
available to all programs that I write by using an include statement.

sub dbStringFix
{
  local($i, $str1, $str2, $partsCount, $routine);

  $str1=$_[0];
  $routine="dbStringFix";



  if ($partsCount > 1)
  {
    $str2=$parts[0];
    for ($i=1; $i<$partsCount; $i++)
    {
      $str2=$str2."''".$parts[$i];
    }
  }
  else
  {
    $str2=$str1;
  }
  $str2;

Quote:
}

As you can see, it simply reads in the string as $str1, checks for any '
characters and doubles them if they exist and then returns the value of $str2
which is a string that will work with DBI! Hope that this helps.

Brad McBride

Quote:

> Hello,
> I have created a ODBC web Database. The user inputs a city which is then
> used as part of the following select statement to return information.

> $sqlStatement= qq!SELECT * FROM PLACE WHERE W_CITY = '$city';!;
> $dataObject = executeSQLStatement($sqlStatement);

> For most cities entered it works fine. The exception is when a city is
> enetered like "St. John's" which contains an apostrophe. In this case I
> receive the message...

> Software error:
> Can't call method "execute" without a package or object reference at
> c:\users\cpp\cgi-shl\cpdb.pl line 138.

> Now I beleive it has something to do with the fact that the apostrophe is
> used as a delimeter in the select statement. What I would like to know is
> how I can get around this. DBI select statements seem to require the
> apostrophe as a delimeter. I have tried replacing the apostrophe with double
> quotes but the statement then doesn't work at all.

> Any help would be greatly apreciated.

> Thanks,
> Rick.



Mon, 24 Dec 2001 03:00:00 GMT  
 DBI SQL statement
Yes your right about the doubling up of quotes. I also realize that I could
parse the statement, but  I thought that there might be an easier way and
there is. Thanks to Richard H, I found out that there is a built in function
called quotes that will handle this situation.

Rick.

Quote:

> I believe that it is the SQL that requires this. I think you can
> escape it by doubling it up i.e 'St. John''s'

> You may have to do a little checking and cleaning on all of your
> variables that will end up in SQL statements.

>/tex
>--
>Warning: Dates on Calendar are closer than they appear.



Mon, 24 Dec 2001 03:00:00 GMT  
 DBI SQL statement

Quote:

>Hello,
>I have created a ODBC web Database. The user inputs a city which is then
>used as part of the following select statement to return information.

>$sqlStatement= qq!SELECT * FROM PLACE WHERE W_CITY = '$city';!;
>$dataObject = executeSQLStatement($sqlStatement);

>For most cities entered it works fine. The exception is when a city is
>enetered like "St. John's" which contains an apostrophe. In this case I
>receive the message...

>Software error:
>Can't call method "execute" without a package or object reference at
>c:\users\cpp\cgi-shl\cpdb.pl line 138.

For quoting purposes DBI provides the quote method.  

ie:
$city = $dbh->quote($city);
$sqlStatement= qq!SELECT * FROM PLACE WHERE W_CITY = $city;!;

Martin



Mon, 24 Dec 2001 03:00:00 GMT  
 DBI SQL statement
try

$dbh->quote()

(I think this is the name).  Use it something like this...

$sql = "INSERT INTO MYTABLE (MY_TEXT_COLUMN) VALUES ( "
       . $dbh->quote($thevalue)
       . " ); " ;

Quote:

> You, yes you, Rick. Stop writing things like this:

> : Now I beleive it has something to do with the fact that the apostrophe is
> : used as a delimeter in the select statement. What I would like to know is
> : how I can get around this. DBI select statements seem to require the
> : apostrophe as a delimeter. I have tried replacing the apostrophe with double
> : quotes but the statement then doesn't work at all.

>  I believe that it is the SQL that requires this. I think you can
>  escape it by doubling it up i.e 'St. John''s'

>         You may have to do a little checking and cleaning on all of your
>  variables that will end up in SQL statements.

> /tex
> --
> Warning: Dates on Calendar are closer than they appear.



Sun, 30 Dec 2001 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. DBI SQL Statement

2. execute a Perl Programm with DBD:DBI - SQL-Statements in a file

3. Recommendations for escaping apostrophes in DBI SQL statements?

4. DBD::Oracle PL/SQL statement emulating a SQL statement

5. Problem with SQL statement in DBI

6. ORA-00900: invalid SQL statement when using Perl DBI::Oracle module

7. dbi:Oracle & SQL statement with date

8. Valid SQL Statement doesn't work in DBI::ODBC

9. DBI and DESCRIBE statement vs select statements

10. What is going to happen if prepare SQL statements without executing them

11. Announce: SQL::Statement 0.1002

12. How to split SQL statement nicely

 

 
Powered by phpBB® Forum Software