Skip to Content.
Sympa Menu

freetds - dynamic SQL and NUMERIC problems

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Eric Deutsch" <edeutsch AT systemsbiology.org>
  • To: freetds AT franklin.oit.unc.edu
  • Subject: dynamic SQL and NUMERIC problems
  • Date: Tue, 6 Mar 2001 14:14:06 -0500



Hi, I recently started using DBD::Sybase and FreeTDS to access some
data on a MSSQL Server with good results, but have a few minor issues,
and I'd like to find out if anyone can help with them.

The setup:
- Windows 2000 + MS SQL Server 2000
- Red Hat 7.0 with latest updates (perl 5.6.0)
- DBD-Sybase-0.91
- FreeTDS 0.51
(compiled with: ./configure --with-tdsver=7.0)

I can INSERT rows into the database and SELECT rows well, but there are
two slight problems:

1) I cannot use dynamic INSERTs, e.g.:
$sth = $dbh->prepare ("SELECT * FROM dbaccesstests WHERE test_id > ?");
$sth->bind_param (1,1)
I get:Panic: dynamic SQL (? placeholders) are not supported by the server
you are connecting to

I'm almost positive that SQL Server 2000 does support this, so I poked around
the FreeTDS code and find that in ct.c (line ~1131), the dynamic SQL detection
option has been forcibly disabled. Just for fun, I enabled dynamic SQL
detection, and the server seems to be report that it is capable of it, but now
when I try to run my test program, it fails with a simple "Cannot prepare
query".

I'm guessing that maybe dynamic SQL just hasn't been implemented in FreeTDS,
but can anyone give an authoritative answer on this? Might it be
soon?


2) I thought I once read a message about this problem somewhere, but cannot
find it now, so apologize for bringing it up again. Results returned for
a columntype of NUMERIC seems to truncate the last digit. This is pretty
obscure, and I only stumbled upon it while using someone else's tables
that had NUMERIC(10,0) instead of INT. This can be reproduced by creating
a table with columntype NUMERIC(10,0), inserting some number, and then
retrieving the values. The last digit won't appear. This is definitely a
SELECT to NUMERIC issue, because the number is actually correct within the
database.

I don't know if this is a FreeTDS issue, or a Sybase.pm issue, or just
something peculiar to my setup. Any ideas?

thanks,
Eric


I've attached my perl test script below in case it helps:


#! /usr/bin/perl
# dbaccesstests.pl - Perform several tests accessing the database


use DBI;
use strict;


my ($dsn) = "DBI:Sybase:server=billg;database=testdb";
my ($user_name) = "guest"; # user name
my ($password) = "guest"; # password
my ($dbh, $sth); # database and statement handles
my (@ary); # array for rows returned by query
my (%attr) = # error-handling attributes
(
PrintError => 0,
RaiseError => 0
);
my (%tables);
my ($tablename);
my ($counter);
my ($ntables);
my ($tablerows);
my ($querystring);


#### connect to database
$dbh = DBI->connect ($dsn, $user_name, $password, \%attr )
or bail_out ("Cannot connect to database");


#### Drop the test table
$querystring="DROP TABLE dbaccesstests";
print "[QUERY]: ",$querystring,"\n";
$sth = $dbh->prepare ($querystring)
or bail_out ("Cannot prepare query");
$sth->execute ()
or bail_out ("Cannot execute query");


#### Create the test table
$querystring="CREATE TABLE dbo.dbaccesstests (
test_id int identity,
intfield int,
charfield varchar(50),
numfield numeric(10,0),
floatfield float,
datefield datetime
)";
print "[QUERY]: ",$querystring,"\n";
$sth = $dbh->prepare ($querystring)
or bail_out ("Cannot prepare query");
$sth->execute ()
or bail_out ("Cannot execute query");


#### INSERT some rows
$querystring="INSERT INTO dbaccesstests
(intfield,charfield,numfield,floatfield,datefield) VALUES (
20,'butter',123,3.1415,'2000-01-25 18:00' )
INSERT INTO dbaccesstests
(intfield,charfield,numfield,floatfield,datefield) VALUES (
25,'milk',4567,2.1700,'1/22/2001' )
INSERT INTO dbaccesstests
(intfield,charfield,numfield,floatfield,datefield) VALUES ( 30,'swamp
gas',89012,1.234567890123456789,'5/10/98 10:11 pm' )";
print "[QUERY]: ",$querystring,"\n";
$sth = $dbh->prepare ($querystring)
or bail_out ("Cannot prepare query");
$sth->execute ()
or bail_out ("Cannot execute query");


if (1==1) {
#### issue query
$querystring="SELECT * FROM dbaccesstests";
print "[QUERY]: ",$querystring,"\n";
$sth = $dbh->prepare ($querystring)
or bail_out ("Cannot prepare query");
$sth->execute ()
or bail_out ("Cannot execute query");
}


if (0==1) {
#### issue query
print "syb_dynamic_supported=",$dbh->{syb_dynamic_supported},"\n";
$querystring="SELECT * FROM dbaccesstests WHERE test_id > ?";
print "[QUERY]: ",$querystring,"\n";
$sth = $dbh->prepare ($querystring)
or bail_out ("Cannot prepare query");
$sth->bind_param (1,1)
or bail_out ("Cannot bind parameter");
$sth->execute ()
or bail_out ("Cannot execute query");
}



#### read results of query
$counter = 0;
while (@ary = $sth->fetchrow_array ())
{
print join(" | ",@ary),"\n";
$counter++;
}

#my $rows = $sth->dump_results();


$DBI::err == 0
or bail_out ("Error during retrieval");


#### clean up
$sth->finish ()
or bail_out ("Cannot finish query");



#### ---------------------------------------------------------


$dbh->disconnect ()
or bail_out ("Cannot disconnect from database");
exit (0);


#### --------------------------------------------------------------
#### --------------------------------------------------------------
#### bail_out() subroutine - print error code and string, then exit

sub bail_out
{
my ($message) = shift;
die "$message\nError $DBI::err ($DBI::errstr)\n";
}






Archive powered by MHonArc 2.6.24.

Top of Page