Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

DBI:Sybase - Period in database name

by apu (Novice)
on Feb 22, 2013 at 23:59 UTC ( #1020250=perlquestion: print w/ replies, xml ) Need Help??
apu has asked for the wisdom of the Perl Monks concerning the following question:

Using DBI:Sybase to connect to an MS SQL Server. The database is the backend of a commercial inventory management system and I need to get some data out of it, massage it and generate some formatted output. My problem is that the database I need has a period in its name and, I think, DBI is interpreting this a separate between a database name and a table name. There is another database on the same server, generated by the same commercial program, named ps_inventory (without the ".ad") and I can connect to and query it without a problem.
my $DBNAME = 'ps_inventory.ad';
...
$dbh->do("use $DBNAME");
How do I escape the period? I tried quotes, backticks, a backslash, ... nothing seems to work Thanks in advance, Monks.

Comment on DBI:Sybase - Period in database name
Re: DBI:Sybase - Period in database name
by runrig (Abbot) on Feb 23, 2013 at 00:27 UTC
    Try quote_identifier:
    "quote_identifier" $sql = $dbh->quote_identifier( $name ); $sql = $dbh->quote_identifier( $catalog, $schema, $table, \%att +r ); Quote an identifier (table name etc.) for use in an SQL statement, by escaping any special characters (such as double quotation marks) it contains and adding the required type of outer quotation marks.
    Update: Hmm, all that function seems to do for 'foo.table' is wrap double quotes around it...so if that doesn't work for you, then I don't know what else to do.

    Another update: Aha (read other posts in this thread). Sybase quotes identifiers with brackets (e.g. "[column name with spaces]"), which suggests to me that quote_identifier should do the same, although there is also a DBD::Sybase attribute 'syb_quoted_identifier', which AFAICT is supposed to enable single quotes for quoting identifiers.

        $DBNAME = $dbh->quote_identifier( $DBNAME );
        $dbh->do("use $DBNAME");
      
      results in
         DBD::Sybase::db do failed: Server message number=170
         severity=15 state=1 line=1 server=SQLSERVER
         text=Line 1: Incorrect syntax near 'ps_inventory.ad'.

      (I did throw some additional debugging in there temporarily and it is quoting the $DBNAME but DBD::Sybase is still throwing the error.)

        what does sybase manual say about quoting database names? about error (number 170) ?
Re: DBI:Sybase - Period in database name
by roho (Monsignor) on Feb 23, 2013 at 08:10 UTC
    Try this:

    my $DBNAME = '[ps_inventory.ad]'; $dbh->do("use $DBNAME");

    "Its not how hard you work, its how much you get done."

      Thanks, roho! That works.
      This suggests to me that $dbh->quoted_identifier(..) should wrap things in brackets, not quotes, for DBD::Sybase, and so currently has a bug. Though there is also a syb_quoted_identifier atribute, which if set, should allow you to use quotes (though I've never used it). And I do now recall having to occasionally use brackets to quote some table/column names.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1020250]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (5)
As of 2014-12-27 10:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (176 votes), past polls