Re: Installation problems on OS X 10.2.3
In case anyone cares. It appears the threaded version of Perl 5.8.0 on OSX is incompatible with DBI. This really bites since you need the threaded version for Apache 2.0.43 and mod_perl 1.99_07. So, on OSX, you are probably better off using FastCGI or, I hate to say, Java Servlets/JSP for dynamic web pages since not having DBI available pretty much makes mod_perl worthless... at least for me. The distro of Perl I'm using on my OSX box is from serverlogistics.com (aaronfaby.com... there's a link next to Rendezvous on Safari). Since Perl 5.8 is binary incompatible with 5.6.x, you have to recompile all of the XS mods (i.e. Storable)... but with the pkg from serverlogistics, that's all taken care off. I'm unsure if there are similar incompatibilities with threaded Perl5.8 and DBI on Linux... I'll try it out later today. My main Linux box runs Perl5.8 non-threaded and DBI-1.31 works fine. Hopefully, this incompatiblity with threaded Perl 5.8 and DBI gets resolved soon... otherwise Java is looking more like the route to go on OSX... and I really really hate Java. Curt
Re: Installation problems on OS X 10.2.3
I have already submitted the output from make in a previous. I don't think recompiling with a different version of gcc will help... Serverlogistics was quite clear that the threaded version is incompatible with DBI. I could be wrong about that, but I'm just going to go the path of least resistence now and use JSP/servlets and FastCGI on the Mac and if I want to do mod_perl, I go back to Linux. When the folks at Apple come out with a OS upgrade that includes gcc 3.2, Perl 5.8.x, etc, then I'll revisit this. I would assume that if you were using OSX 10.1 that you were using Perl5.6.0... that version I do not believe has the problems that Perl5.8.0 has (however, it has a whole slew of other problems... this is why I am upgrading). Thanks, Curt On Sat, 15 Feb 2003, David Wheeler wrote: On Saturday, February 15, 2003, at 09:57 AM, Curt Russell Crandall wrote: It appears the threaded version of Perl 5.8.0 on OSX is incompatible with DBI. I had it working under OS X 10.1 a while ago. Have you tried using the older gcc that comes with Mac OS X, instead of the default 3.1x? Try using the -fapple-kext argument to gcc. Also, could you send the output of your attempts to compile DBI with gcc 3.1 on Mac OS X and threaded Perl 5.8.0 to the list? Tim or another industrious hacker can probably figure out the issue and repair it that way. Regards, David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 Yahoo!: dew7e Jabber: [EMAIL PROTECTED] Kineticode. Setting knowledge in motion.[sm]
Re: Installation problems on OS X 10.2.3
I can give it a try later on... I'm in the midst of setting up my Linux box to be my webserver now. Thanks for the info, though. While Apple, might be shipping with some less than stable apps, they are making fast progress. Hopefully it won't be long before 10.2.x comes out and fixes these problems. On Sat, 15 Feb 2003, David Wheeler wrote: On Saturday, February 15, 2003, at 11:13 AM, Curt Russell Crandall wrote: I would assume that if you were using OSX 10.1 that you were using Perl5.6.0... that version I do not believe has the problems that Perl5.8.0 has (however, it has a whole slew of other problems... this is why I am upgrading). No, I meant that I used Perl 5.8.0 with iThreads and DBI on Mac OS X 10.1. That's why I recommended the gcc 2.95 compatability mode on Jaguar, because that's the primary thing that's different in Jaguar. David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 Yahoo!: dew7e Jabber: [EMAIL PROTECTED] Kineticode. Setting knowledge in motion.[sm]
Re: Installation problems on OS X 10.2.3
Ya, this is way beyond my knowledge too.
I'll check out what Jarkko has in regards to Perl5.8+iThreads+MacOSX.
BTW, I tried writting him at cpan.perl.org and my mails have been getting
bounced back. Anyone know if there's a problem with the CPAN mail addr?
Thanks,
Curt
On Tue, 11 Feb 2003, David Wheeler wrote:
On Monday, February 10, 2003, at 09:06 PM, Curt Russell Crandall wrote:
I reran the install and before I could complete the make step, I
received
the error:
[CurtsiMac:~ccrandal/DBI-1.32] root# make
cc -c -pipe -fno-common -no-cpp-precomp -fno-strict-aliasing -O3
-DVERSION=\1.32\ -DXS_VERSION=\1.32\
-I/Library/Perl/darwin-thread-multi/CORE -Wall -Wno-comment Perl.c
Perl.c: In function `dbixst_bounce_method':
Perl.c:712: internal error: Bus error
Please submit a full bug report,
with preprocessed source if appropriate.
See URL:http://developer.apple.com/bugreporter for instructions.
{standard input}:5901:FATAL:.abort detected. Assembly stopping.
make: *** [Perl.o] Error 1
I have submitted a bug report to Apple and plan on trying a more
current
version of GCC or I may attempt to use GCC 2.95_2 (which OS X should
still
support).
Hrm, this is beyond my knowledge. Tim, any ideas?
As far as the success of my Perl 5.8 build, here are the results:
Failed Test Stat Wstat Total Fail Failed List of
Failed
---
../ext/DB_File/t/db-btree.t010?? ?? % ??
../ext/DB_File/t/db-recno.t 1603 1.88% 61 63 65
../ext/threads/t/libc.t01111 11 100.00% 1-11
(1 subtest UNEXPECTEDLY SUCCEEDED), 29 tests and 426 subtests skipped.
Failed 3/712 test scripts, 99.58% okay. 14/68756 subtests failed,
99.98%
okay.
I'm not concerned with the first two failures since the Berkley DBM
version on OSX is ancient. But, I'm wondering if turning threads on
is a
problem. However, threads in 5.8 *should* work and w/o threading, I
had
more errors compiling (I'll have to retry that).
I haven't tried running 5.8.0 with iThreads in a while. It had worked
for me on 10.1.x a while ago, including with DBI. But I've not tried it
on Jaguar. There's no reason why it shouldn't work, though. You might
want to pop a note to Jarkko about it. He may have already patched the
darwin hints file to make it work properly.
HTH,
David
--
David Wheeler AIM: dwTheory
[EMAIL PROTECTED] ICQ: 15726394
Yahoo!: dew7e
Jabber: [EMAIL PROTECTED]
Kineticode. Setting knowledge in motion.[sm]
Re: Installation problems on OS X 10.2.3
I reran the install and before I could complete the make step, I received
the error:
[CurtsiMac:~ccrandal/DBI-1.32] root# make
cc -c -pipe -fno-common -no-cpp-precomp -fno-strict-aliasing -O3
-DVERSION=\1.32\ -DXS_VERSION=\1.32\
-I/Library/Perl/darwin-thread-multi/CORE -Wall -Wno-comment Perl.c
Perl.c: In function `dbixst_bounce_method':
Perl.c:712: internal error: Bus error
Please submit a full bug report,
with preprocessed source if appropriate.
See URL:http://developer.apple.com/bugreporter for instructions.
{standard input}:5901:FATAL:.abort detected. Assembly stopping.
make: *** [Perl.o] Error 1
I have submitted a bug report to Apple and plan on trying a more current
version of GCC or I may attempt to use GCC 2.95_2 (which OS X should still
support).
As far as the success of my Perl 5.8 build, here are the results:
Failed Test Stat Wstat Total Fail Failed List of Failed
---
../ext/DB_File/t/db-btree.t010?? ?? % ??
../ext/DB_File/t/db-recno.t 1603 1.88% 61 63 65
../ext/threads/t/libc.t01111 11 100.00% 1-11
(1 subtest UNEXPECTEDLY SUCCEEDED), 29 tests and 426 subtests skipped.
Failed 3/712 test scripts, 99.58% okay. 14/68756 subtests failed, 99.98%
okay.
I'm not concerned with the first two failures since the Berkley DBM
version on OSX is ancient. But, I'm wondering if turning threads on is a
problem. However, threads in 5.8 *should* work and w/o threading, I had
more errors compiling (I'll have to retry that).
Thanks,
Curt
On Thu, 6 Feb 2003, David Wheeler wrote:
On Thursday, February 6, 2003, at 11:39 AM,
[EMAIL PROTECTED] wrote:
I tried installing DBI 2 ways, first through CPAN.pm and second by
just downloading the source and compiling. In both instances, I
received an internal bus error.
Send in the output of make test TEST_VERBOSE=1.
David
--
David Wheeler AIM: dwTheory
[EMAIL PROTECTED] ICQ: 15726394
Yahoo!: dew7e
Jabber: [EMAIL PROTECTED]
Kineticode. Setting knowledge in motion.[sm]
Re: Accessing .mdb files residing on a linux box with the DBI
The *.mdb file IS the Access database. I've tried a couple of ways to read it on a UNIX system and nothing worked. Your best bet is to set up a similar scheme on a database that sits on your Linux box (i.e. Postgres, MySQL, etc). On the Windows machine, load Perl (like ActiveState Perl) with DBI, and drivers and client libs for your target DB. Make a Perl script to select * and then as your loop through your result set, insert it into the target DB. If someone has a way to directly read Access databases on a *NIX system, I'd love to hear about it. It's been a while, but I think using DBI Proxy to connect to an Access DB on windows was one way to do it. Even if a method like that worked, I think you'd eventually want to scrap using Access altogether and use a real DB that resides on your Linux box. HTH Curt On Sat, 7 Sep 2002, Moritz von Schweinitz wrote: hi, i have access a database that's stuck (poor thing ;-) inside a .mdb file, and resides on a linux machine via the DBI. the linux-box is a file-server, and the clients access the database via samba (why would anybody do it that way??! i simply don't get it). i'd really, reallt like my program to run on that box. does anybody have an idea how i can accomplish this without having to ask some windows-machine to tell me what's inside that DB? i'd be perfectly happy with read-only access, if that makes a difference. any help is very appreciated, M.
Re: Info about DBI::Sybase.
Install DBD::Sybase and then perldoc DBD::Sybase Also, the Programming the Perl DBI is a good reference book to have. HTH, Curt On Tue, 20 Nov 2001, Mcgregory Pinto wrote: Hy all I´m a beginer. I´m looking for any information about DBI::Sybase. Where i can get it ?? Thanks. Gregory
RE: How to install DBI on AIX; need DBDSybase and DBDOracle to work
Maybe I've missed something... but I'd recommend not using a tarball directly and just use the CPAN module to install it: perl -MCPAN -e install 'DBI' perl -MCPAN -e install 'DBD::Sybase' perl -MCPAN -e install 'DBD::Oracle' assuming you have a standard setup on your box, you can probably just select the defaults for everything and let CPAN.pm do all the dirty work. This is pretty much the same way PPM works with ActiveState. HTH, Curt
Re: Informix error messages
We don't set INFORMIXDIR in the scripts, the system is set up so all accounts (including web, the account that owns all the web content and scripts) have $INFORMIXDIR set to /usr/informix. What is odd is that for the SAME script, it will display $DBI::errstr correctly once, and then the next several times it'll only display the error code. Since it's a fast cgi script, each of the separate invocations of the script should be inheriting the same $INFORMIXDIR environmental var. Thanks for the info... I'll just have to continue digging into this and see if there's a pattern I'm missing. Thanks --Curt
Re: Informix error messages
I don't think this is the problem. There aren't any filehandles that are
opened by the script unless something within DBD::Informix is opening
filehandles.
Since I'm the only one here today, I have the luxury of using the web
server on the test system without someone constantly restarting it. So, I
did a little experiment to see what was going on.
1.) I defined $ENV{'INFORMIXDIR'}='/usr/informix' near the top of the
fcgi script.
2.) In the block that prints error message, also printing
$ENV{'INFORMIXDIR'} to see if it's getting undef'd
3.) Restarted apache
The first time after the restart, I get the right return from $DBI::errstr
and $ENV{'INFORMIXDIR'} is set correctly. However, any subsequent runs of
the script causes $DBI::errstr to only return error codes (and indicate
that the error cannot be found within the error_log), but the
environmental variable is still correctly set. So, I don't think
$ENV{'INFORMIXDIR'} is the problem.
I tried this several times, restarting apache, trying different errors
back to back, etc. Always on the first invocation of the script I get the
correct output, and only error codes after that.
Is DBD::Informix attempting to cache error codes, so it only reads from
that file the first time?
What is CSDK? Since I do not know what it is, I'm not sure what version
it would be.
Thanks
--Curt
On Thu, 16 Aug 2001, Gary Armstrong wrote:
Curt Russell Crandall wrote:
We don't set INFORMIXDIR in the scripts, the system is set up so all
accounts (including web, the account that owns all the web content and
scripts) have $INFORMIXDIR set to /usr/informix.
What is odd is that for the SAME script, it will display $DBI::errstr
correctly once, and then the next several times it'll only display the
error code. Since it's a fast cgi script, each of the separate
invocations of the script should be inheriting the same $INFORMIXDIR
environmental var.
Is it also inheriting open file handles? I wonder if it is being rewound?
Gary
Re: Informix error messages
Forgot this in the last reply... it's the code I'm using to examine this
problem. Basically, it provides a text box for you to enter 1 SQL
statement. If executed successfully, it prints the results otherwise it
prints the error. Ignore the Javascript, it's not completed, but it's
meant to display query result (ONLY) in a separate window... doesn't work
yet, though. Regardless, it's not related to the problem... we see this
in all our fcgi scripts.
As stated previously, $ENV{'INFORMIXDIR'} is always correctly
printed. However, the only time the $DBI::errstr string prints correctly
is the first time after the Apache server is restarted. After the first
time, subsequent executions of this script have $DBI::errstr displaying
only the error codes and indicating in the error_log that there was a
failure to locate the SQL error message.
#!/usr/bin/perl
use strict;
use warnings;
use appbase;
use CGI::Carp qw(fatalsToBrowser);
use CGI::Fast qw(:standard -no_debug);
use DBI;
use Data::Dumper;
use POSIX qw(tmpnam);
$CGI::POST_MAX= 100;
$CGI::DISABLE_UPLOADS = 1;
my %app = get_appbase(); ### module that extracts env info from flat file
$ENV{'INFORMIXSERVER'} = $app{'ifxsvr'}; ### either 'test' or 'prod'
$ENV{'INFORMIXDIR'} = '/usr/informix';
my $dbh = DBI-connect( $app{'dbconn'}, $app{'dbuser'}, $app{'dbpass'} );
$dbh-{RaiseError} = 0;
$dbh-{PrintError} = 1;
$dbh-{AutoCommit} = 0;
my $q = undef;
while ($q = CGI::Fast-new())
{
my $status = '';
my $random_win_name = tmpnam();
$random_win_name =~ s/\/tmp\///;
if ($q-param('go'))
{
$q-print(
style_hdg(
{'title' = SQL Interface to
$dbh-{Name}},
$status,
show_action($random_win_name),
show_result()
)
);
}
else
{
$q-print(
style_hdg(
{'title' = SQL Interface to
$dbh-{Name}},
show_action($random_win_name)
)
);
}
}
##
## Exit
##
END
{
$dbh-disconnect();
undef($dbh);
}
exit(0);
### Subroutines ###
sub show_action
{
my $random_win_name = shift;
my $html = EOF;
SCRIPT LANGUAGE=JavaScript
function results_win(name)
{
var query = document.sql_form.sql.value;
window.open(, name, width=500, height=500, status=yes,
resizable=yes);
return true;
}
/SCRIPT
EOF
$html .= $q-start_form({-name='sql_form'})
. p( 'Enter SQL: ',
font({-face = 'monospace'},
$q-textarea(-name = 'sql',
-rows = 20,
-cols = 60)),
'brNumberbrof Resultsbr',
$q-popup_menu(-name = 'num_results',
-value=['50','100','150','200','300','ALL'],
-default='ALL'),
'nbsp;nbsp;nbsp;',
$q-submit(-value= 'Submit Query',
-name = 'go',
-onSubmit = return
results_win($random_win_name);) )
. $q-end_form
. 'hrbr';
return $html;
}
sub show_result
{
my $html = '';
my $sql = $q-param('sql');
$dbh-do('set isolation to dirty read');
if ($DBI::errstr)
{
return p(['Set Isolation Level failed: ', $DBI::errstr]);
}
my $stmt = $dbh-prepare($sql);
if ($DBI::errstr)
{
return p([INFORMIXDIR: $ENV{'INFORMIXDIR'} Prepare failed: ,
$DBI::errstr]);
}
$stmt-execute();
if ($DBI::errstr)
{
return p(['Execute failed: ', $DBI::errstr]);
}
if ($sql =~ /\s*select/i)
{
my @rows = @{$stmt-fetchall_arrayref()};
if ($DBI::errstr)
{
return p(['fetchall-arrayref failed:', $DBI::errstr]);
}
my $cols = $stmt-{NUM_OF_FIELDS};
my $rows_affected = $stmt-rows();
$html .= EOF;
table border=1 cellpadding=4 cellspacing=0
trtd colspan=$cols align=left
Result: $rows_affected rows found, $cols columns
/td/tr
EOF
$html .= Tr(td({-align = 'center'}, ['ROW',
@{$stmt-{NAME}}]));
my $stop_at = $q-param('num_results');
if ($stop_at eq 'ALL') { $stop_at = $rows_affected; }
my $count = 1;
foreach my $row (@rows)
{
$html .= Tr(td([$count,@{$row}]));
if ($count == int($stop_at)) { last; }
$count++;
}
$html .= /table\n;
}
else
{
my $rows_affected = $stmt-rows();
$dbh-commit();
if ($DBI::errstr)
{
return p(['Commit failed: ', $DBI::errstr]);
}
$html .= p($rows_affected rows
Re: Informix error messages
Thanks for the information. I'm not authorized to rebuild DBD::Informix on these systems nor do I know when or how DBD::Informix was built last time. I'll forward your advice to the senior developer here. It's still a little confusing as to how I'll get the correct errstr the first time, but not after that. If /usr/informix is not the right value for $INFORMIXDIR, I would've thought that I would never get the error message in errstr. Thanks, Curt
Re: Informix error messages
I'm just trying to gather info... I'll let the senior developer worry
about reinstalls and stuff... less for me to worry about. So, I'm not
upset at all about not having authority to reinstall things. Actually,
there's nothing stopping me since I'm root... but I don't want to make the
boss mad.
I'll try doing a dump on %ENV before and after. Althought, just viewing
$ENV{'INFORMIXDIR'}, it looks like that is retaining its value throughout.
Thanks,
Curt
Informix error messages
We've been noticing that often times DBD::Informix is not returning the whole error message generated by the database. For example, I received the following string in response to a char to numeric conversion error: SQL:-1213: However in the Apache error_log, I get: FastCGI: server /apache/test/vip2/cgi/user/a03.fcgi stderr: DBD::Informix::st execute failed: SQL: -1213: Failed to locate SQL error message at ../ip01_ipid_cache.pm line 52. or FastCGI: server /apache/test/vip2/cgi/user/a03.fcgi stderr: DBD::Informix::st execute failed: SQL: -1213: Character to numeric conversion error at ../ip01_ipid_cache.pm line 52. The behavior of what is included in $DBI::errstr does not seem to follow any pattern. Sometimes when I print $DBI::errstr I'll get the error message other times I just get the SQL and/or ISAM error. Is anyone aware of a bug in DBD::Informix that is causing this? It is rather irritating since I often go to the Informix web site to look up the error code, and the web developers decided to dump almost 2MB of text on that page, thus causing my browser to puke often. I'm using Perl 5.6.0, DBI 1.14 and DBD::Informx 1.00.PC1 on AIX 4.3.3. Thanks, Curt
getting return messages from non-selects in Informix
I have a FCGI script that lets you enter an sql statement in a textbox and on submit the statement is submitted to the database. If it is a select statement, a table with the results is displayed. If it is not a select statement, the program simply prints an error that fetch* didn't work. I'd like to make this program more intelligent by displaying the message returned by the database by successful update, delete, insert, etc. statements. I'm using Informix and I'm unable to find an attribute or function that allows me to do this. I believe in Sybase you can just do a fetch and grab a particular piece of the result set that would contain this data (don't have the perldoc for DBD::Sybase in front of me, so I don't know offhand what the syntax would be to do this). Is there's something equivalent I can use under Informix to get this information? Example update informix.vp_ppreq set ts = '2001-07-04 12:00:00' where prt_date = '2001-03-01' No rows found. - this is what I want to get!!! Thank you, Curt Crandall
RE: getting return messages from non-selects in Informix
That's kind of like the workaround I have in now, but it's not the solution I really want... I would like to display verbatim the return message from Informix since there will be some commands issued where looking at the return value for the number of rows updated will be insufficient. Thanks, Curt On Tue, 3 Jul 2001, Sterin, Ilya wrote: Not sure about the attribute, but why not just use $DBI::errstr along with the return value. If an error is return you can display the $DBI::errstr if not, but no rows are returned/updated you can display the No rows found message yourself. Ilya
RE: getting return messages from non-selects in Informix
The perldoc I have does not refer to a sqlda structure, however it briefly talks about a sqlca structure... but I am unable to find the specific information I need. I'll have to track down the Informix manuals and look at this structure in there. Thanks, Curt On Tue, 3 Jul 2001, Wilson, Doug wrote: Look at the perldoc for DBD::Informix, you have access to the sqlda structure. Or the do() and execute() methods return the number of rows affected.
Re: -w does not allow undef for NULLs
You will get this if you use the older DBD::Sybase. I struggled with this
stuff and made ugly workarounds just get stuff undef through a placeholder
with DBD::Sybase 0.21. Either switch to 0.91 or check for an undef value
and do a $dbh-quote() on it... unfortunately, you won't be able to use
placeholders and you'll be stuck using a stored procedure to do a lousy
insert. I HIGHLY recommend you use the latest DBD::Sybase module and at
least DBI 1.14.
HTH,
Curt Crandall
On Thu, 28 Jun 2001, Tim Harsch wrote:
how do you use undef when you mean to without -w barking this at you:
Use of uninitialized value at
/usr/local/perl5.005/lib/site_perl/DBD/Sybase.pm line 121.
code snip:
$hDB-do( SQL );
create table #temp_for_this_session
( fld1 int not null,
fld2 varchar(20) null )
SQL
my @myvalues = (
[ 5, undef ],
);
foreach my $myvalue ( @myvalues ) {
my $sql = 'insert into #temp values ( ?, ? )';
$hDB-do( $sql, {}, @$myvalue );
} # end foreach
__END__
I'm supposed to use undef to indicate NULL in the database right? Should
Sybase.pm be checking for undef before trying to use the value?
Sybase problem
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
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
disconnect from an active statement handle
I'm getting the error:
DBI::db=HASH(0xf0130)-disconnect invalidates 1 active statement handle
(either destroy statement handles or call finish on them before
disconnecting)...
What's interesting is the dbh handle has AutoCommit enabled and the lone
sth handle on that dbh is an insert... so I do not believe calling
finish() would do anything for me here.
In my object, the method _prepare_sql() is called by the object
constructor. In that method, I create both the dbh and sth and assign
them to an object attribute:
sub _prepare_sql
{
my $self = shift;
my $dbconn = dbi:Sybase:server=$self-{server};
my %attrs = (RaiseError=0, PrintError=1, ChopBlanks=1);
$self-{dbh_fa_insert} = DBI-connect($dbconn, $self-{user},
$self-{passwd}, \%attrs);
$self-{dbh_fa_insert}-{AutoCommit} = 1;
.
$self-{fa_ins} = $self-{dbh_fa_insert}-prepare( EOF
INSERT INTO $self-{database}..sa_fund_alias
(source_id, fund_alias_name)
values (?, ?)
EOF
);
return(0);
}
In another method, I open an input file and loop through each line. As
part of the datamapping, the above insert statement may be called:
sub process_file
{
my $self = shift;
...
while (defined(my $record = IN))
{
...
if (...something...)
{
$self-{fa_ins}-execute($source_id, $par_fund_group);
return($DBI::errstr) if ($DBI::err);
}
...
}
$self-{dbh_fa_ins}-disconnect();
return($rc);
}
In all, I have 4 separate dbh handles that are alive and 3 of them are
used only to handle a single insert statement. 2 of the 3 have AutoCommit
enabled. This handle, however, is the only one that complains about there
being an active statement handle at the time of disconnect. If AutoCommit
is enabled and there's only the one insert statement, how could I be
getting this error.
I'm using Perl 5.00404, DBI 1.14 DBD::Sybase 0.91 on Solaris 2.6.
Thank you,
Curt Crandall
memory leak
I'm having a problem with my program having its memory usage go out of
control. I'm working on Solairs 2.5.1 and 2.6 with several Perl builds:
Perl 5.00404 w/ DBI 1.13 DBD::Sybase 0.21
Perl 5.00404 w/ DBI 1.14 DBD::Sybase 0.91
Perl 5.6.0 w/ DBI 1.14 DBD::Sybase 0.91
My program is somewhat OO in design. I loop through a set of files and
instantiate an object for each. In that object, I instantiate 2 more
objects that hold the input and output data structures. These structures
are created using Class::Struct and I'm using the array form.
For each file, I loop through each line parsing the contents and moving
the contents into the input structure. I then map that to the output
structure.
For some of the files, I may need to insert a record into a database table
if a parameter comes up missing. For these files, the memory usage grows
larger with each line.
Since Sybase can't have a database handle with multiple statement handles
with AutoCommit off, I'm creating 2 database handles. The one with
AutoCommit off is used for selects and for the insert mentioned
above. The other handle is used to insert the output structure into the
database... I'm caching every 100 transactions between commits.
my %attrs = (RaiseError=0, PrintError=1, AutoCommit=1, ChopBlanks=1);
my $dbconn = dbi:Sybase:server=$env{DB_SERVER};
my $dbh1 = DBI-connect($dbconn, $env{DB_USER}, $env{DB_PASSWD}, \%attrs);
$attrs{ChopBlanks} = 0;
my $dbh2 = DBI-connect($dbconn, $env{DB_USER}, $env{DB_PASSWD}, \%attrs);
These 2 database handles are stuffed into attributes of the main object:
$self-{dbh} and $self-{dbh_ins}, respectively.
Now I prepare the statement and place that in an object attribute:
$self-{fa_ins} = $self-{dbh}-prepare( EOF
INSERT INTO blah
(foo, bar)
values (?, ?)
EOF;
In the piece of the program that does the processing:
sub process_file
{
my $self = shift; ### main object
open(IN, $self-{file});
while (defined(my $record = IN))
{
my @data = split(\t, $record);
$self-{inObj}-init(@data); ### Populate sub-object
$self-{inObj}-val();### validate sub-object
$self-{inObj}-map();### map input to output
$self-write(); ### write to database
}
}
The problem happens in the method map:
if ((!defined($master-{outObj}-cusip))
(!defined($href-{alias_name})))
{
$master-{fa_ins}-execute($master-{source_id}, $self-fund_name);
return($DBI::errstr) if ($DBI::err);
}
where $master refers to the main object in this method.
This chunk of code should conditionally insert a line into a db table and
immediately commit since the AutoCommit is enabled. The correct actions
are taking place, however, each time a line is mapped and this command
executed, the amount of memory and, to a lesser degree, cpu resources are
consumed in increasing amounts. By the time I hit about 17000 lines, my
memory usage is around 2GB.
Can anyone guess as to what I am doing wrong, or is this possibly a quirk
with the Sybase driver?
Thanks,
Curt Crandall
Re: memory leak
So, should I have a separate DB handle for each statement that does something other than selects? In the instances where the program does not prepare this statement handle, there are still 8 or 9 other statements on a single DB handle all of which do nothing but select queries. The memory usage remains stable at 0.9% (out of 2.5GB) throughout the entire run. Thanks for the help, Curt
Re: DBD::Sybase - (Not) Chopping Blanks
I personally have not seen blanks come back with any of my columns unless
someone accidentally entered a whitespace character in a varchar
field. ChopBlanks=1 should remove all trailing whitespace from CHAR
fields only. If you want trailing whitespace removed from VARCHAR or
other datatypes returned, use a regex to strip it.
Based off a Perl Cookbook recipe I learned when I first started
programming Perl, you can create a very simple trim() routine that'll
remove leading/trailing whitespace:
sub trim
{
my @out = @_;
for (@out)
{
s/^\s+//; ### Remove leading whitespace
s/\s+$//; ### Remove trailing whitespace
}
return wantarray ? @out : $out[0];
}
So if you just want to pass one value or an array of values to trim(),
it'll get rid of that pesky whitespace that no one wants.
If you want to then pad the data with whitespace for fixed formatted
output, just use sprintf and supply the formatting you want.
HTH
--Curt
On Mon, 18 Jun 2001, [iso-8859-1] Klaus Dittrich wrote:
Hello all,
I just startet using DBD::Sybase (after experimenting with
DBD::Oracle and DBD::ODBC), and I found some effects I don't
know how to handle.
1. Is it possible to avoid the additional Blank after each
column I select (it does not appear always, but in most cases)?
I know it can be avoided in isql, but how is it done in DBD::
Sybase (without losing the blanks I need for a fixed format
output, i.e. ChopBlanks does not help) ?
2. In some obscure cases, all Blanks are chopped when I add
an Order-By-Clause to an Select, though I explicitely cast
the columns to a fixed length. Without Order-By the Blanks
are not chopped and I get a Fixed-Length-Output.
Can this behaviour be changed (or can someone explain it to me)?
I'm using Perl 5.005_03 and DBD::Sybase 0.22.
Thanks in advance.
Klaus Dittrich
Mannesmann Mobilfunk GmbH
Duesseldorf, Germany
[EMAIL PROTECTED]
Der clevere Infobote - Shopping-Tipps bequem abonniert. Jetzt neu bei
http://www.epost.de
Re: Anyone else experiencing problems with the dbi-* mailing lists?
I have been having problems posting. The mail often bounces back to me, yet I can receive mail from the list and can mail individuals when replying to a post. The return message was saying that my account wasn't be recognized, or something, by the mailing list server. I also received a message from the mailing list about losing my account because they thought my email was invalid (or something like that). It has since cleared up. --Curt
Re: getting a list of databases
I didn't have an Informix DB available at the time, but yes it does return a list of dbs with data_sources... however, Sybase does not as well as many others. On Thu, 7 Jun 2001, Jonathan Leffler wrote: Curt Russell Crandall wrote: Is the DBI-data_sources method the only facility available in the DBI to get a list of databases on a server? I'm trying to build a Perl/Tk application that would give me an interface similar to say RapidSQL where I can have a frame containing a list of all the databases on a server... by clicking one, I can enter that database and issue commands and stuff. Sybase won't return anything with data_sources and I'm not sure if Informix will either... those are the 2 dbs I'm concerned with at this moment. I know in Sybase I could just query the master..sysdatabases table to get this list, but I was hoping there'd be a more portable method available in the DBI especially since I don't know what command to issue in Informix to get a similar list. Have you tried using data_sources with DBD::Informix? If you don't have it, you must have a really, really, really ancient version of DBD::Informix and you are three to four years overdue for an upgrade. Check the test -- t/t??dblist.t IIRC. And/or the documentation? -- Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED]) Guardian of DBD::Informix 1.00.PC1 -- see http://www.cpan.org/ #include disclaimer.h
Re: I want to unsuscribe
As my inclusion of the previous message implies I prefer to have not have messages trimmed on occassion... many times the thread goes over several days and either I don't want to search for the original message, or have already deleted the original message, or don't have access to the archives and old messages when I'm on the road and downloading mail on my PDA. I agree that messages that have 10 replies tacked on to the end of them are kind of ridiculous, but I presonally don't have a problem with a message containing the past one or two messages. --Curt On Wed, 6 Jun 2001, Ronald J Kimball wrote: On Wed, Jun 06, 2001 at 02:57:05PM -0600, Sterin, Ilya wrote: Well depends. Sometimes you don't want them trimmed, so if someone comes in in the middle of the thread, they don't have to read each and every message, but rather the last one. That's ridiculous. If someone comes in in the middle of the thread, they should check the archives for past messages. You're putting a burden on actual people that are already on the mailing list to accomodate some hypothetical person who might subscribe in the middle of a discussion. Ronald
Re: I need structure help!!!
I have been using Class::Struct a lot recently and instantiating an array object with it. You can also use a hash if you wish. This type of object gives you a C-like structure that you can manipulate as either an array (hash) or as a regular object using automatically generated accessor/mutator functions. When defining the structure, you set up a call to struct and list the elements (keys) you want in your object. For each DB table, create a class whose attributes (array elements/hash keys, whatever you want to call it) match the columns of your table. Then just instantiate the appropriate class and when doing a fetch, feed the results to your object. Take a look at the perldoc for Class::Struct... it might do the trick for you. HTH, Curt On Mon, 4 Jun 2001, Mohammad Shoja wrote: Hi folks, I am using dbi with mysql to develope a data access interface for my application, and also I am using hash to retriving rows from database using fetchrow_hashref function, everything is fine, But the problem is I have to redefine a hash for every table which I am working with each time and in each function. how can I bypass this, just like using structure in C. to defining it once and using it as a data type. I know that I can implement it in Object oriented perl but How can I handle it in structured perl-dbi programming. Any help and suggestion appriciate. Thank you all in advance. regards -- Mohammadreza Shojatalab European Bioinformatics InstituteTel: +44 (0)1223 494 669 EMBL Outstation Fax: +44 (0)1223 494 468 Wellcome Trust Genome Campus E-Mail: [EMAIL PROTECTED] Hinxton, Cambridge URL: http://www.ebi.ac.uk/~shoja CB10 1SD, UK
getting a list of databases
Is the DBI-data_sources method the only facility available in the DBI to get a list of databases on a server? I'm trying to build a Perl/Tk application that would give me an interface similar to say RapidSQL where I can have a frame containing a list of all the databases on a server... by clicking one, I can enter that database and issue commands and stuff. Sybase won't return anything with data_sources and I'm not sure if Informix will either... those are the 2 dbs I'm concerned with at this moment. I know in Sybase I could just query the master..sysdatabases table to get this list, but I was hoping there'd be a more portable method available in the DBI especially since I don't know what command to issue in Informix to get a similar list. Thank you, Curt Crandall
Re: fetchrow_hashref
Instead of fetching again, setup a hash of hashes and populate it on the
first fetch... then it's right there for you the second time
around... depending on the amount of data and how many times you need to
use the same data set, it could save you more time than reexecuting and
refetching.
--Curt
On Mon, 14 May 2001, [iso-8859-1] Jonas Askås wrote:
Hi,
Sorry to bother you with an (maybe) easy question, but I don't now how to
do...
I want to to this:
$SQL = SELECT * FROM members;
my $sth = $dbh-prepare($SQL);
$sth-execute;
my $record_hash;
while ($record_hash = $sth-fetchrow_hashref){
print $record_hash-{first_name} $record_hash-{last_name}\n;
}
Then I want to search the $sth again, and maybe print some other
information, like this:
while ($record_hash = $sth-fetchrow_hashref){
print $record_hash-{last_name} $record_hash-{company}
$record_hash-{e-mail}\n;
}
My question is:
After I've search $sth the first time using the fetchrow_hashref-method, how
do I point $sth to the first position in the array again, so I can serach it
again?
Regards,
Jonas Askås
RE: running php script from withing file to get variables
2) documentation (saves me explaining PHP AND Perl:DBI in the emergency documentation should I keel over and die tomorrow and take the Perl:DBI knowledge with me. like it or not, not many do the Perl thing any more) Huh? Are you just talking about at your own shop... I know very few people who write PHP any longer and far many more who still do the Perl thing, personally. I totally agree with Brett, I would just write it all in Perl and forget about mixing the 2 languages... unless of course someone over you dictates something else.
dbish questions
There's a couple of things I'm having a proble with when trying to use dbish. First, I want to turn autocommit off. So, according to the perldoc on DBI::Shell, I'm supposed to type /option autocommit=0, but when I do I get the message: Unknown or ambiguous option name 'autocommit' I do see an init_autocommit, but I'm unsure if that does what I think it would since it is not documented in the Perldoc. Second, I was trying to specify the database in the driver... dbi:Sybase:server=;database= username passwd This doesn't appear to work, however, dbi:Sybase:server= does work. Is this a bug or is this intentional? It's asking for a DSN and according to the documentation on DBD::Sybase, I am in both cases feeding it a valid DSN. Thank you, Curt Crandall
RE: dbish questions
I don't think that'll work with dbish. Thanks anyway.
On Wed, 9 May 2001, Jones Robert Contr 81 CS/SCK wrote:
Try ...
$dbh-{AutoCommit} = 0;
-Original Message-
From: Curt Russell Crandall [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 09, 2001 1:57 PM
To: [EMAIL PROTECTED]
Subject: dbish questions
There's a couple of things I'm having a proble with when trying to use
dbish. First, I want to turn autocommit off. So, according to the
perldoc on DBI::Shell, I'm supposed to type /option autocommit=0, but when
I do I get the message:
Unknown or ambiguous option name 'autocommit'
I do see an init_autocommit, but I'm unsure if that does what I think it
would since it is not documented in the Perldoc.
Second, I was trying to specify the database in the driver...
dbi:Sybase:server=;database= username passwd
This doesn't appear to work, however, dbi:Sybase:server= does
work. Is this a bug or is this intentional? It's asking for a DSN and
according to the documentation on DBD::Sybase, I am in both cases feeding
it a valid DSN.
Thank you,
Curt Crandall
Re: Use of Null
Yes, I totally forgot about that... seems like you just mentioned that to me not too long ago. I guess I forgot since I can't use placeholders in my code because Sybase won't allow it with stored procs. Sorry 'bout that. On Tue, 8 May 2001, Ronald J Kimball wrote: On Tue, May 08, 2001 at 03:29:30PM -0400, Sherry Graham wrote: I commented out the binds. So I just have: $patient_title = $dbh-quote($patient_title); print HERE $patient_title\n; $sth-execute($first_name, $last_name, $middle_initial, $patient_title, $person_id); By the way, the print types HERE NULL. Either call quote() OR use a placeholder. Don't do both for the same value. Ronald
sanity check
I'm royally confused right now. This is something I've looked at on and
off but never followed through with and eventually would always forget
about.
I have several objects that have been created using Class::Struct. The
structure is filled by splitting a tab delimited record and feeding that
to the structure array:
@{$struct} = split(\t, $record);
The structure is scrubbed so leading/trailing whitespace, soft quotes and
dollar signs are all removed. Next, the structure array is run through a
loop to turn NULL fields (i.e. '', ' ', ' ', undef) into SQL NULLs:
for (my $i = 0; $i = @#{$struct}; $i++;)
{
if ((!DBI::looks_like_number(${$struct}[$i]) (!${$struct}[$i]))
{
${$struct}[$i] = undef;
${$struct}[$i] = $dbh-quote(${$struct}[$i]);
}
}
Next, the structure array is mapped to another structure array that
matches the columns of a db table I'm populating. If there's nothing to
map to one of the columns, I was doing this:
$outObj-param1(NULL);
where $outObj is the output structure and $outObj-param1() is its mutator
method.
The output structure is then scrubbed like the input structure was and
null/empty fields converted to SQL nulls. The the output structure array
is passed as an argument list to an insert statement:
my $cmd = sprintf(exec sales2..sa_ext_tran_dly %s%s%s., @$outObj);
my $rc = $dbh-do($cmd);
Now, what has me scratching my head is why when I define an input argument
to this stored proc as NULL the string, it is showing up in the database
as [NULL]... this is what I want, but I'm wondering why it is working... I
would think that the database would've interpreted it as the string NULL.
Obviously I could make sure that I'm getting an SQL NULL by scrubbing
NULL values and using quote(undef) as the value. But now I'm concerned
that I might have made 2 mistakes that are cancelling each other out.
I realize that this could be a rather confusing post, if there's something
that I'm not getting across clearly, please let me know.
BTW: I'm using Perl 5.00404, DBI 1.13 and DBD::Sybase 0.21 on Solaris 2.6
with Sybase 11.9.2.
Thank you,
Curt
bind_param and dates
I'm getting conversion errors (VARCHAR to [SMALL]DATETIME) on an
insert. I have a structure that contains a list of params that need to be
inserted into a db table. Prior to insertion, I make sure that undef
values are treated as SQL NULLs:
for (my $i = 0; $i = $#array; $i++)
{
if ((!DBI::looks_like_number($array[$i]) (!$array[$i]))
{
$array[$i] = $dbh-quote($array[$i];
}
}
Then I go through and use bind_param on all the array members, specifying
each params SQL data type:
$sth-bind_param(1, \$array[0], SQL_DATE);
I have 4 date values that are inserted into the db... the corresponding db
types are SMALLDATETIME, DATETIME, DATETIME, DATETIME. The values I'm
inserting are (NULL, NULL, 20010429 14:01:35, 20010429 14:10:22). I can
see where NULL may get misinterpreted, but why would the last two values
be misinterpreted as VARCHAR... in fact, it was my understanding that
specifying SQL_DATE would automatically format these values properly.
Prior to doing this, I used a stored proc to insert values like
NULL... the sp was able to tell that NULL meant SQL_NULL and not
NULL. However, Sybase won't let me bind values to a stored proc call
(those significantly slowing the application since I have to prepare each
time in the loop) and the version of Perl I am using will not let me pass
undef to an SQL statement w/o causing a SEGV signal and a core dump. I
cannot change the Perl setup I have so I have to code around them.
Thanks,
Curt Crandall
Re: bind_param and dates
Correction, those dates I was sending are OK I mistakenly sent the reference to them instead of the value. However, the value NULL still doesn't work. In one case, I'm getting the value straight from an SQL query... so it's actually undef... then I quote() it which is supposed to properly convert it to a SQL NULL... yet I still get the conversion error. --Curt
Sybase time comparisons
I need to insert and then update an entry in a table using 2 stored procs
that I am not authorized to alter. The first stored proc checks to see if
I can insert a row and if so it returns the row:
$cmd = 'exec sp_1 ...';
$sth-prepare($cmd);
$sth-execute();
while (@row = $sth-fetchrow_array())
{
.
$date = $row[2];
}
Later, I call another stored proc and pass date to it.
$cmd = exec sp_2 ..., $date ...;
$sth-prepare($cmd);
$sth-execute();
In sp_2, the date is used in the 'where' clause of the update
statement. However, it will never find a row to update because in this
case:
'Apr 27 2001 11:56AM' != 'Apr 27 2001 11:56AM'
The date returned by sp_1 loses its resolution beyond the minutes. So,
while the db may return a date format in the 'C' locale, that's apparently
not what the db uses in the comparison. Is there anything I can do in my
code to force the stored proc to return the full date down to the 300th of
a sec? The date is generated in sp_1 using the getdate() function.
Thank you,
Curt Crandall
Re: Sybase time comparisons
Sorry, I reread Cheetah and DBD::Sybase perldoc and found the
_date_fmt() method. That should
work for me. Thanks anyway.
On Fri, 27 Apr 2001, Curt Russell Crandall wrote:
I need to insert and then update an entry in a table using 2 stored procs
that I am not authorized to alter. The first stored proc checks to see if
I can insert a row and if so it returns the row:
$cmd = 'exec sp_1 ...';
$sth-prepare($cmd);
$sth-execute();
while (@row = $sth-fetchrow_array())
{
.
$date = $row[2];
}
Later, I call another stored proc and pass date to it.
$cmd = exec sp_2 ..., $date ...;
$sth-prepare($cmd);
$sth-execute();
In sp_2, the date is used in the 'where' clause of the update
statement. However, it will never find a row to update because in this
case:
'Apr 27 2001 11:56AM' != 'Apr 27 2001 11:56AM'
The date returned by sp_1 loses its resolution beyond the minutes. So,
while the db may return a date format in the 'C' locale, that's apparently
not what the db uses in the comparison. Is there anything I can do in my
code to force the stored proc to return the full date down to the 300th of
a sec? The date is generated in sp_1 using the getdate() function.
Thank you,
Curt Crandall
problem retrieving sp results in Sybase
I'm rewriting a piece of C code that uses the OpenClient libraries to set
a row in a process table under certain circumstances. The chunk of Perl
code that handles this is:
package init_process;
...
my $cmd = exec sales2..sa_initprocess , $dbh-quote($item_src) . ,
. $proc_id;
my $sth = $dbh-prepare($cmd) or warn(...);
$sth-execute() or warn(...);
my $numrows = 0;
do
{
while (my @row = $sth-fetchrow_array())
{
print join(::, @row), \n; ### line 45
($a, $b, $c, $d, $e, $f, $g, $h, $i) = @row;
}
} while ($sth-{syb_more_results});
$sth-finish();
print numrows: $numrows\n;
The output of this is:
Use of uninitialized value at init_process.pm line 45
Use of uninitialized value at init_process.pm line 45
/tmp/xxx.tmp::EXTtranldr::Apr 25 2001 12:26PM1::1::Apr 25 2001
12:00AM::0::0::0::0::0::0::0::8
0
numrows: 2
If I remove the recommended do { } while ($sth-{syb_more_results}) I get:
Use of uninitialized value at init_process.pm line 45
Use of uninitialized value at init_process.pl line 45
/tmp/xxx.tmp::EXTtranldr::Apr 25 2001 12:26PM1::1::Apr 25 2001
12:00AM::0::0::0::0::0::0::0::8
numrows: 1
The second set of results is closer to what I expect, however I don't
understand why Perl thinks @row is an uninit value when it properly gets
the info out of @row.
I ran this in debug mode, and the whole thing seems to execute out of
order.
$sth-finish();
print join(::, @row), \n;
$numrows++;
($a,...$i) = @row;
while (my @row = $sth-fetchrow_array())
{
$sth-finish();
and just after the print on @row occurrs, a x @row yields:
empty array
So the questions are:
1.) Why is the recommended way of retrieving stored proc results screwing
this up?
2.) Why is @row uninitialized when I can print its values just fine
3.) Why is the order of execution under the debugger causing the finish to
be called first, etc.
Running with the do {} while() makes the order of execution under -d even
more disordered.
Thanks,
Curt
Re: problem retrieving sp results in Sybase
Totally missed that first one. I guess I'll have to wrap it in a block
and set $^W = 0. I'll give the {syb_do_proc_status} =1 a try.
Thank you
You have two entries in @row that are undef (i.e. - they are NULL in
the result set).
/tmp/xxx.tmp::EXTtranldr::Apr 25 2001 12:26PM1::1::Apr 25 2001
12:00AM::0::0::0::0::0::0::0::8
0
numrows: 2
The second row (actually the second result set) is the status result
from the stored proc.
If you are not interested in this value you can set
$dbh-{syb_do_proc_status} = 1, which should cause $sth-execute() to
fetch this result value for you and return a failure if it is not 0.
Michael
--
Michael Peppler - Data Migrations Inc. - [EMAIL PROTECTED]
http://www.mbay.net/~mpeppler - [EMAIL PROTECTED]
International Sybase User Group - http://www.isug.com
Sybase on Linux mailing list: [EMAIL PROTECTED]
Re: Problem using DBI in Object Oriented Package
Don't use BEGIN or END as subroutine names since they have special meaning
in Perl. BEGIN blocks are executed at compile time while END blocks are
always executed at program termination (unless a kill signal or something
similar is sent by the OS). Try using a different name for your
subroutines... it might have something to do with that since I can't see
anything glaring at me from the code snippet you have (of course it's 7a
and I'm not thinking very clearly yet).
--Curt
On Tue, 10 Apr 2001, [iso-8859-1] Tom Robinson wrote:
Hi,
I'm trying to use the DBI within a package I'm
writing, and am getting an odd error (warning). What
is even more odd is that everything actually works
fine.
Here is what I am doing in the package:
package Package;
# class data
my $Dbh;
sub BEGIN {
$Dbh = DBI-connect("DBI:mysql:database=mydb",
{PrintError = 1, RaiseError
= 0})
or croak "Cannot connect to mysql database:
$DBI::errstr\n";
}
sub END {
$Dbh-disconnect()
or croak "could not disconnect from database:
$DBI::errstr";
}
In between I run a routine which just dumps a list of
tables in the database, so my script looks something
like:
use Package;
Package-dumptables();
And I get a printout of the tables, which means it
must be connecting ok. I don't get the croak message
on the $Dbh-disconnect(), so that must also be
working. But I am getting:
Useless use of private variable in void context at
Package.pm line 56.
line 56 being the one that just contains:
$Dbh-disconnect()
so it must be saying $Dbh is being used in a void
context. But if that were the case, surely the
disconnect method call would fail with cannot call
method on undefined object?
Any help appreciated.
Tom
Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
or your free @yahoo.ie address at http://mail.yahoo.ie
RE: Help with installing DBD::Oracle
What distribution of Perl are you using. If it is not ActiveState, then most likely the correct way to install your module would be to use the CPAN module. You can do a perldoc on CPAN for instructions. --Curt On Mon, 9 Apr 2001, Reuss, Bob wrote: Ok, I followd the docs, but ran into a new problem. We are using a product 'Interwoven' that installs a version of perl that I was told was using ActiveState Perl. After following the docs, I realized that this is not true. Now the question would be, how do I install the DBD::Oracle on a Windows 2000 system not running ActiveState Perl? Thanks, Bob Reuss -Original Message- From: Michael A. Chase [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 2:47 PM To: Reuss, Bob; [EMAIL PROTECTED] Subject:Re: Help with installing DBD::Oracle 'Just move the modules' is not the instruction for installing modules in ActiveState Perl. Look up PPM in the fine Online Documentation that came with ActiveState Perl and follow the instructions. The usual 'perl Makefile.pl ...' process doesn't normally work with ActiveState Perl, but ActiveState has a large library of pre-built modules ready for you to use. Don't forget to install DBI before DBD::Oracle or DBD::Oracle8 (depending on the version of Oracle client software you have in your system). -- Mac :}) ** I normally forward private database questions to the DBI mail lists. ** Give a hobbit a fish and he'll eat fish for a day. Give a hobbit a ring and he'll eat fish for an age. - Original Message - From: "Reuss, Bob" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, April 09, 2001 12:12 PM Subject: Help with installing DBD::Oracle I am trying to install the DBD::Oracle module on my Windows 2000 system. I have downloaded the module and have extracted the files via winzip. Here is where I run into problems. If I follow the installation instructions for active state and just move the modules, it doesn't work. If I run the makefile.pl file, I get multiple library errors, but, it finds all the oracle paths correctly, and the DBI paths, etc... The problem then is I need to run make on the makefile. I do not have a make utility installed on the windows 2000 server. I tried to install a few I found on the web but they did not work. Can anyone help me with solutions on how to install the module?
Re: Binding to Output Parameters using DBD::ODBC
Can't do it... the Sybase driver does not allow placeholders in a prepare
of a stored proc. I spent the better part of a day trying to figure out
why my code wasn't working when doing something similar until I read
Cheetah's section on DBD::Sybase.
--Curt
On Mon, 9 Apr 2001, Bruce Hodge wrote:
I am using DBD::ODBC drivers to connect to a Sybase 11.5 database. I have
migrated a bunch of procedures from Oracle 8i that use output parameters to
return data. I was migrating an abstract database interface that I built
dbObject.pm, which previously called the Oracle stored procedures. I
verified that the ODBC prepare statement was being correctly built {call
get_seq(?,?,?)} and when I called DBI::bind_param_inout on the 3rd output
parameter I got the error message "Can't bind output values (currently)".
Now I know I am not the only one who has ever tried this so can someone
please tell me how to bind Perl variables to stored procedure's output
parameters using DBD::ODBC.
Thanks,
Bruce
```
(o o)
--oOO--(_)--OOo
Bruce HodgeDynaScript Internet Language
P.O. Box 428 http://www.dynascript.com
Greenfield, NY 12833 [EMAIL PROTECTED]
-o00-00o---
Re: Binding to Output Parameters using DBD::ODBC
Ooops, I thought you were using the DBD::Sybase driver. Sorry.
On Mon, 9 Apr 2001, Bruce Hodge wrote:
I am using DBD::ODBC drivers to connect to a Sybase 11.5 database. I have
migrated a bunch of procedures from Oracle 8i that use output parameters to
return data. I was migrating an abstract database interface that I built
dbObject.pm, which previously called the Oracle stored procedures. I
verified that the ODBC prepare statement was being correctly built {call
get_seq(?,?,?)} and when I called DBI::bind_param_inout on the 3rd output
parameter I got the error message "Can't bind output values (currently)".
Now I know I am not the only one who has ever tried this so can someone
please tell me how to bind Perl variables to stored procedure's output
parameters using DBD::ODBC.
Thanks,
Bruce
```
(o o)
--oOO--(_)--OOo
Bruce HodgeDynaScript Internet Language
P.O. Box 428 http://www.dynascript.com
Greenfield, NY 12833 [EMAIL PROTECTED]
-o00-00o---
Re: DBD::Sybase with freetds -- no statement executing???
Yes, you can set LD_LIBRARY_PATH within the script and it should pick
up the correct path. It is standard with
all of the DBI scripts accessing Sybase in our shop... normally the path
is in an ini file, the ini file is read and the ENV variable set. We do
this since the same account may be executing the same set of scripts but
from different environments.
As far as the code that is attempting to connect to the DB (where it was
failing on $sth-execute())... I couldn't see anything outwardly wrong
with it. Since there were problems installing FreeTDS, I suspect the
problem starts there.
--Good Luck
Curt
On Fri, 6 Apr 2001, Alexander Farber (EED) wrote:
fukushi wrote:
#! /good_directory_for_perl/perl -w
# fttest -- access MS-SQL7 using freetds
use strict;
use DBI;
$ENV{SYBASE} = "/good_direcroty_for_freetds/freetds";
$ENV{LD_LIBRARY_PATH} = $ENV{SYBASE}."/lib";
I believe, that you can't set the LD_LIBRARY_PATH variable this way
(just from within of you Perl-script and without any forking), so you
should set this variable in the UNIX shell (or in httpd.conf of Apache).
I haven't used freetds, so I can't comment on your other problems...
Re: Script Hangs when executing ...
If Perl DBI is saying it can't find disconnect, then I would think either
$dbh somehow gets undef'd or maybe your disconnect method is
missing. I've run into this before where my disconnect method was
"missing". It may be you just need to reinstall DBI, which is what my
sysadmin did. Why disconnect was missing... who knows??? It may also be
that we needed to reinstall the DBD::Sybase driver and possibly the db
client libraries... it was a while ago and I do not remember.
Is that the WHOLE script or just the section that deals with the db?
On Fri, 6 Apr 2001, Jamie Orzechowski wrote:
I tried sthX-finish and I get the same hangs ...
I am split the script into seperate scripts and I run each part manually ...
Even with the following script I get a hang ... if I insert sth-finish;
then the script will hang ... If I remove it then script will run but I get
the error
DBI::db=HASH(0x80efd18) trace level set to 9 in DBI 1.14-nothread
Can't locate object method "disconnect" via package "DBI::st" at ./test line
34.
- DESTROY for DBD::Sybase::db (DBI::db=HASH(0x80efd18)~INNER)
syb_db_disconnect() - ct_close()
- DESTROY= undef during global destruction.
#-
#script
$dbh = DBI-connect("dbi:Sybase:database=xx", "x", "");
$sql = _ENDSQL;
Exec Subscribe_InsertPlan 13200,136,6,11,"$today"
_ENDSQL
$sth = $dbh-prepare($sql);
$sth-execute || warn $sth-errstr;
$dbh-trace(9);
$dbh-disconnect;
- Original Message -
From: "Hans Foght" [EMAIL PROTECTED]
To: "'Thomas A. Lowery'" [EMAIL PROTECTED]; "Jamie Orzechowski"
[EMAIL PROTECTED]
Cc: "DBI-Users" [EMAIL PROTECTED]
Sent: Friday, April 06, 2001 1:22 PM
Subject: RE: Script Hangs when executing ...
try $sthX-finish after execute when you do not need the $sthX anymore.
-Original Message-
From: Thomas A. Lowery [mailto:[EMAIL PROTECTED]]
Sent: 6. april 2001 19:25
To: Jamie Orzechowski
Cc: DBI-Users
Subject: Re: Script Hangs when executing ...
Still just hangs after the last destory? (Also, keep the dbi-users
mail list in the replies, the Sybase gurus are there.)
Tom
On Fri, Apr 06, 2001 at 01:03:52PM -0400, Jamie Orzechowski wrote:
with tace 9 I get
DBI::db=HASH(0x80efd9c) trace level set to 9 in DBI 1.14-nothread
- disconnect for DBD::Sybase::db (DBI::db=HASH(0x80efdc0)~0x80efd9c)
DBI::db=HASH(0x80efd9c)-disconnect invalidates 3 active statement
handles
(either destroy statement handles or call finish on them before
disconnecting) at ./addplan line 107.
syb_db_disconnect() - ct_close()
- disconnect= 1 at ./addplan line 107.
- DESTROY for DBD::Sybase::db (DBI::db=HASH(0x80efd9c)~INNER)
- DESTROY= undef during global destruction.
any ideas??
- Original Message -
From: "Thomas A. Lowery" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, April 06, 2001 12:55 PM
Subject: Re: Script Hangs when executing ...
What does trace say? ($dbh-trace(1-9) just before the disconnect).
Tom
On Fri, Apr 06, 2001 at 12:25:51PM -0400, Jamie Orzechowski wrote:
Hello ... I have a script which connected to a MS SQL database ...
it
hangs
right at the end of the script ... if I check my database it DOES
insert
all
the information correctly ... it just hangs and never exits ... any
ideas??
... snip
$sth6 = $dbh-prepare($exec6);
$sth6-execute || warn $sth6-errstr;
$dbh-trace(3);
$dbh-disconnect || print "$DBI::errstr $DBI::err\n";
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Re: Script Hangs when executing ...
It doesn't appear that this should happen, though, since $dbh is the database handle ($dbh = DBI-connect()) and disconnect is called by $dbh-disconnect. From the code given, the DBI::st should not be complaining about this. This is an error similar to what I received a while ago using Sybase and Perl 5.005_03/DBI 1.13... it was corrected by reinstalling at least DBI. If this is only a problem when calling a stored proc, then I'm lost. I know DBD::Sybase won't deal with calls to sp's with placeholders... this doesn't appear to be the problem here. On Fri, 6 Apr 2001, Ronald J Kimball wrote: On Fri, Apr 06, 2001 at 02:26:31PM -0400, Curt Russell Crandall wrote: If Perl DBI is saying it can't find disconnect, then I would think either Perl is saying that it can't find the disconnect method in the DBI::st package. That's the statement handle package. The disconnect method is in the DBI package. The solution is to call disconnect on the database handle object rather than a statement handle object. $dbh somehow gets undef'd or maybe your disconnect method is missing. I've run into this before where my disconnect method was "missing". It may be you just need to reinstall DBI, which is what my sysadmin did. Why disconnect was missing... who knows??? It may also be that we needed to reinstall the DBD::Sybase driver and possibly the db client libraries... it was a while ago and I do not remember. I'm afraid that these approaches would not solve this problem. Ronald
Re: Script Hangs when executing ...
Have you tried running this through the debugger to see where exactly the
problem is occurring. Have you also tried running the stored proc in isql
(or what ever M$SQL calls it) to make sure the stored proc is OK?
The error message below indicates that your statement handle is still
active... you said you used finish() on this, right? I don't know about
M$SQL, but in Sybase it is sometimes helpful to loop through the result
set completely (look in the DBD::Sybase perldoc for the code).
The small snippet of code you have looks like the syntax is correct, so
I'd look at the stored proc and then try to make sure that the statement
handle is not active by finishing it or making sure all the results have
been returned.
On Fri, 6 Apr 2001, Jamie Orzechowski wrote:
same error
Name "main::charge" used only once: possible typo at ./test line 11.
DBI::db=HASH(0x81ce7d8) trace level set to 9 in DBI 1.15-nothread
- disconnect for DBD::Sybase::db (DBI::db=HASH(0x81ce82c)~0x81ce7d8)
DBI::db=HASH(0x81ce7d8)-disconnect invalidates 1 active statement handle
(either destroy statement handles or call finish on them before
disconnecting) at ./test line 25.
syb_db_disconnect() - ct_close()
- disconnect= 1 at ./test line 25.
- DESTROY for DBD::Sybase::db (DBI::db=HASH(0x81ce7d8)~INNER)
- DESTROY= undef during global destruction.
--
$dbh = DBI-connect("dbi:Sybase:database=xxx", "x",
"x");
$sql = _ENDSQL;
Exec Subscribe_InsertPlan 13200,136,6,11,"04/06/2001"
_ENDSQL
$sth = $dbh-prepare($sql);
$sth-execute;
$dbh-trace(9);
$dbh-disconnect() or die("$DBI::errstr $DBI::err\n");
- Original Message -
From: "Curt Russell Crandall" [EMAIL PROTECTED]
To: "Jamie Orzechowski" [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Friday, April 06, 2001 2:37 PM
Subject: Re: Script Hangs when executing ...
I don't know if this will help, but try changing
$dbh-disconnect || print "$DBI::errstr $DBI::err\n";
to
$dbh-disconnect() or die("$DBI::errstr $DBI::err\n");
I'm probably wrong, but if something is wrong with disconnect, but not
'die'ing, you might be causing it to hang on the print. Since disconnect
is at the very end of your script and a failure in disconnect probably
indicates something serious... I'd just let it die.
On Fri, 6 Apr 2001, Jamie Orzechowski wrote:
Hello ... I have a script which connected to a MS SQL database ... it
hangs
right at the end of the script ... if I check my database it DOES insert
all
the information correctly ... it just hangs and never exits ... any
ideas??
here is the script ...
!/usr/bin/sybaseperl
$ENV{'SYBASE'}="/opt/sybase";
$ENV{'DSQUERY'}="rodopi";
use DBI;
use Date::Manip;
use Date::Format;
$tomorrow = time2str("%h %d %Y", UnixDate(ParseDate("tomorrow"),"%s"));
$todaystring = ParseDate("today");
$today = time2str("%m/%d/%Y", UnixDate(ParseDate("today"),"%s"));
$thismonth = time2str("%m/01/%Y", UnixDate(ParseDate("today"),"%s"));
$lastmonth = time2str("%m/01/%Y",
UnixDate(ParseDate("lastmonth"),"%s"));
$charge = 3;
$extracharge = 0;
$duration = 6;
$tax = 1.07;
$dbh = DBI-connect("dbi:Sybase:database=X", "XX", "XXX");
$sql = _ENDSQL;
SELECT Customers.CustomerID, Plans.PlanID, Emails.PopName,
Emails.EmailAddress, Emails.Password
FROM Plans INNER JOIN Customers ON Plans.CustomerID =
Customers.CustomerID
INNER JOIN Emails ON Plans.PlanID = Emails.PlanID
WHERE Emails.PopName="$emailname" AND Emails.EmailAddress="$email" AND
Emails.Password="$password"
_ENDSQL
$sth = $dbh-prepare($sql);
$sth-execute || warn $sth-errstr;
while (@detail = $sth-fetchrow_array) {
$custid = $detail[0];
$emailid = $detail[1];
$popname = $detail[2];
$address = $detail[3];
$password = $detail[4];
$realname = $detail[5];
print "Custid : $custid\n";
print "EmailId : $emailid\n";
print "address : $address\n";
print "Popname : $popname\n";
print "Password: $password\n";
print "Real: $real\n";
print "Today : $today\n";
}
#---
# Insert Plan
$exec = qq{ Exec Subscribe_InsertPlan $custid,136,6,11,"$today"};
$sth2 = $dbh-prepare($exec);
$sth2-execute || warn $sth2-errstr;
#---
Re: newbie: need help in specifying ODB connection parameters to SQServer
Why not try DBD::Sybase if you have the db libraries for M$SQL on the machine where your perl app is running? DBD::Sybase will run with both Sybase and M$SQL. Sorry that doesn't really answer your question about ODBC, but if you aren't tied to ODBC, the other driver should work for you. Good Luck --Curt On Thu, 5 Apr 2001, David J. Marcus wrote: Hi I'm running on a Win2K w/SP1 system. I've searched hi/lo for a clear example on how to connect and use ODBC to get to an MS/SQL Server database. I'm trying to connect to a SQL Server system on my local network (resides on \\SQLSRVR). I've defined an ODBC data source name using the Control Panel - Administrative Tools - Data Sources (ODBC) - System DSN I specified under the Name column: TestDB the Driver is: SQL Server In the configuration of the entry the Name shows up correctly as TestDB and the Server to connect to shows up as SQLSRVR. When setting up the entry, the testing of the connection indicated that it was successful (able to interact with the DB). When I list the available ODBC drivers (using example in Programming the Perl DBI book), it shows: Driver: ODBC Data Source is DBI:ODBC:Visual FoxPro Database . . . Data Source is DBI:ODBC:TestDB . . . So I know that the ODBC connection is there. In my test program I: my $conn = DBI-connect( "dbi:ODBC:DSN=TestDB", 'sa', ''') or die... my $sth = $conn-prepare( " CREATE TABLE ..."} or die... $sth-execute or die... The connect fails with the following error message: DBI-connect(DSN=TestDB) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002)(DBD: db_login/SQLConnect err=-1) I am suspecting that I am not specifying the DSN properly. I would love to see a simple example with actual values in the connect statement corresponding to an ODBC connection to an MS/SQL Server database. -TIA David
connecting to Sybase through dbish
Hi, I'm having problems opening a db connection to Sybase using dbish. The perldoc says to use the syntax: dbi:Sybase:server=X [user [password]] So, I executed dbish and did the following Available DBI drivers: 1: dbi:ADO 2: dbi:ExampleP 3: dbi:Oracle 4: dbi:Proxy 5: dbi:Sybase Enter driver name or number, or full 'dbi:...:...' DSN: here I entered: dbi:Sybase:server=SERVER1 user_name passwd I received the following error: Connecting to 'dbi:Sybase:server=SERVER1 user_name passwd' as ''... DBI-connect failed: server message number=4002 severity=14 state=1 line=8 server=SERVER1 text=Login failed. OpenClient message: LAYER = (4) ORIGIN = (1) SEVERITY = (4) NUMBER = (44) Message String: ct_connect(): protocol specific layer: external error: The attempt to connect to the server failed. at /usr/local/lib/perl5/site_perl/DBI/Shell.pm line 738 My configuration is: SunOS 5.6 Perl 5.00404 DBI 1.13 Shell 10.6 DBD::Sybase 0.21 Sybase 11.9.2 The server name, user and id are definitely correct since I use them to open DBI connections in my Perl scripts and connect through isql all of the time. I've tried comma-delimiting the dsn, user, password and ';' delimiting the list as well. All gave pretty much the same error. Would anyone know what it is I am doing wrong? Thanks --Curt
Re: connecting to Sybase through dbish
Bingo! I just assumed I could use the same syntax at the dbish DSN prompt. But it works fine if I use that syntax when invoking dbish. thanks --Curt On Thu, 29 Mar 2001, Michael Peppler wrote: Curt Russell Crandall writes: Hi, I'm having problems opening a db connection to Sybase using dbish. The perldoc says to use the syntax: dbi:Sybase:server=X [user [password]] That's on the command line. I looked at the code for DBI::Shell and I don't see anyway of entering the user interactively, unless it's part of the DSN, and DBD::Sybase doesn't understand that (yet). Maybe I'll add user=foo and password=bar logic to the DSN part of connect() for DBD::Sybase. At the moment you can also set the DBI_USER environment variable before running the script. Michael -- Michael Peppler - Data Migrations Inc. - [EMAIL PROTECTED] http://www.mbay.net/~mpeppler - [EMAIL PROTECTED] International Sybase User Group - http://www.isug.com Sybase on Linux mailing list: [EMAIL PROTECTED]
yet another addendum
I kept looking and the combination of parameters passed to the select that seems to hang ($sth3) ends up returning NULL when I run it against the development db. Using declare @date datetime select @date = max(date) from dbo.table3 where a = '020' and b = '451' select @date I've now been waiting for over 10min to see a result returned in isql. Any other combo of a and b that I know to be in the input causes a return immediately. Running a similar select (w/o all the declare junk) against Informix, with a table with almost as many rows as its Sybase counterpart, returns the select within less than a second. I suspect I'll have to create some sort of hash containing the max dates at the beginning of the program and then query against the hash later on. Not the kind of overhead I'd want to deal with when the number of input records is relatively small. --Curt
Re: Use C header file
I think you can use h2ph to convert the header file into something that perl can use. There's several different ways to mix Perl with C/C++. You can use one of the embed modules, XS, inline.pm, etc. Look at the Perl perldoc for a list of appropriate documents on embedding/extending Perl. If you have the Camel or Panther Books (Programming Perl and Advanced Perl Programming from O'Reilly Assoc.), they have sections that show you how to do this as well. inline.pm is relatively new, so I think you need to look at its perldoc and I *think* inline.pm is really just for embedding small C functions directly into your Perl code. --Curt On Thu, 29 Mar 2001, Rajan Sharma wrote: Can i use c header file in perl program. Want to use some functions that are there in c header file. I know this is not DBI question. But if you can answer me for it. :) Thanks _ Get your FREE download of MSN Explorer at http://explorer.msn.com
Re: DBI compilation failed
I could not read the attachment with my mail reader. Can you tell me what C compiler you are using? Are you using the Perl interpreter supplied by Sun? If you are, reinstall Perl using the gcc compiler. Then try installing DBI and DBD* also using gcc. Sun's Perl and C compiler spell disaster when you are trying to upgrade or add modules... at least in my experience. --Curt On Thu, 29 Mar 2001, Yossi Winograd wrote: Hello, I am trying to install the DBI module for perl on Sun Ultra 10. I run perl 5.005_03. At the 'make' command, the c compiler fails with an error: 'language optional software package not installed'. I tried to do it on other machines around and received the very same error. The complete output of my installation is attached. Can you help please? In case there is something wrong with my c compiler, can someone who installed it on Sun Ultra 10 send me all the executables? Also, I will need to connect to an oracle database. Will I need to download and install a driver or the DBI will have the driver? Thanks, Yossi WinogradLucent Technologies Inc. Telephone: (630)713-7393 263 Shuman Blvd. Fax: (630)713-2900Naperville, IL. 60566-7050 E-mail: [EMAIL PROTECTED] USA
Re: ODBC Error - Invalid Cursor State (SQL-24000)
No that wasn't the case here. Each select only produces 1 result row.
So, I just did a
$sth-execute() or die(...);
$href = $sth-fetchrow_hashref();
In the method... the method is executed in a loop within the caller.
With Sybase, it was necessary to insert a finish after the call to
fetchrow_hashref... from what I recall, the error happened at compile time
and didn't even attempt execution (I could be wrong about that, though).
So for me, there were 2 morals of the story here. First, in regards to
the original question, it's not necessary to keep doing a prepare just
because you call finish on the statement handle. Second, avoid Sybase
whenever possible. I've been doing most of my DBI development with
Informix and when I started working with Sybase it's been one problem
after the other... i.e. no placeholders in statements executing stored
procs, can't have multiple statement handles open on a db handle with
AutoCommit off, etc. etc. Some of this may be due to the fact that the
Sybase driver is not current (I have no choice), but the concensus I've
heard from people who've worked with Sybase and another db such as Oracle,
Informix, DB2 is that Sybase is garbage.
On Wed, 28 Mar 2001, Alexander Farber (EED) wrote:
Hi Curt,
Curt Russell Crandall wrote:
I ran into a similar problem with Sybase. I had a statement prepared
using placeholders (a select statement) and I had to loop several times as
I processed transactions and each time through the loop I had to execute
the prepared statement. Perl and/or Sybase complained that I needed to
finish the statement handle.
maybe the reason for that was, that you had several data sets
returned by 1 statement and you had to use this way (described
in "perldoc DBD::Sybase"):
do {
while($d = $sth-fetch) {
... do something with the data
}
} while($sth-{syb_more_results});
Regards
Alex
Using finish doesn't mean you have to keep repreparing the statement
handle... according to the Cheetah book, it's a rarely used functions used
for "internal housekeeping". In my program, I prepare my frequently used
select statements in an init method and assign the statement handle to an
object attribute. I have no need to prepare the statement more than once
and explicitly finish()'ing a handle after a fetch* didn't cause any
noticable decrease in performance. So I don't think using finish is any
sort of a "nasty workaround". I would just try to remove the multiple
prepare()'s and prepare the statement up front, then just finish() after
each fetch*. Hope it helps.
Re: ODBC Error - Invalid Cursor State (SQL-24000)
On Wed, 28 Mar 2001, Alexander Farber (EED) wrote:
Curt Russell Crandall wrote:
No that wasn't the case here. Each select only produces 1 result row.
Are you really sure it was 1 row?
That has been verified. It is a small table containing only 7
rows... for now.
So, I just did a
$sth-execute() or die(...);
$href = $sth-fetchrow_hashref();
I would try
while ($href = $sth - fetchrow_hashref)
instead.
Well, there's no need for that with there being only one row... if there
is more than one row, then there is a problem because that would conflict
with a business rule.
If only one row is returned (which IS the case here), there is NO reason
why DBD::Sybase/Sybase should be issuing the warning:
DBD::Sybase::st execute failed: OpenClient message: LAYER = (1) ORIGIN =
(1) SEVERITY = (1) NUMBER = (16)
Message String: ct_param(): user api layer: external error: This routine
cannot be called while results are pending for a command that has been
setn to the server.
In the method... the method is executed in a loop within the caller.
With Sybase, it was necessary to insert a finish after the call to
fetchrow_hashref...
I never seen that and will believe it only if I see some code.
You don't have to call finish if you have fetched _all_ results.
The code is too large to post here and I'd have to clean out proprietary
names, etc. to post. It suffices to say that I prepare a statement
handle, then in a loop, that statement handle is executed and the single
row is fetched. Even when a single row is returned, the current
configuration of Sybase, Perl, and DB* causes me to something like adding
a call to finish or what you had suggested with some sort of a loop.
from what I recall, the error happened at compile time
and didn't even attempt execution (I could be wrong about that, though).
So for me, there were 2 morals of the story here. First, in regards to
the original question, it's not necessary to keep doing a prepare just
because you call finish on the statement handle. Second, avoid Sybase
whenever possible.
Sybase is a nice database, I don't understand why are you saying this.
EVERYONE I've worked with who has used Sybase dispises it. EVERY client I
have worked for that has used or is using Sybase is/has switch(ing) to
something like Oracle unless they have too much invested in Sybase to
economically switch to something else. My personal experience working
with Sybase after working predominantly with Informix has been rather
negative.
To quote my last PM who has worked with databases for 15+ years and with
DBI for 3+ years "Sybase sucks. Get a real database. Or a smarter client
:) Only suckers have fallen for Sybase in recent history; everyone else
wised up years ago." I wouldn't call anyone who uses Sybase a
"sucker" since obviously there are those that like it. MY EXPERIENCE has
been that most do not... but that's only my opinion.
I've been doing most of my DBI development with
Informix and when I started working with Sybase it's been one problem
after the other... i.e. no placeholders in statements executing stored
procs, can't have multiple statement handles open on a db handle with
AutoCommit off, etc. etc. Some of this may be due to the fact that the
Sybase driver is not current (I have no choice),
What hinders you to install the current Perl module (DBD::Sybase or
Sybperl)? You can always do that (for example in your home dir).
The client. Installing something my home directory is mostly a waste of
time since it cannot be used in production. As frustrating as it can be
not to have the latest and greatest of everything, the reality is that
many clients, particularly in the financial industry, do not (cannot
?) regularly upgrade compilers, interpreters, libraries, OS's etc.
Thanks for all of the input. I had forgotten about using do {} while
($sth-{syb_more_results} which will come in handy in my next app.
--Curt
Re: Can PHP use Perl
First of all, I'm not an expert on PHP. But, I believe you cpan has modules like Mason that could give you what you want w/o messing with PHP. Actually, I'm not real big on mixing presentation layers with code and you might consider using mod_perl or FastCGI instead. That being said, why do you want to mix PHP and Perl? Is it in order to use Perl DBI? If so, PHP, I believe, has a similar db interface available (???I think)???. I don't think you really want to mix PHP and Perl unless you have a really really good reason to do so. PHP alone probably has the capabilities you are already looking for. If not and you have the flexibility to pick which tools you are going to use, write the whole thing in Perl. If you know Java well, maybe write a Java servlet. I would try posting a question like this to a more general Perl mail list (look at www.perl.org for a list) since this is a mailing list for DBI issues. You will probably find more help there. Good Luck, --Curt On Wed, 28 Mar 2001, Dexter Coehlo wrote: Howdy dbi-users, I know a lot of here use perl, How can if possible can I use Perlin PHP, I now know PHP is another script language. But I believe I heard you can embed Perl into a PHP page. That PHP as I thought was a technology like ASPallowing embedding different languages within PHP tags. Thanks for helping Dexter
Re: ODBC Error - Invalid Cursor State (SQL-24000)
Phil,
I ran into a similar problem with Sybase. I had a statement prepared
using placeholders (a select statement) and I had to loop several times as
I processed transactions and each time through the loop I had to execute
the prepared statement. Perl and/or Sybase complained that I needed to
finish the statement handle.
Using finish doesn't mean you have to keep repreparing the statement
handle... according to the Cheetah book, it's a rarely used functions used
for "internal housekeeping". In my program, I prepare my frequently used
select statements in an init method and assign the statement handle to an
object attribute. I have no need to prepare the statement more than once
and explicitly finish()'ing a handle after a fetch* didn't cause any
noticable decrease in performance. So I don't think using finish is any
sort of a "nasty workaround". I would just try to remove the multiple
prepare()'s and prepare the statement up front, then just finish() after
each fetch*. Hope it helps.
--Curt
On Mon, 26 Mar 2001, Phil R Lawrence wrote:
From the DBI archives I found a workaround for this error (one
which takes all the performance benefit out of placeholders and
prepared statements, however). Any ideas as to how we can lick
this and avoid the nasty workaround? Read on to see the
problem...
On 30 Jun 1998, Jeff Urlwin ([EMAIL PROTECTED]) wrote:
I am using the DBI:ODBC driver with an Access97 database. It
seems that
when calling 'execute' too many times I receive this error
"[Microsoft][ODBC Driver Manager] Invalid Cursor State
(SQL-24000) (DBD:
st_execute/SQL Execute err=-1)"
On 15 Oct 1998, Thomas Yengst ([EMAIL PROTECTED]) wrote:
This one dogged me for over a day...finally, a solution.
Just to add some experimental evidence to this problem with the
implementation or MS-SQL itself (it isn't a problem if you're
using DBI
with MySQL), if you do an explicit $sth-finish after
completing a set
of fetchrow_*, then the Invalid cursor state does not occur.
I recieve the same error in the following test script. A redo of
the script (also below) where I re-prepare the cursor every time
avoids the problem. BTW, I am using Access 2000, and my Perl and
DBI came new from ActiveState a few weeks ago.
Version with the problem:
--
use warnings;
use strict;
use DBI;
$| = 1;
DBI-trace(2,'trace.log');
my $DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=BLAH.mdb';
my $dbh = DBI-connect("dbi:ODBC:$DSN", '', '', {RaiseError =
1})
or die "Couldn't connect!";
my %results;
my $sth = $dbh-prepare("");
SELECT count(YOQsScheduledCode)
FROM Sites INNER JOIN
(
YOQs INNER JOIN tblYOQsScheduled
ON YOQs.[YOQ Key] =
tblYOQsScheduled.AdministeredYOQKey
)
ON Sites.[Site Key] = YOQs.[Site Key]
WHERE Sites.Identifier = '1019'
AND ScheduledDate = #2001-01-01#
AND ScheduledDate = #2001-03-26#
AND AdministeredYOQDaysVariance = ?
AND AdministeredYOQDaysVariance = ?
AND [Provider Key] = ?
my $idx = 0;
foreach (527) {
$idx++;
my $staff_num = 'Staff' . $idx;
$results{"${staff_num}ID"} = $_;
# On time count
$sth-execute(2,-2,$_);
$results{"${staff_num}OnTime"} = $sth-fetchrow_array;
# Very Early count
DIES HERE with Invalid Cursor State!
$sth-execute(-5,-365,$_);
##
$results{"${staff_num}VeryEarly"} = $sth-fetchrow_array;
# Early count
$sth-execute(-3,-4,$_);
$results{"${staff_num}Early"} = $sth-fetchrow_array;
# Late count
$sth-execute(4,3,$_);
$results{"${staff_num}Late"} = $sth-fetchrow_array;
# Very Late count
$sth-execute(365,5,$_);
$results{"${staff_num}Late"} = $sth-fetchrow_array;
}
$sth-finish;
$dbh-disconnect;
--
Version that doesn't complain:
--
use warnings;
use strict;
use DBI;
$| = 1;
DBI-trace(2,'trace.log');
my $DSN = 'driver=Microsoft Access Driver
(*.mdb);dbq=StudyManager_PHIL.mdb';
my $dbh = DBI-connect("dbi:ODBC:$DSN", '', '', {RaiseError =
1})
or die "Couldn't connect!";
my %results;
my $sql = "";
SELECT count(YOQsScheduledCode)
FROM Sites INNER JOIN
(
YOQs INNER JOIN tblYOQsScheduled
ON YOQs.[YOQ Key] =
tblYOQsScheduled.AdministeredYOQKey
)
ON Sites.[Site Key] = YOQs.[Site Key]
WHERE Sites.Identifier = '1019'
AND ScheduledDate = #2001-01-01#
AND ScheduledDate = #2001-03-26#
AND AdministeredYOQDaysVariance = ?
AND AdministeredYOQDaysVariance = ?
AND [Provider Key] = ?
my $sth;
my $idx = 0;
foreach (527) {
Re: Fellow dbi users simple fork prob.
Can you send a chunk of code so we can see what you are trying to do? In the meantime, look in Camel III Chapter 16 (Interprocess Communication) and perlipc perldoc for examples of correct forking. Also, there are some examples in chapters 16 17 in the Perl Cookbook of forking child processes. --Curt On Tue, 27 Mar 2001, Dexter Coehlo wrote: Hi fellow dbiusers, I have pulled emails(from my opt in list I may add) from a MySql db. Waht I wanted was to have a seperate process , say a child process after forking send out emailsthen exit while the parent would give a "Done" confirmation screen so that the admin wouldnt have to wait a long time for that screen and possibly time out(of course I could print the emails to screen). What I am getting the the confirmation screen being displayed after all the emails are sent like there is not 2 seperate processes happening but there is. I would like the child process to continue should the parent which displays the "Emails sent " screen discontinue and i believe that what should happen. Thanks Dexter
db disconnect
This may be more of a general Perl question than a DBI one, but since DBI
is involved, I thought I'd ask anyway.
I create a database handle called $dbh_sale. I want to ensure that this
handle is destroyed at the end of the program... so, I wrote up this chunk
of code
END
{
$dbh_sale-disconnect(); ###this is line 155
}
When doing a perl -c using Perl 5.00404, I get:
test.pl syntax OK
Can't call method "disconnect" without a package or object reference at
test.pl line 155.
END failed--cleanup aborted.
However, I get no such error/warning when running under Perl 5.6.0. Since
I am forced to use Perl 5.00404, I am concerned that this error could
cause a problem down the line. Does anyone know why Perl 5.00404 is
reporting this? Since I've only used Perl 5.00503 and Perl 5.6.0, I've
never run into this problem before and I have no idea if this a really a
bug or if it's just sloppy error reporting by the old interpreter.
Thanks,
Curt Crandall
distinguishing NULL from NULL
I'm having a problem where my intention is to insert a NULL value into a
nullable float column, however the result ends up being a floating point
0.
I have an object created by Class::Struct that contains a field for each
row in a particular db table. If I have no value to assign to a field,
I'm entering NULL:
i.e. $obj-member("NULL")
This works fine for (var)char datatypes, but apparently the string NULL is
automatically converted to 0.00 when I do:
my $sth = $dbh-prepare( EOF
insert into owner.table
(column1)
values (?)
EOF
);
$sth-execute($obj-member) or die .
I've tried 2 things to correct this. First, I tried using
$obj-member(NULL), however that is not allowed under strict
subs. Second, I was advised to try $obj-member(undef), however that
caused a segmentation fault (? was replaced by nothing after bind_param).
If I go into isql and manually type NULL... it works. Does anyone know
how I can assign NULL to my object member variable and have it be
correctly interpreted as NULL and not "NULL"?
I'm running this on SunOS 5.6 using Sun's Perl 5.00404, DBI.pm 1.13,
DBD::Sybase 0.21 and Sybase 11.1.1. Upgrading these is not possible... I
just have to live with it.
Thanks,
Curt Crandall
