Re: DBI/Sybase problem

[Michael Peppler]

On Wed, 2005-08-24 at 14:09 -0400, Anderson, James H (Company IT) wrote:
> For a given server, dbconnect works in most cases, but fails for a
> specific userid/pswd. However, using isql I can connect to the server
> using the userid/pswd that hangs when used in DBI.

Off hand I don't see why that would happen.

If you can connect with isql you should be able to connect with
DBD::Sybase, as long as your environment is the same.

Try running this with DBI->trace(5) and send me (not the list) the
output.

Michael
-- 
Michael Peppler  -  [EMAIL PROTECTED]  -  http://www.peppler.org/
Sybase DBA/Developer
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html

DBI/Sybase problem

[Anderson, James H \(Company IT\)]

For a given server, dbconnect works in most cases, but fails for a specific 
userid/pswd. However, using isql I can connect to the server using the 
userid/pswd that hangs when used in DBI.

Any help greatly appreciated.

Thanks,

Jim


The code:

#!/ms/dist/perl5/bin/perl5.8

use strict;
use warnings;

use English;

use MSDW::Version
'DBD::Sybase' => '1.04',
'DBI' => '1.47';

use DBI;
use DBD::Sybase;


# # #  M A I N L I N E  # # #

my %dbinfo1 = (
   server   => "NYQ_SPR_CREDIT",# this connects fine
   DB   => "spr_credit_batch",
   user => "sprguest",
   pswd => '',
   );

my %dbinfo2 = ( # this hangs
   server   => "NYQ_SPR_CREDIT",
   DB   => "spr_credit_batch",
   user => "gracedev",
   pswd => '',
   );

dbconnect( \%dbinfo1 );
dbconnect( \%dbinfo2 );


# connect to database

sub dbconnect {
my ($dbinfo) = @_;

my $db = defined($dbinfo->{DB}) ? $dbinfo->{DB} : '';

warn <{server}
user: $dbinfo->{user}
database: $db
-
END_MSG

DBI->trace(2);
my $dbh = DBI->connect( "dbi:Sybase:server=$dbinfo->{server}", 
$dbinfo->{user}, $dbinfo->{pswd},
{ RaiseError => 1, AutoCommit => 1 } );

die "dbconnect failed for S=$dbinfo->{server}, U=$dbinfo->{user}, D=$db" 
unless $dbh;

if ( $dbinfo->{DB} ) {
$dbh->do( "use $dbinfo->{DB}" ) or die $dbh->errstr;
}

$dbh->{syb_do_proc_status} = 0;

warn < DBI->connect(dbi:Sybase:server=NYQ_SPR_CREDIT, sprguest, , 
HASH(0x83077e4))
-> DBI->install_driver(Sybase) for linux perl=5.008004 pid=2094 ruid=80055 
euid=80055
   install_driver: DBD::Sybase version 1.04 loaded from 
//ms/dist/perl5/PROJ/DBD-Sybase/1.04-5.8/lib/perl5/DBD/Sybase.pm
<- install_driver= DBI::dr=HASH(0x8263b04)
-> connect for DBD::Sybase::dr (DBI::dr=HASH(0x8263b04)~0x8158074 
'server=NYQ_SPR_CREDIT' 'sprguest'  HASH(0x830a30c)) thr#813b008
syb_db_login() -> checking for chained transactions
syb_db_login() -> chained transactions are  supported
-> prepare for DBD::Sybase::db (DBI::db=HASH(0x8158044)~0x830a300 'select 
@@version') thr#813b008
<- prepare= DBI::st=HASH(0x830a408) at Sybase.pm line 120
-> execute for DBD::Sybase::st (DBI::st=HASH(0x830a408)~0x830a3b4) 
thr#813b008
syb_db_opentran() -> ct_command(
BEGIN TRAN DBI830a768
) = 1
syb_db_opentran() -> ct_send() = 1
syb_db_opentran() -> ct_results(4047) == 1
syb_db_opentran() -> ct_results(4046) == 1
syb_st_execute() -> ct_command() OK
syb_st_execute() -> ct_send() OK
st_next_result() -> ct_results(4040) == 1
ct_res_info() returns 1 columns
describe() -> col 0, type 0, realtype 0
describe() retcode = 1
st_next_result() -> lasterr = 0, lastsev = 0
<- execute= -1 at Sybase.pm line 121
-> fetch for DBD::Sybase::st (DBI::st=HASH(0x830a408)~0x830a3b4) thr#813b008
<- fetch= [ 'Adaptive Server Enterprise/11.9.2.6/1290/P/EBF 10491 
ESD1/Sun_svr4/OS 5.5.1/FBO/Mon Jul 15 06:54:22 2002' ] row1 at Sybase.pm line 
122
-> STORE for DBD::Sybase::db (DBI::db=HASH(0x830a300)~INNER 
'syb_server_version' '11.9.2.6') thr#813b008
<- STORE= 1 at Sybase.pm line 124
-> DESTROY for DBD::Sybase::st (DBI::st=HASH(0x830a3b4)~INNER) thr#813b008
syb_st_finish() -> ct_cancel(CS_CANCEL_ALL)
syb_st_finish() -> resetting ACTIVE, moreResults, dyn_execed
syb_st_destroy: called on 8cc10c0...
syb_st_destroy(): freeing imp_sth->statement
syb_st_destroy(): cmd dropped: 1
<- DESTROY= undef at Sybase.pm line 116
<- connect= DBI::db=HASH(0x8158044) at DBI.pm line 596
-> STORE for DBD::Sybase::db (DBI::db=HASH(0x830a300)~INNER 'RaiseError' 1) 
thr#813b008
<- STORE= 1 at DBI.pm line 643
-> STORE for DBD::Sybase::db (DBI::db=HASH(0x830a300)~INNER 'PrintError' 1) 
thr#813b008
<- STORE= 1 at DBI.pm line 643
-> STORE for DBD::Sybase::db (DBI::db=HASH(0x830a300)~INNER 'AutoCommit' 1) 
thr#813b008
syb_db_commit() -> ct_command(
COMMIT TRAN DBI830a768
)
syb_db_commit() -> ct_send() OK
syb_db_commit() -> ct_results(4047) == 1
syb_db_commit() -> ct_results(4046) == 1
<- STORE= 1 at DBI.pm line 643
-> STORE for DBD::Sybase::db (DBI::db=HASH(0x830a300)~INNER 'Username' 
'sprguest') thr#813b008
<- STORE= 1 at DBI.pm line 646
<> FETCH= 'sprguest' ('Username' from cache) at DBI.pm line 646
<- connect= DBI::db=HASH(0x8158044)
-> STORE for DBD::Sybase::db (DBI::db=HASH(0x830a300)~INNER 
'dbi_connect_closure' CODE(0x8157fb4)) thr#813b008
<- STORE= 1 at DBI.pm line 666
-> do for DBD::Sybase::db (DBI::db=HASH(0x8158044)~0x830a300 'use 
spr_credit_batch') thr#813b008
1   -> prepare for DBD::Sybase::db (DBI::db=HASH(0x830a300)~INNER 'use 
spr_credit_batch' undef) thr#813b008
1   <- prepare= DBI::st=

DBD::Sybase problem correction

[Sean E Ewing]

Solaris is 5.8

I am trying to get DBD::Sybase to work in Perl 5.8, Solaris 5.8 and Sybase 12,
but I get the folowing error message:


install_driver(Sybase) failed:
Can't load
'/usr/local/lib/perl5/site_perl/5.8.0/sun4-solaris/auto/DBD/Sybase/Sybase.so'
for module DBD::Sybase: ld.so.1: /usr/bin/perl: fatal:
/home/sybase/OCS-12_5/lib/libct.so:
Permission denied at /usr/local/lib/perl5/5.8.0/sun4-solaris/DynaLoader.pm line
229.
 at (eval 1) line 3
Compilation failed in require at (eval 1) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at dbichk.pl line 4




This e-mail, including attachments, may include confidential and/or 
proprietary information, and may be used only by the person or entity to 
which it is addressed. If the reader of this e-mail is not the intended 
recipient or his or her authorized agent, the reader is hereby notified 
that any dissemination, distribution or copying of this e-mail is 
prohibited. If you have received this e-mail in error, please notify the 
sender by replying to this message and delete this e-mail immediately.

Re: Sybase problem

[Curt Russell Crandall]

Sorry about that, I didn't mean to send this since midway through writing
this I figured it out for myself.

--Curt

On Fri, 29 Jun 2001, Curt Russell Crandall wrote:

> I have a program that reads a file, and tries to process each line and
> insert the results into a database table.  If the processing fails, the
> line is written to a reject file where it is corrected and reprocessed.
> 
> In reject processing mode, I open the reject table, read a line, process
> it and then delete it from the table.  Each line in the reject table has a
> numeric id as its primary key.  If processing fails, the program reinserts
> the line back into the table under a different ID prior to 
> 
> 

Sybase problem

[Curt Russell Crandall]

I have a program that reads a file, and tries to process each line and
insert the results into a database table.  If the processing fails, the
line is written to a reject file where it is corrected and reprocessed.

In reject processing mode, I open the reject table, read a line, process
it and then delete it from the table.  Each line in the reject table has a
numeric id as its primary key.  If processing fails, the program reinserts
the line back into the table under a different ID prior to 

RE: Sybase & problem with finish()

[Sterin, Ilya]

Ah, guess that wasn't needed.
Sorry Michael, didn't see your post.

Ilya Sterin

-Original Message-
From: Sterin, Ilya [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 02, 2001 9:24 PM
To: David Sevier; [EMAIL PROTECTED]
Subject: RE: Sybase & problem with finish()


How about undef'ing the statement handle
undef($sth);

Then try to call disconnect().

Ilya Sterin

-Original Message-
From: David Sevier [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 02, 2001 5:49 PM
To: [EMAIL PROTECTED]
Subject: DBD:Sybase & problem with finish()


Hi!

I've got a strange problem with DBD:Sybase,

We have a program written that goes into our SQL Server box and lists the
active threads so that we can monitor it remotely. For the most part it
works fine. We've used the same script using DBD:ODBC, and DBD:ODBC through
the dbiproxy. We recently got the ability to have our linux box connect with
DBD:Sybase, and while that works much better than the proxy did for multiple
connections, it won't call finish(). The program just hangs. If you don't
call finish() or disconnect, it does the same thing. The only way to end it
is to call disconnect without calling finish(). Now, according to the
cheetah book, that's not supposed to cause any problems, but it's messy, and
gives us an annoying message saying "disconnect invalidates 1 active
statement handle (either destroy statement handles or call finish on them
before disconnecting).

Any ideas on what's wrong here? We don't get this error when we're not using
DBD:Syabse. I suppose we could just disable that message, but, I'd rather
find a way to fix the problem.

Thanks!!

David Sevier


#!/bin/perl

use DBI;
use SQLUtility;
use Getopt::Long;

my $connection  = "dbi:Sybase:sdcaecap02";
my $username= "X";
my $password= "X";


&GetOptions("dsn=s"  => \$connection
   ,"user=s" => \$username
   ,"password=s" => \$password
   );

$Command = "dbcc sqlperf (threads)";

### Connect to the database
my $dbh = connectiontodb($connection,$username,$password);

### Create a new statement handle to fetch table information
my $database_schema=performsqlaction($dbh,$Command);

my $NumberofFields = $database_schema->{NUM_OF_FIELDS};


### Iterate through all the tables...
  foreach ( my $i = 0; $i < $NumberofFields; $i++) {
my $name = $database_schema->{NAME}->[$i];
printf "%-10s   ",$name ;
  }
  print "\n";

  foreach (my $i = 0; $i < $NumberofFields; $i++) {
my $name = "=";
printf "%-10s   ",$name;
  }
  print "\n";

while (my $hash_ref = $database_schema->fetchrow_hashref() )
{

### Tidy up NULL fields
  foreach (my $i = 0; $i < $NumberofFields; $i++)
  {
my $name = $database_schema->{NAME}->[$i];
printf "%-10s   ", $hash_ref->{$name} ;
  }
  print "\n";
}
$database_schema->finish() or die "Can't finish it!\n";
$dbh->disconnect;

exit;
_
Get your FREE download of MSN Explorer at http://explorer.msn.com

RE: Sybase & problem with finish()

[Sterin, Ilya]

How about undef'ing the statement handle
undef($sth);

Then try to call disconnect().

Ilya Sterin

-Original Message-
From: David Sevier [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 02, 2001 5:49 PM
To: [EMAIL PROTECTED]
Subject: DBD:Sybase & problem with finish()


Hi!

I've got a strange problem with DBD:Sybase,

We have a program written that goes into our SQL Server box and lists the
active threads so that we can monitor it remotely. For the most part it
works fine. We've used the same script using DBD:ODBC, and DBD:ODBC through
the dbiproxy. We recently got the ability to have our linux box connect with
DBD:Sybase, and while that works much better than the proxy did for multiple
connections, it won't call finish(). The program just hangs. If you don't
call finish() or disconnect, it does the same thing. The only way to end it
is to call disconnect without calling finish(). Now, according to the
cheetah book, that's not supposed to cause any problems, but it's messy, and
gives us an annoying message saying "disconnect invalidates 1 active
statement handle (either destroy statement handles or call finish on them
before disconnecting).

Any ideas on what's wrong here? We don't get this error when we're not using
DBD:Syabse. I suppose we could just disable that message, but, I'd rather
find a way to fix the problem.

Thanks!!

David Sevier


#!/bin/perl

use DBI;
use SQLUtility;
use Getopt::Long;

my $connection  = "dbi:Sybase:sdcaecap02";
my $username= "X";
my $password= "X";


&GetOptions("dsn=s"  => \$connection
   ,"user=s" => \$username
   ,"password=s" => \$password
   );

$Command = "dbcc sqlperf (threads)";

### Connect to the database
my $dbh = connectiontodb($connection,$username,$password);

### Create a new statement handle to fetch table information
my $database_schema=performsqlaction($dbh,$Command);

my $NumberofFields = $database_schema->{NUM_OF_FIELDS};


### Iterate through all the tables...
  foreach ( my $i = 0; $i < $NumberofFields; $i++) {
my $name = $database_schema->{NAME}->[$i];
printf "%-10s   ",$name ;
  }
  print "\n";

  foreach (my $i = 0; $i < $NumberofFields; $i++) {
my $name = "=";
printf "%-10s   ",$name;
  }
  print "\n";

while (my $hash_ref = $database_schema->fetchrow_hashref() )
{

### Tidy up NULL fields
  foreach (my $i = 0; $i < $NumberofFields; $i++)
  {
my $name = $database_schema->{NAME}->[$i];
printf "%-10s   ", $hash_ref->{$name} ;
  }
  print "\n";
}
$database_schema->finish() or die "Can't finish it!\n";
$dbh->disconnect;

exit;
_
Get your FREE download of MSN Explorer at http://explorer.msn.com