DBD::ODBC::db prepare failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tblClient'. (SQL-S0002) 
Author Message
 DBD::ODBC::db prepare failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tblClient'. (SQL-S0002)

Greetings,
I've finally connected to the database OK, I was having problems
before but that was some mis-communication between me and the
webserver host, my bad.

Now that I'm connected, I'm getting the following error:

DBD::ODBC::db prepare failed: [Microsoft][ODBC SQL Server Driver][SQL
Server]Invalid object name 'tblClient'. (SQL-S0002)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
be prepared. (SQL-37000)(DBD: st_prepare/SQLPrepare err=-1) at
D:\home\LPJ861\cgi-bin\dbsearch.pl line 75.

I know that the select statement works, I've ran it in SQL Query
Analyzer and it printed out the results no problem.

If anyone can point me to the error that I'm having or a way to fix
it, I'd be greatly appreciative.

Ryan

Code is as follows:

#!/usr/local/bin/perl

use strict;
use CGI qw(:standard);
use Data::Dumper;
use DBI;

#Retreiving variable information
my $keyWord = param("keyWord");
my $Area = param("cboArea");
my $Municipality = param("cboMun");
my $City = param("cboCity");
my $Category = param("cboCategory");

#Variable declarations
my $userID = "userID";
my $password = "password";

#Connects to the Database
my $dbh = DBI->connect( "dbi:ODBC:beHealthyManitoba", $userID,
$password,
    {RaiseError => 1, PrintError => 1, AutoCommit => 1})
                                or die "Unable to connect: " . $DBI::errstr . "\n";

#Tells if database is defined
print "\$dbh is ", (defined($dbh) ? "defined" : "undefined"), "\n";

#Prepares the select statement
my $sql = "SELECT FirstName, LastName, Organization, Address, Phone,
Website FROM tblClient ";

#keyWord entered
if ($keyWord ne "")
  {
    $sql = "$sql WHERE FirstName LIKE '%$keyWord%'";
        $sql = "$sql OR LastName LIKE '%$keyWord%'";
        $sql = "$sql OR Address LIKE '%$keyWord%'";
        $sql = "$sql OR CategoryKey LIKE '%$keyWord%'";
        $sql = "$sql OR Organization LIKE '%$keyWord%'";
        $sql = "$sql OR Phone LIKE '%$keyWord%'";
        $sql = "$sql OR AreaKey LIKE '%$keyWord%'";
        $sql = "$sql OR MunicipalityKey LIKE '%$keyWord%'";
        $sql = "$sql OR CityKey LIKE '%$keyWord%'";
        $sql = "$sql OR Website LIKE '%$keyWord%'";
  }
#Area selected
if ($Area ne "")
  {
        $sql = "$sql WHERE AreaKey = $Area";
  }
#Municipality selected
if ($Municipality ne "")
  {
        $sql = "$sql AND Municipality = $Municipality";
  }
#City selected
if ($City ne "")
  {
        $sql = "$sql AND City = $City";
  }
#Category selected
if ($Category ne "")
  {
        if ($sql =~ m/WHERE/i)
          {
            $sql = "$sql AND CategoryKey = $Category";
          }
        else
          {
            $sql = "$sql WHERE CategoryKey = $Category";
          }
  }
#End select Statement

print $sql;
#prepares select statement
my $pre = $dbh->prepare("$sql");

#Executes the select statement

#Disconnect from Database and exit
$pre->finish;
$dbh->disconnect;
exit



Sun, 31 Oct 2004 21:39:20 GMT  
 DBD::ODBC::db prepare failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tblClient'. (SQL-S0002)

Quote:

> Greetings,
> I've finally connected to the database OK, I was having problems
> before but that was some mis-communication between me and the
> webserver host, my bad.

> Now that I'm connected, I'm getting the following error:

> DBD::ODBC::db prepare failed: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Invalid object name 'tblClient'. (SQL-S0002)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
> be prepared. (SQL-37000)(DBD: st_prepare/SQLPrepare err=-1) at
> D:\home\LPJ861\cgi-bin\dbsearch.pl line 75.

> I know that the select statement works, I've ran it in SQL Query
> Analyzer and it printed out the results no problem.

As what user?  Is it possible that beHealthyManitoba has different
tables visible to the $userID you use in the cgi than the userid you
used when you were testing it with your query analyzer?

Quote:
> If anyone can point me to the error that I'm having or a way to fix
> it, I'd be greatly appreciative.

> Ryan

> Code is as follows:

[snip]
> my $dbh = DBI->connect( "dbi:ODBC:beHealthyManitoba",
[snip]
> my $sql = "SELECT FirstName, LastName, Organization, Address, Phone,
> Website FROM tblClient ";

--
print reverse( ",rekcah", " lreP", " rehtona", " tsuJ" )."\n";


Sun, 31 Oct 2004 22:36:03 GMT  
 DBD::ODBC::db prepare failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tblClient'. (SQL-S0002)

Quote:

> DBD::ODBC::db prepare failed: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Invalid object name 'tblClient'. (SQL-S0002)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
> be prepared. (SQL-37000)(DBD: st_prepare/SQLPrepare err=-1) at
> D:\home\LPJ861\cgi-bin\dbsearch.pl line 75.

Are you telling DBI to use the correct database that contains that
table? Maybe the default database is not the one where the table is.

Quote:
>   {
>     $sql = "$sql WHERE FirstName LIKE '%$keyWord%'";
>         $sql = "$sql OR LastName LIKE '%$keyWord%'";
>         $sql = "$sql OR Address LIKE '%$keyWord%'";
>         $sql = "$sql OR CategoryKey LIKE '%$keyWord%'";
>         $sql = "$sql OR Organization LIKE '%$keyWord%'";
>         $sql = "$sql OR Phone LIKE '%$keyWord%'";
>         $sql = "$sql OR AreaKey LIKE '%$keyWord%'";
>         $sql = "$sql OR MunicipalityKey LIKE '%$keyWord%'";
>         $sql = "$sql OR CityKey LIKE '%$keyWord%'";
>         $sql = "$sql OR Website LIKE '%$keyWord%'";

That looks very VB-ish to me. This is sometimes easier to read:

$sql .= <<END_OF_SQL;
 WHERE FirstName LIKE '%$keyWord%'
       OR LastName LIKE '%$keyWord%'
       OR Address LIKE '%$keyWord%'
       OR CategoryKey LIKE '%$keyWord%'
       OR Organization LIKE '%$keyWord%'
       OR Phone LIKE '%$keyWord%'
       OR AreaKey LIKE '%$keyWord%'
       OR MunicipalityKey LIKE '%$keyWord%'
       OR CityKey LIKE '%$keyWord%'
       OR Website LIKE '%$keyWord%'
END_OF_SQL

You might want to condense this:

Quote:
>#Area selected
>if ($Area ne "")
>  {
>        $sql = "$sql WHERE AreaKey = $Area";
>  }
>#Municipality selected
>if ($Municipality ne "")
>  {
>        $sql = "$sql AND Municipality = $Municipality";
>  }

into this ( I sometimes use 'if length $Area' ):

 $sql .= " WHERE AreaKey = $Area" if $Area ne '';
 $sql .= " AND Municipality = $Municipality" if $Municipality ne '';

- Ron



Sun, 31 Oct 2004 22:38:58 GMT  
 DBD::ODBC::db prepare failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tblClient'. (SQL-S0002)

Quote:

> DBD::ODBC::db prepare failed: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Invalid object name 'tblClient'. (SQL-S0002)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not
> be prepared. (SQL-37000)(DBD: st_prepare/SQLPrepare err=-1) at
> D:\home\LPJ861\cgi-bin\dbsearch.pl line 75.

This indicates that the table was not found.  You may need to qualify the
table name (indicate the catalog and/or schema name).

--
  Simon Oliver



Mon, 01 Nov 2004 09:30:07 GMT  
 DBD::ODBC::db prepare failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tblClient'. (SQL-S0002)
Quote:

> Win32::OLE(0.13): GetOleObject() Not a Win32::OLE object at
> d:/indigoPerl/site/lib/Win32/OLE/Lite.pm
>  line 154 during global destruction.

This shouldn't happen - and at first glance it looks to me like it is a
problem with your ODBC drivers.  Have you tried installing the latest
mdac?
  http://www.microsoft.com/data/download.htm

I would bypass ODBC alltogether if using DBD::ADO and use SQL server's
native OLE DB driver  - this should improve performance too :-)

my $dsn = "Provider=SQLOLEDB;Server=$srv;Database=$db";
my $dbh = DBI->connect( "dbi:ADO:$dsn", $uid, $pwd,
    {RaiseError => 1, PrintError => 0, AutoCommit => 1})

Quote:
> It's particularly interesting, since the error came during
> destruction, after the script was done executing, just like when I was
> using DBD::ODBC.

Yes - again perhaps you have a corrupt .dll.

--
  Simon Oliver



Mon, 01 Nov 2004 15:30:33 GMT  
 DBD::ODBC::db prepare failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tblClient'. (SQL-S0002)
Quote:


> > This indicates that the table was not found.  You
> > may need to qualify the
> > table name (indicate the catalog and/or schema
> > name).

> That makes sense.  Do you have any idea how to do
> this?  Sorry but I'm very new to Perl and my knowledge
> is rather limited.
> Thanks,
> Ryan

You're using MS SQL Server right?  A fully qualified table name would
include the database and owner:

database.owner.table

--
  Simon Oliver



Mon, 01 Nov 2004 15:06:50 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Win32:ODBC and Microsoft's SQL Server 6.5

2. SQL Server - DBD::ODBC - sp_setapprole - PerlEx

3. DBD::ODBC from 64bit Dec Alpha to MS SQL Server

4. DBI, DBD:ODBC error with MS SQL Server 6.5

5. DBD::ODBC and MS SQL server

6. DBD::ODBC and SQL Server from Unix

7. DBD::Altera DBI driver for Altera SQL Server

8. DBD::ADO and Microsoft OLEDB Provider for SQL Server

9. DBD Drivers for Microsoft SQL 6.5

10. LONG: Req: Help in retrieving values from SQL Server stored procedure using Win32::ODBC

11. ODBC perl module to access MS-SQL Server?

12. Connecting to SQL Server through ODBC using Perl

 

 
Powered by phpBB® Forum Software