Discipulus has asked for the wisdom of the Perl Monks concerning the following question:

Hello monks

i'm gona be mad about a msSQL connection; consider this code(working):
use DBI; my $connect_string = "driver={SQL Server};app=ProvolonePiccante;Server +=100.100.100.100;Database=TEST;UID=usrTEST;PWD=pwdTEST"; my $dbh = DBI ->connect ( "DBI:ODBC:host=localhost;$connect_string" )| +|die; my $str="SELECT * FROM usrTEST.test_table"; $sth = $dbh->prepare($str); $sth->execute ;
..and this other one that die on the execute whit:
DBD::ODBC::st execute failed: MicrosoftODBC SQL Server DriverSQL ServerInvalid object name 'usrTEST.test_table'. (SQL-42S02) MicrosoftODBC SQL Server DriverSQL ServerStatement(s) could not be prepared. (SQL-42000)(DBD: st_execute/SQLExecute err=-1) at test.pl line 7.
Nevermind about user.table is equal to table
use DBI; my $connect_string = "driver={SQL Server};app=ProvolonePiccante;Server +=100.100.100.100;Database=TEST;UID=usrTEST;PWD=pwdTEST"; my $dbh = DBI ->connect ( "DBI:ODBC:host=localhost;$connect_string" )| +|die; my $str="INSERT INTO usrTEST.test_table(\'id_oggi\') VALUES (\'111111\ +')"; $sth = $dbh->prepare($str); $sth->execute ;
Any help appreciated 'cause I have a script working and I cannot write another one. I've goggled a lot without solution. I guess the error is not truly the table name but..

thks a lot
Lor*

Replies are listed 'Best First'.
Re: DBI DBI:ODBC msSQL strangeness
by terce (Friar) on Nov 18, 2005 at 13:24 UTC
    As your error message clearly states, this is not a Perl problem.
    If you try and run your statement:

    INSERT INTO usrTEST.test_table('id_oggi') VALUES ('111111')

    in the sql query analyser, you will find it fails with the same error.

    I suggest you look at the documentation for a SQL insert statement - you'll see that you shouldn't quote the column names. Try this instead:

    INSERT INTO usrTEST.test_table(id_oggi) VALUES ('111111')

    When you've got it working in query analyser, then you can put it into you Perl script.
    A side point: you don't need to escape single quotes with \ in a double-quoted string.
      thnks terce,

      whitout quoting the column name, thanks for the remark, the script run well.. (I hate non Perl error string!)

      Lor*