I'm currently attempting to re-write a cgi-shell script to query an MS SQL database into perl.
The shell script uses sqsh compiled against freetds to access the database, which whilst it works, doesn't really give the level of flexibility I'm after.
I'm confident that I'm managing to turn the query string, into appropriate SQL properly, but I'm having problems actually running the query and getting some results.
The SQL I'm trying to run is this:
 
set TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
set quoted_identifier on use "smdb";
SELECT "_SMDBA_"."_TELMASTE_".SEQUENCE AS "Problem #",
"_SMDBA_"."_CUSTOMER_".FNAME + ' ' + "_SMDBA_"."_CUSTOMER_".NAME AS "N
+ame",
"_SMDBA_"."_CUSTOMER_".EXT AS "Cl. Ext",
"_SMDBA_"."_TELMASTE_"."DESCRIPTION" AS "Problem Description",
"_SMDBA_"."_TELMASTE_"."DATE OPEN" AS "Opened",
"_SMDBA_"."_PERSONNEL_".CODE AS "Assigned To"
FROM "_SMDBA_"."_CUSTOMER_","_SMDBA_"."_TELMASTE_","_SMDBA_"."_PERSONN
+EL_"
WHERE "_SMDBA_"."_TELMASTE_"."SENT TO" = "_SMDBA_"."_PERSONNEL_".SEQUE
+NCE AND 
"_SMDBA_"."_TELMASTE_".CLIENT = "_SMDBA_"."_CUSTOMER_".SEQUENCE AND 
"_SMDBA_"."_PERSONNEL_".CODE LIKE 'MUSER' AND 
"_SMDBA_"."_TELMASTE_".STATUS = 'O'
ORDER BY "Problem #";

[download]
This works fine when cut/pasted directly into sqsh. The code to run it against my database looks like this: 
 
my $DSN = 'server=sqlsrv;database=\"smdb\"';
#my $DSN = 'server=sqlsrv';
#my $DSN = 'database=smdb;server=sqlsrv

$db_handle = DBI->connect("dbi:Sybase:$DSN", $DB_User{'username'},
                                $DB_User{'password'},
                                { RaiseError => 0,
                                  PrintError => 1,
                                  AutoCommit => 0,
                                  syb_quoted_identifier => 1,
                                  syb_rowcount => ( $input{rowcount} )
+ ? $input{rowcount} : 0 } ) or warn "$DBI::errstr";

$db_handle -> do ( "set TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;"
+ );
$db_handle -> do ( "set quoted_identifier on;" );
$db_handle -> do ( " use \"smdb\";" );


$trans_handle -> $dh_handle -> prepare ( $sql );
if ( $trans_handle -> execute )
{
   while ( @row = $trans_handle -> fetchrow )
   {
     print "<TR><TD>",join("</TD><TD>",@row), "</TD></TR>\n";
   }
}

[download]

This code merely returns 'Changed database context to 'master''. Which is expected, since that's the default for the user I connect as. I'd also expect another message though, saying that it had changed to a different context, which I'm not seeing.
After some messing around, it seems that if I do 
$db_handle -> execute or die "$DBI::errstr"

[download]
then it fails sure enough, but there's no 'die' message.
I can get _some_ sql to work. The basic 
$db_handle -> prepare("select \@\@servername");

[download]
seems to work just fine.
Now I'm far from a perl/database guru, so I'm fairly convinced that there's something pretty obvious that I've missed. However, I've spend half a day trolling the web, and experimenting with different combinations, so if anyone has an insight into what I'm doing wrong, a suggesting for 'better' diagnostics, or an example of how it should be done, I'd be deeply appreciative.

In reply to DBD::Sybase + MS SQL server problem by Preceptor

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.