RE: DBI buffer commit
I have found that the problem ocuurs in many cases. For example, I use sqlload userid=scott/tiger control=load log=load.log to upload my data to the oracle. Once the data file is very long. The same problem also occurs: it is stuck. Jeff >From: "Sterin, Ilya" <[EMAIL PROTECTED]> >To: jeff knot <[EMAIL PROTECTED]>, [EMAIL PROTECTED] >Subject: RE: DBI buffer commit >Date: Wed, 2 May 2001 21:31:44 -0400 > >Didn't know that SUN bought Oracle, but these days you never know, >especially when both of them are more focused on overthrowing Mico$oft than >their product:-) Kidding... > >Did you explicitly set AutoCommit => 0, if not you must do that in order to >use commit(). >Not sure what is overflowing and why, did you get an error message? How do >you know it's overflowing? Use trace() (see 'perldoc DBI') to get the >trace >output and send to list. > >Ilya Sterin > >-Original Message- >From: jeff knot [mailto:[EMAIL PROTECTED]] >Sent: Wednesday, May 02, 2001 6:14 PM >To: [EMAIL PROTECTED] >Subject: DBI buffer commit > > >Hi, >I am new to this list and I have the following question: > >My database is SUN Oracle. I try to use >perl DBI to update the record: >$sth=$dbh->prepare("update mytable set filed=mynewrecord >where id=?"); >$sth->execute ($i); >After doing the above over 1000 lines, my database stucks. I guess my >buffer > overflow. Then >I use $dbh->commit; trying to clear the buffer, but >it is not working. I am wondering if there is a solution. > >Jeff > >_ >Get your FREE download of MSN Explorer at http://explorer.msn.com _ Get your FREE download of MSN Explorer at http://explorer.msn.com
ANNOUCE:: DBD::Excel-0.01 (Re: Extract data from MS Excel Spreadsheets. Can it be done?)
- Original Message - From: "Kawai,Takanori" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, May 02, 2001 5:58 AM Subject: Re: Extract data from MS Excel Spreadsheets. Can it be done? (snip) > Now, I'm planinng to make a DBD for Excel with Spreadsheet::ParseExcel and > Spreadsheet::WriteExcel. I've just done it but still be a EXPERIMENT. So I don't upload DBD-Excel-0.01 and Spreadsheet-ParseExcel-0.22 to CPAN yet. You can get them from these URLs: DBD-Excel-0.01 http://www.hippo2000.net/perl/DBD-Excel-0.01.tar.gz Spreadsheet-ParseExcel-0.22 http://www.hippo2000.net/perl/Spreadsheet-ParseExcel-0.22.tar.gz I've tested it at Linux(TurboLinux6.0) and WinNT4.0 = NAME DBD::Excel - A class for DBI drivers that act on Excel File. DESCRIPTION The DBD::Excel module is a DBI driver. The module will work on the majority of Windows, UNIX and Macintosh platforms. REQUIREMENT This module requires these modules: DBI SQL::Statement Spreadsheet::WriteExcel 0.31 or later Spreadsheet::ParseExcel 0.22 or later == Kawai, Takanori(Hippo2000) Mail: [EMAIL PROTECTED] [EMAIL PROTECTED] http://member.nifty.ne.jp/hippo2000 ==
RE: DBD driver for Filemaker Pro
use DBD::ODBC Neil > -Original Message- > From: David Blaikie [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 03, 2001 8:24 AM > To: [EMAIL PROTECTED] > Subject: DBD driver for Filemaker Pro > > > Hi All, > > does anyone know if there is a DBD driver for Filemaker Pro? > > David > __ Please Note : Only the intended recipient is authorised to access or use this e-mail. If you are not the intended recipient, please delete this e-mail and notify the sender immediately. The contents of this e-mail are the writer's opinion and are not necessarily endorsed by the Gunz Companies unless expressly stated. We use virus scanning software but exclude all liability for viruses or similar in any attachment.
DBD driver for Filemaker Pro
Hi All, does anyone know if there is a DBD driver for Filemaker Pro? David
RE: DBI buffer commit
Didn't know that SUN bought Oracle, but these days you never know, especially when both of them are more focused on overthrowing Mico$oft than their product:-) Kidding... Did you explicitly set AutoCommit => 0, if not you must do that in order to use commit(). Not sure what is overflowing and why, did you get an error message? How do you know it's overflowing? Use trace() (see 'perldoc DBI') to get the trace output and send to list. Ilya Sterin -Original Message- From: jeff knot [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 02, 2001 6:14 PM To: [EMAIL PROTECTED] Subject: DBI buffer commit Hi, I am new to this list and I have the following question: My database is SUN Oracle. I try to use perl DBI to update the record: $sth=$dbh->prepare("update mytable set filed=mynewrecord where id=?"); $sth->execute ($i); After doing the above over 1000 lines, my database stucks. I guess my buffer overflow. Then I use $dbh->commit; trying to clear the buffer, but it is not working. I am wondering if there is a solution. Jeff _ Get your FREE download of MSN Explorer at http://explorer.msn.com
RE: DBI buffer commit
Excuse me. But what exactly is your code. I hope your not looping over both of those lines of code. -- Neil > -Original Message- > From: jeff knot [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 03, 2001 8:14 AM > To: [EMAIL PROTECTED] > Subject: DBI buffer commit > > > Hi, > I am new to this list and I have the following question: > > My database is SUN Oracle. I try to use > perl DBI to update the record: > $sth=$dbh->prepare("update mytable set filed=mynewrecord >where id=?"); > $sth->execute ($i); > After doing the above over 1000 lines, my database stucks. I > guess my buffer > overflow. Then > I use $dbh->commit; trying to clear the buffer, but > it is not working. I am wondering if there is a solution. > > Jeff > > _ > Get your FREE download of MSN Explorer at http://explorer.msn.com > __ Please Note : Only the intended recipient is authorised to access or use this e-mail. If you are not the intended recipient, please delete this e-mail and notify the sender immediately. The contents of this e-mail are the writer's opinion and are not necessarily endorsed by the Gunz Companies unless expressly stated. We use virus scanning software but exclude all liability for viruses or similar in any attachment.
RE: Sybase & problem with finish()
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()
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: Extract data from MS Excel Spreadsheets. Can it be done?
> -Original Message- > From: Sterin, Ilya [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, May 02, 2001 10:56 PM > To: 'Simon Oliver ' > Cc: '''[EMAIL PROTECTED]' ' ' > Subject: RE: Extract data from MS Excel Spreadsheets. Can it be done? > > > Also I would think that this is a big enough issue to > address, since most of > the servers are not NT and most of the clients are win32, therefore if > clients are working on excel which is later uploaded to the > server (Unix), > it should be processed on the server side, rather than having > users save as > CSV. I think we have side tracked here somewhat and are getting away from seeing the problem. What needs to be addressed? For an Excel Document to exist, there must be a Windows Machine somewhere. Information can be extracted from this using ODBC, OLE, ADO, and all manner of text ripping that Perl is well suited to. As a "database client", this Windows machine has all of the Native Windows ways of doing things and, of all wonderous suprises, a Perl that runs on it too. It seems that the focus of this thread has hitherto been about getting an MS Excel spreadsheet onto Solaris and then processing the contents. The point that should be open for consideration is, do you really want the whole spreadsheet anyway, or is this just a simple tool for a user to fill in table information? Unless you have some arcane layout of information in the Spreadsheet, your probably just want to extract a table with DBD::ADO or DBD::ODBC. In fact if the layout is anything but a table, DBI is just not your tool. The simple thing to do is process the Excel Data on the Win32 box and send that data to your database of choice. This can be done from either end, very easily with just a little thought put into the process. -- Neil > Ilya Sterin > > -Original Message- > From: Simon Oliver > Cc: ''[EMAIL PROTECTED]' ' > Sent: 05/02/2001 2:27 AM > Subject: Re: Extract data from MS Excel Spreadsheets. Can it be done? > > Steve Sapovits wrote: > > > If I'm wrong about this and someone's used either DBD::ADO or > > DBD::ODBC on Sun/Solaris, I'd like to hear how you did it. > One can't use DBD::ADO because that uses Win32::OLE which > needs a Win32 > Operating System. > > -- > Simon Oliver > __ Please Note : Only the intended recipient is authorised to access or use this e-mail. If you are not the intended recipient, please delete this e-mail and notify the sender immediately. The contents of this e-mail are the writer's opinion and are not necessarily endorsed by the Gunz Companies unless expressly stated. We use virus scanning software but exclude all liability for viruses or similar in any attachment.
DBI buffer commit
Hi, I am new to this list and I have the following question: My database is SUN Oracle. I try to use perl DBI to update the record: $sth=$dbh->prepare("update mytable set filed=mynewrecord where id=?"); $sth->execute ($i); After doing the above over 1000 lines, my database stucks. I guess my buffer overflow. Then I use $dbh->commit; trying to clear the buffer, but it is not working. I am wondering if there is a solution. Jeff _ Get your FREE download of MSN Explorer at http://explorer.msn.com
Re: Last fetch takes forever to undef under XBase
Tried that...no good. Still took 43 seconds, I think. I've watched it zip through all the records until it gets to the end where fetch returns undef, and that seems to be taking a while. I kinda' thought that it might be the connection, too, but obviously that's not the deal. I'm at a loss... Phillip Phillip Perkins wrote: > You would think that I would have already thought to try that...but, alas, I > am a moron... > > I'll give that a whirl... > > Phillip > > Chris Winters wrote: > > > * Phillip Perkins ([EMAIL PROTECTED]) [010502 17:44]: > > > Just tried it out... Still takes 25 seconds to do a > > > fetchall_arrayref. This is on a query that returns 994 records of > > > 22 fields. Then I did another that did 1 record of 69 fields...and > > > it took 46 seconds. Is it just the machine that it's on? I am > > > getting the data off of a Novell mounted server drive... I have yet > > > to dig down into the guts of the modules to see how it pulls data > > > from the DBF file, but I'm hoping to avoid this. > > > > Just curious: what happens when you copy the file to a local hard drive? > > > > Chris > > > > -- > > Chris Winters ([EMAIL PROTECTED]) > > Building enterprise-capable snack solutions since 1988.
Re: DBD:Sybase & problem with finish()
Set the syb_flush_finish attribute - that should fix the problem. (the issue being that MS-SQL doesn't handle the ct_cancel() call correctly) Michael David Sevier writes: > 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 > -- 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]
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: Last fetch takes forever to undef under XBase
You would think that I would have already thought to try that...but, alas, I am a moron... I'll give that a whirl... Phillip Chris Winters wrote: > * Phillip Perkins ([EMAIL PROTECTED]) [010502 17:44]: > > Just tried it out... Still takes 25 seconds to do a > > fetchall_arrayref. This is on a query that returns 994 records of > > 22 fields. Then I did another that did 1 record of 69 fields...and > > it took 46 seconds. Is it just the machine that it's on? I am > > getting the data off of a Novell mounted server drive... I have yet > > to dig down into the guts of the modules to see how it pulls data > > from the DBF file, but I'm hoping to avoid this. > > Just curious: what happens when you copy the file to a local hard drive? > > Chris > > -- > Chris Winters ([EMAIL PROTECTED]) > Building enterprise-capable snack solutions since 1988.
Re: Last fetch takes forever to undef under XBase
* Phillip Perkins ([EMAIL PROTECTED]) [010502 17:44]: > Just tried it out... Still takes 25 seconds to do a > fetchall_arrayref. This is on a query that returns 994 records of > 22 fields. Then I did another that did 1 record of 69 fields...and > it took 46 seconds. Is it just the machine that it's on? I am > getting the data off of a Novell mounted server drive... I have yet > to dig down into the guts of the modules to see how it pulls data > from the DBF file, but I'm hoping to avoid this. Just curious: what happens when you copy the file to a local hard drive? Chris -- Chris Winters ([EMAIL PROTECTED]) Building enterprise-capable snack solutions since 1988.
Re: Last fetch takes forever to undef under XBase
Just tried it out... Still takes 25 seconds to do a fetchall_arrayref. This is on a query that returns 994 records of 22 fields. Then I did another that did 1 record of 69 fields...and it took 46 seconds. Is it just the machine that it's on? I am getting the data off of a Novell mounted server drive... I have yet to dig down into the guts of the modules to see how it pulls data from the DBF file, but I'm hoping to avoid this. TIA Phillip Phillip Perkins wrote: > Okay, sorry in my last email. I put down wrong version numbers. However, I'm > updating XBase, DBD::XBase, and DBI. Gonna' give it a whirl... > > Phillip > > "Sterin, Ilya" wrote: > > > Phillip did send an example yesterday. Look in your email or the archives. > > > > Ilya Sterin > > > > -Original Message- > > From: Honza Pazdziora > > To: Phillip Perkins > > Cc: [EMAIL PROTECTED] > > Sent: 05/02/2001 1:46 AM > > Subject: Re: Last fetch takes forever to undef under XBase > > > > On Tue, May 01, 2001 at 11:36:49AM -0400, Phillip Perkins wrote: > > > Here's a question I hope someone can answer for me. When using DBI to > > > query a table, the query takes only milliseconds. However, if you do > > > any type of fetch on an sth, when you reach the EOF ($sth->fetch is > > > undef), it takes a while before it fails and, for instance, exits a > > > while loop. Same thing happens if you do a fetchall_arrayref. Can > > > anyone explain to me while this takes so long and possibly how to > > > overcome it? By the way, I'm using an XBase connection, so it may be > > > relative to that module. > > > > Moved to dbi-users. > > > > Please mention the name of the driver in Subject, I got to read this > > by a sheer luck. > > > > Please post five line example of this problem, together with version > > numbers of your software -- I'm unable to repeat the behaviour here. > > > > Thanks, > > > > -- > > > > Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ > >.project: Perl, DBI, Oracle, MySQL, auth. WWW servers, DBD::XBase. > >
Re: Last fetch takes forever to undef under XBase
Okay, sorry in my last email. I put down wrong version numbers. However, I'm updating XBase, DBD::XBase, and DBI. Gonna' give it a whirl... Phillip "Sterin, Ilya" wrote: > Phillip did send an example yesterday. Look in your email or the archives. > > Ilya Sterin > > -Original Message- > From: Honza Pazdziora > To: Phillip Perkins > Cc: [EMAIL PROTECTED] > Sent: 05/02/2001 1:46 AM > Subject: Re: Last fetch takes forever to undef under XBase > > On Tue, May 01, 2001 at 11:36:49AM -0400, Phillip Perkins wrote: > > Here's a question I hope someone can answer for me. When using DBI to > > query a table, the query takes only milliseconds. However, if you do > > any type of fetch on an sth, when you reach the EOF ($sth->fetch is > > undef), it takes a while before it fails and, for instance, exits a > > while loop. Same thing happens if you do a fetchall_arrayref. Can > > anyone explain to me while this takes so long and possibly how to > > overcome it? By the way, I'm using an XBase connection, so it may be > > relative to that module. > > Moved to dbi-users. > > Please mention the name of the driver in Subject, I got to read this > by a sheer luck. > > Please post five line example of this problem, together with version > numbers of your software -- I'm unable to repeat the behaviour here. > > Thanks, > > -- > > Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ >.project: Perl, DBI, Oracle, MySQL, auth. WWW servers, DBD::XBase. >
(Fwd) DBD::Oracle and International characters?
- Forwarded message from Bret Bailey <[EMAIL PROTECTED]> - Date: Tue, 01 May 2001 16:36:37 -0700 From: Bret Bailey <[EMAIL PROTECTED]> X-Accept-Language: en To: [EMAIL PROTECTED] Subject: DBD::Oracle and International characters? Hi there, I am using DBI and DBD::Oracle for web applications. I'm finding that the way we have it set up, it translates some international characters into english characters. Is there a way to configure it to handle international characters? If not, do you know of a module or library that does handle international characters? Thanks, Bret Bailey Borland IS - End forwarded message -
RE: :Pg errstr problem
At 12:13 PM 5/2/2001 -0600, Sterin, Ilya wrote: >Actually I am not even seeing the error number/message > >DBD::Pg::st execute failed: ERRO at init_accounts line 163. >Unable to insert fundsource (ERRO) > >ERRO will not really help us help you. If that is all you are getting, try >using trace(2, "file.log") (see docs) to trace the procedures, then send to >the list. > >Ilya Sterin It sometimes returns 'ERRO' as $dbh->errstr, and sometimes '7'. I suppose it's possible that '7' means something. I'm not so sure about 'ERRO' - it might be the first four characters of 'ERROR 7'. Here is a test program and the output of 'trace'. I have melted my eyeballs trying to find a list of numeric error codes in the PostgreSQL documentation. -Rick -- #!/usr/common/bin/perl -w use strict; #use diagnostics; use DBI; use Carp; # Loads modules from source directory if executed in source directory use lib qw(. /usr/local/netprint/testlib); use npstdlib; use netprintdb; $| = 1; # Connect to the database my($dbspec, $user, $password) = dbparams(); my($dbh); $dbh = DBI->connect($dbspec, 'rcc2', '', { AutoCommit => 1 }) or die "Unable to connect to '$dbspec' ($DBI::errstr)\n"; $dbh->trace(2, "bug.log"); my($table) = 'Superscriptions'; $dbh->do("DROP TABLE $table") or carp "Unable to DROP TABLE $table (".$dbh->errstr.")\n"; $dbh->disconnect; exit(0); -- DBI::db=HASH(0x2030f9fc) trace level set to 2 in DBI 1.13-nothread -> do in DBD::_::db for DBD::Pg::db (DBI::db=HASH(0x2030fa14)~0x2030f9fc 'DROP TABLE Superscriptions') 2 -> prepare for DBD::Pg::db (DBI::db=HASH(0x2030f9fc)~INNER 'DROP TABLE Superscriptions' undef) dbd_st_prepare: statement = >DROP TABLE Superscriptions< dbd_st_preparse: statement = >DROP TABLE Superscriptions< 2 <- prepare= DBI::st=HASH(0x2030fb28) at DBI.pm line 900. -> execute for DBD::Pg::st (DBI::st=HASH(0x2030fb28)~0x2030fb70) dbd_st_execute dbd_st_execute: statement = >DROP TABLE Superscriptions< error 7 recorded: !! ERROR: 7 '' <- execute= undef at DBI.pm line 901. !! ERROR: 7 '' <- do= undef at bug.pl line 25. -> errstr in DBD::_::common for DBD::Pg::db (DBI::db=HASH(0x2030fa14)~0x2030f9fc) <- errstr= 7 at bug.pl line 25. -> DESTROY for DBD::Pg::st (DBI::st=HASH(0x2030fb70)~INNER) dbd_st_destroy <- DESTROY= undef at bug.pl line 28. -> disconnect for DBD::Pg::db (DBI::db=HASH(0x2030fa14)~0x2030f9fc) dbd_db_disconnect <- disconnect= 1 at bug.pl line 28. -> DESTROY for DBD::Pg::db (DBI::db=HASH(0x2030f9fc)~INNER) dbd_db_destroy <- DESTROY= undef at unknown location!
RE: :Pg errstr problem
Actually I am not even seeing the error number/message DBD::Pg::st execute failed: ERRO at init_accounts line 163. Unable to insert fundsource (ERRO) ERRO will not really help us help you. If that is all you are getting, try using trace(2, "file.log") (see docs) to trace the procedures, then send to the list. Ilya Sterin -Original Message- From: Rick Cochran To: Sterin, Ilya; '[EMAIL PROTECTED] ' Sent: 05/02/2001 11:50 AM Subject: RE: :Pg errstr problem At 09:22 AM 5/2/2001 -0600, Sterin, Ilya wrote: >We would also like to see your prepare statement with a little more >description of the fields that are being binded. Ilya, Thanks for your quick response! I think you're inferring too much complexity. I get the same bogus error message(s) doing a "drop table" if the table doesn't exist. The context of this is that I have code which works with Informix and I'm trying to switch to PostgreSQL. In other words, this code produces intelligible error messages when used with DBD::Informix. I hope that this makes the situation clearer. I also hope that there is a good solution since I am very interested in using PostgreSQL. Yours, -Rick |Rick Cochran phone: 607-255-7618| |Network and Computing Systems, Cornell CIT FAX: 607-255-8521| |730 Rhodes Hall, Ithaca, N.Y. 14853email: [EMAIL PROTECTED]|
Re: DBD::DB2 installation problem
Hmm, I've never seen that before. Are you sure your DB2 client environment is setup properly? i.e. did you run sqllib/db2profile? Can you connect to a database from the command line? If this is all okay, please send me the complete output for each step: 'perl Makefile.PL', 'make' & 'make install'. Also, run the following commands and sent me the output: perl -v perl -mDBI -e 'print "DBI Version = $DBI::VERSION\n"' perl -mDBD::DB2 -e 'print "DBD::DB2 Version = $DBD::DB2::VERSION\n"' db2level Regards, Robert "rmd" <[EMAIL PROTECTED]> on 02/05/2001 02:13:45 PM Please respond to "rmd" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> cc: Subject: DBD::DB2 installation problem ´ve tried to install DBD:DB2 using CPAN or compiling it directly via perl Makefile.PL, then make test and i get always this error: [root@linux DBD-DB2-0.75]# make test make[1]: Entering directory '/instalar/DBD-DB2-0.75/Constants' make[1]: Leaving directory '/instalar/DBD-DB2-0.75/Constants' PERL_DL_NONLAZY=1 /usr/local/bin/perl -Iblib/arch -Iblib/lib -I/usr/local/lib/perl5/5.6.1/i686-linux -I/usr/local/lib/perl5/5.6.1 -e 'use Test::Harness qw(&runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t t/base..install_driver(DB2) failed: Can't load 'blib/arch/auto/DBD/DB2/DB2.so' for module DBD::DB2: undefined symbol: sqluirmt__FP7sqlcharP5sqldaT1P5sqlca at /usr/local/lib/perl5/5.6.1/i686-linux/DynaLoader.pm line 206. 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 t/base.t line 16 t/base..dubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 4-5 Failed 2/5 tests, 60.00% okay t/main..ok Failed Test Status Wstat Total Fail Failed List of Failed t/base.t 255 65280 52 40.00% 4-5 Failed 1/2 test scripts, 50.00% okay. 2/6 subtests failed, 66.67% okay. make: *** [test_dynamic] Error 29 My system is a RedHat v7 with DB2 Personal Edition v7.1 Thanx
RE: :Pg errstr problem
At 09:22 AM 5/2/2001 -0600, Sterin, Ilya wrote: >We would also like to see your prepare statement with a little more >description of the fields that are being binded. Ilya, Thanks for your quick response! I think you're inferring too much complexity. I get the same bogus error message(s) doing a "drop table" if the table doesn't exist. The context of this is that I have code which works with Informix and I'm trying to switch to PostgreSQL. In other words, this code produces intelligible error messages when used with DBD::Informix. I hope that this makes the situation clearer. I also hope that there is a good solution since I am very interested in using PostgreSQL. Yours, -Rick |Rick Cochran phone: 607-255-7618| |Network and Computing Systems, Cornell CIT FAX: 607-255-8521| |730 Rhodes Hall, Ithaca, N.Y. 14853email: [EMAIL PROTECTED]|
DBD::DB2 installation problem
´ve tried to install DBD:DB2 using CPAN or compiling it directly via perl Makefile.PL, then make test and i get always this error: [root@linux DBD-DB2-0.75]# make test make[1]: Entering directory '/instalar/DBD-DB2-0.75/Constants' make[1]: Leaving directory '/instalar/DBD-DB2-0.75/Constants' PERL_DL_NONLAZY=1 /usr/local/bin/perl -Iblib/arch -Iblib/lib -I/usr/local/lib/perl5/5.6.1/i686-linux -I/usr/local/lib/perl5/5.6.1 -e 'use Test::Harness qw(&runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t t/base..install_driver(DB2) failed: Can't load 'blib/arch/auto/DBD/DB2/DB2.so' for module DBD::DB2: undefined symbol: sqluirmt__FP7sqlcharP5sqldaT1P5sqlca at /usr/local/lib/perl5/5.6.1/i686-linux/DynaLoader.pm line 206. 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 t/base.t line 16 t/base..dubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 4-5 Failed 2/5 tests, 60.00% okay t/main..ok Failed Test Status Wstat Total Fail Failed List of Failed t/base.t 255 65280 52 40.00% 4-5 Failed 1/2 test scripts, 50.00% okay. 2/6 subtests failed, 66.67% okay. make: *** [test_dynamic] Error 29 My system is a RedHat v7 with DB2 Personal Edition v7.1 Thanx
Re: Last fetch takes forever to undef under XBase
I'm running RedHat Linux 6.1 (Cartman), Perl version 5.005_03 for i386 Linux, XBase 1.07, and DBD::XBase 0.160. Here's the example again: use DBI; my $dbh = DBI->connect("DBI:XBase:/directory") || die...; my $sth = $dbh->prepare("select * from table") || die...; # This is the part that only takes "milliseconds" $sth->execute(); # There's no difference in my problem between any fetch method so # I'll just use fetchrow_arrayref; # it's when the while() statement returns undef that takes so long... while ($ref = $sth->fetchrow_arrayref) { # all of this takes about as much time as you would expect # until that last fetch. Then it seems to take forever for it # to return undef on the $ref = $sth->fetchrow_arrayref. ... } # I could also just do a $sth->fetchall_arrayref, but it takes just as long. $sth->finish; $dbh->disconnect; exit 0; "Sterin, Ilya" wrote: > Phillip did send an example yesterday. Look in your email or the archives. > > Ilya Sterin > > -Original Message- > From: Honza Pazdziora > To: Phillip Perkins > Cc: [EMAIL PROTECTED] > Sent: 05/02/2001 1:46 AM > Subject: Re: Last fetch takes forever to undef under XBase > > On Tue, May 01, 2001 at 11:36:49AM -0400, Phillip Perkins wrote: > > Here's a question I hope someone can answer for me. When using DBI to > > query a table, the query takes only milliseconds. However, if you do > > any type of fetch on an sth, when you reach the EOF ($sth->fetch is > > undef), it takes a while before it fails and, for instance, exits a > > while loop. Same thing happens if you do a fetchall_arrayref. Can > > anyone explain to me while this takes so long and possibly how to > > overcome it? By the way, I'm using an XBase connection, so it may be > > relative to that module. > > Moved to dbi-users. > > Please mention the name of the driver in Subject, I got to read this > by a sheer luck. > > Please post five line example of this problem, together with version > numbers of your software -- I'm unable to repeat the behaviour here. > > Thanks, > > -- > > Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ >.project: Perl, DBI, Oracle, MySQL, auth. WWW servers, DBD::XBase. >
RE: Extract data from MS Excel Spreadsheets. Can it be done?
This is something done a lot based on my experience. Here we're okay for the most part: We mostly need to extract spreadsheet data row by row and either use it as Oracle query arguments or add to it with row by row queries. You can read row by row like that with Spreadsheet::ParseExcel and create new spreadsheets with Spreadsheet::WriteExcel. The Spreadsheet::ParseExcel author posted the other day that he's working on a DBI interface using the same base (OLE::Storage_Lite). Both of these modules have saved me lots of time. Since most of what I'd need is based on joins with our Oracle data, I'm not sure a DBI module would help if I need to do anything more complicated. It would still be two data sources and I can't join across different DB's that I know of (maybe I'm also missing something there). But using Spreadsheet::ParseExcel and DBI, I can read the data and load it into Oracle tables that I can then join with existing Oracle data. So as with everything Perl, There's More Than One Way To Do It. 8-) Another side of this whole sort of thing: We also periodically have a need to run server side Perl apps. that pull data out of Access. For that, I'd guess ODBC would be the best choice -- I've done this job on NT using Perl ODBC and DBI modules (read using ODBC, write using DBI). But again, there is not a full set of hooks to ODBC on all UNIX platforms. That may just be a matter of finding a UNIX based ODBC driver. I looked into this for only a very short time since it's a very small part of everything else I do. If anyone has knowledge here and free time (I don't have enough of either!) it would be great to have a consolidated web resource that explained the various ways to integrate Microsoft world data with UNIX based databases. I get by here but I'm sure there are many things I'm missing ... Steve Sapovits Global Sports Interactive Work Email: [EMAIL PROTECTED] Home Email: [EMAIL PROTECTED] Work Phone: 610-491-7087 Cell: 610-574-7706 Pager: 877-239-4003 > -Original Message- > From: Sterin, Ilya [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, May 02, 2001 8:56 AM > To: 'Simon Oliver ' > Cc: '''[EMAIL PROTECTED]' ' ' > Subject: RE: Extract data from MS Excel Spreadsheets. Can it be done? > > Also I would think that this is a big enough issue to address, since most > of > the servers are not NT and most of the clients are win32, therefore if > clients are working on excel which is later uploaded to the server (Unix), > it should be processed on the server side, rather than having users save > as > CSV. > > Ilya Sterin > > -Original Message- > From: Simon Oliver > Cc: ''[EMAIL PROTECTED]' ' > Sent: 05/02/2001 2:27 AM > Subject: Re: Extract data from MS Excel Spreadsheets. Can it be done? > > Steve Sapovits wrote: > > > If I'm wrong about this and someone's used either DBD::ADO or > > DBD::ODBC on Sun/Solaris, I'd like to hear how you did it. > One can't use DBD::ADO because that uses Win32::OLE which needs a Win32 > Operating System. > > -- > Simon Oliver
RE: :Pg errstr problem
We would also like to see your prepare statement with a little more description of the fields that are being binded. Ilya Sterin -Original Message- From: Rick Cochran To: [EMAIL PROTECTED] Sent: 05/02/2001 9:05 AM Subject: DBD::Pg errstr problem The following code: $insert_fundsource->execute("$aalias - tmp[1]",$tmp[1],$aalias,$tmp[4],$accttypes{$tmp[2]},$tmp[5]) or carp "Unable to insert fundsource (".$dbh->errstr.")\n"; produces the following error messages: DBD::Pg::st execute failed: ERRO at init_accounts line 163. Unable to insert fundsource (ERRO) DBD::Pg::st execute failed: at init_accounts line 163. Unable to insert fundsource (7) Any help would be greatly appreciated!! -Rick Here's all the twisty little configuration details: Platform: AIX 4.2.3 Perl (and therefore DBD::Pg) is compiled with xlc. PostgreSQL is compiled with gcc. curdie> perl -v This is perl, v5.6.0 built for aix Copyright 1987-2000, Larry Wall Perl may be copied only under the terms of either the Artistic License or the GNU General Public License, which may be found in the Perl 5.0 source kit. Complete documentation for Perl, including FAQ lists, should be found on this system using `man perl' or `perldoc perl'. If you have access to the Internet, point your browser at http://www.perl.com/, the Perl Home Page. curdie> perl -V Summary of my perl5 (revision 5.0 version 6 subversion 0) configuration: Platform: osname=aix, osvers=4.3.2.0, archname=aix uname='aix mqh 3 4 002071074200 ' config_args='-Dprefix=/usr/common -des' hint=recommended, useposix=true, d_sigaction=define usethreads=undef use5005threads=undef useithreads=undef usemultiplicity=undef useperlio=undef d_sfio=undef uselargefiles=define use64bitint=undef use64bitall=undef uselongdouble=undef usesocks=undef Compiler: cc='cc', optimize='-O', gccversion= cppflags='-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384' ccflags ='-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384 -q32 -D_LARGE_FILES -qlonglong' stdchar='unsigned char', d_stdstdio=define, usevfork=false intsize=4, longsize=4, ptrsize=4, doublesize=8 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=8 ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8 alignbytes=8, usemymalloc=n, prototype=define Linker and Libraries: ld='ld', ldflags =' -b32' libpth=/lib /usr/lib /usr/ccs/lib libs=-lbind -lnsl -ldbm -ldl -lld -lm -lC -lc -lcrypt -lbsd -lPW -liconv libc=/lib/libc.a, so=a, useshrplib=false, libperl=libperl.a Dynamic Linking: dlsrc=dl_aix.xs, dlext=so, d_dlsymun=undef, ccdlflags=' -bE:/usr/common/lib/perl5/5.6.0/aix/CORE/perl.exp' cccdlflags=' ', lddlflags='-bhalt:4 -bM:SRE -bI:$(PERL_INC)/perl.exp -bE:$(BASEEXT).exp -b noentry -lC -lc' Characteristics of this binary (from libperl): Compile-time options: USE_LARGE_FILES Built under aix Compiled at Apr 11 2000 14:43:13 @INC: /usr/common/lib/perl5/5.6.0/aix /usr/common/lib/perl5/5.6.0 /usr/common/lib/perl5/site_perl/5.6.0/aix /usr/common/lib/perl5/site_perl/5.6.0 /usr/common/lib/perl5/site_perl . PostgreSQL version 7.1 DBD-Pg version 0.98 DBI version 1.13 |Rick Cochran phone: 607-255-7618| |Network and Computing Systems, Cornell CIT FAX: 607-255-8521| |730 Rhodes Hall, Ithaca, N.Y. 14853email: [EMAIL PROTECTED]|
DBD::Pg errstr problem
The following code: $insert_fundsource->execute("$aalias - tmp[1]",$tmp[1],$aalias,$tmp[4],$accttypes{$tmp[2]},$tmp[5]) or carp "Unable to insert fundsource (".$dbh->errstr.")\n"; produces the following error messages: DBD::Pg::st execute failed: ERRO at init_accounts line 163. Unable to insert fundsource (ERRO) DBD::Pg::st execute failed: at init_accounts line 163. Unable to insert fundsource (7) Any help would be greatly appreciated!! -Rick Here's all the twisty little configuration details: Platform: AIX 4.2.3 Perl (and therefore DBD::Pg) is compiled with xlc. PostgreSQL is compiled with gcc. curdie> perl -v This is perl, v5.6.0 built for aix Copyright 1987-2000, Larry Wall Perl may be copied only under the terms of either the Artistic License or the GNU General Public License, which may be found in the Perl 5.0 source kit. Complete documentation for Perl, including FAQ lists, should be found on this system using `man perl' or `perldoc perl'. If you have access to the Internet, point your browser at http://www.perl.com/, the Perl Home Page. curdie> perl -V Summary of my perl5 (revision 5.0 version 6 subversion 0) configuration: Platform: osname=aix, osvers=4.3.2.0, archname=aix uname='aix mqh 3 4 002071074200 ' config_args='-Dprefix=/usr/common -des' hint=recommended, useposix=true, d_sigaction=define usethreads=undef use5005threads=undef useithreads=undef usemultiplicity=undef useperlio=undef d_sfio=undef uselargefiles=define use64bitint=undef use64bitall=undef uselongdouble=undef usesocks=undef Compiler: cc='cc', optimize='-O', gccversion= cppflags='-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384' ccflags ='-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384 -q32 -D_LARGE_FILES -qlonglong' stdchar='unsigned char', d_stdstdio=define, usevfork=false intsize=4, longsize=4, ptrsize=4, doublesize=8 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=8 ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8 alignbytes=8, usemymalloc=n, prototype=define Linker and Libraries: ld='ld', ldflags =' -b32' libpth=/lib /usr/lib /usr/ccs/lib libs=-lbind -lnsl -ldbm -ldl -lld -lm -lC -lc -lcrypt -lbsd -lPW -liconv libc=/lib/libc.a, so=a, useshrplib=false, libperl=libperl.a Dynamic Linking: dlsrc=dl_aix.xs, dlext=so, d_dlsymun=undef, ccdlflags=' -bE:/usr/common/lib/perl5/5.6.0/aix/CORE/perl.exp' cccdlflags=' ', lddlflags='-bhalt:4 -bM:SRE -bI:$(PERL_INC)/perl.exp -bE:$(BASEEXT).exp -b noentry -lC -lc' Characteristics of this binary (from libperl): Compile-time options: USE_LARGE_FILES Built under aix Compiled at Apr 11 2000 14:43:13 @INC: /usr/common/lib/perl5/5.6.0/aix /usr/common/lib/perl5/5.6.0 /usr/common/lib/perl5/site_perl/5.6.0/aix /usr/common/lib/perl5/site_perl/5.6.0 /usr/common/lib/perl5/site_perl . PostgreSQL version 7.1 DBD-Pg version 0.98 DBI version 1.13 |Rick Cochran phone: 607-255-7618| |Network and Computing Systems, Cornell CIT FAX: 607-255-8521| |730 Rhodes Hall, Ithaca, N.Y. 14853email: [EMAIL PROTECTED]|
RE: Bug (?): Whitespace-Handling of DBI
Well, first of both shoud of failed, since you can't use ';' at the end of you query in DBI. That simply is an end line character used by various databaser query parsers. Second, you should use trace() possibly at level two to trace the operation. You can then post the output to the list. Ilya Sterin -Original Message- From: Thomas Hillebrand To: [EMAIL PROTECTED] Sent: 05/02/2001 6:59 AM Subject: Bug (?): Whitespace-Handling of DBI Hi, although I'm programming a lot I don't use DBI every day. So, being more familar with perl itself, C or SGML-Standards, I found a problem that unfortunately costed me a whole evening to identify and I think it is some kind of bug. I expected that both of the following expressions should have identical results: $myquery = "SELECT * FROM MyTable;" ; $myquery = "SELECT * FROM MyTable; "; But - at least on my (sorry!) Windows-PC with a Win-32-version of Perl - they don't have! The first expression returns the expected result table, the second one leads to an empty table! The reason for that is the space at the end of the string that is going to be passed to DBI! As far as I know, according to all conventions of programming, the space should be ignored by DBI. Like Perl itself ignores the space at the end of the first example. Is this a bug? Is it just a problem of the win-version? Or is there any reason or purpose for that. Thank you and regards Thomas Hillebrand
RE: quoting binaries (MYSQL)
We need more code than (SELECT... and INSERT...). How did you see the funny unescaped character? Did you print them out, in that case did you binmode STDOUT? Use trace() to see exactly what is going on. Also an error message on insert would be very helpful to us. Ilya Sterin -Original Message- From: Yannis Livassof To: [EMAIL PROTECTED] Sent: 05/02/2001 3:28 AM Subject: quoting binaries (MYSQL) Hi! Does anybody knows how to quote the binarie fields properly using the DBI.pm (on Mysql)? I used "SELECT ..." , then $dnh->quote(some data), then $dbh->do("INSERT ...") The last "do" leads to error. As I see, some funny characters selected from binary fields are not escaped. I compared it with the output generated with "mysqldump". Plese help! Yannis Livassov
Bug (?): Whitespace-Handling of DBI
Hi, although I'm programming a lot I don't use DBI every day. So, being more familar with perl itself, C or SGML-Standards, I found a problem that unfortunately costed me a whole evening to identify and I think it is some kind of bug. I expected that both of the following expressions should have identical results: $myquery = "SELECT * FROM MyTable;" ; $myquery = "SELECT * FROM MyTable; "; But - at least on my (sorry!) Windows-PC with a Win-32-version of Perl - they don't have! The first expression returns the expected result table, the second one leads to an empty table! The reason for that is the space at the end of the string that is going to be passed to DBI! As far as I know, according to all conventions of programming, the space should be ignored by DBI. Like Perl itself ignores the space at the end of the first example. Is this a bug? Is it just a problem of the win-version? Or is there any reason or purpose for that. Thank you and regards Thomas Hillebrand
RE: Extract data from MS Excel Spreadsheets. Can it be done?
Also I would think that this is a big enough issue to address, since most of the servers are not NT and most of the clients are win32, therefore if clients are working on excel which is later uploaded to the server (Unix), it should be processed on the server side, rather than having users save as CSV. Ilya Sterin -Original Message- From: Simon Oliver Cc: ''[EMAIL PROTECTED]' ' Sent: 05/02/2001 2:27 AM Subject: Re: Extract data from MS Excel Spreadsheets. Can it be done? Steve Sapovits wrote: > If I'm wrong about this and someone's used either DBD::ADO or > DBD::ODBC on Sun/Solaris, I'd like to hear how you did it. One can't use DBD::ADO because that uses Win32::OLE which needs a Win32 Operating System. -- Simon Oliver
RE: Extract data from MS Excel Spreadsheets. Can it be done?
Right, but what I was thinking is how difficult would it be to port DBD::ADO to use OLE::Storage which is cross platform and preatty much same OLE DB functionality, unless you know of any limitations. Ilya Sterin -Original Message- From: Simon Oliver Cc: ''[EMAIL PROTECTED]' ' Sent: 05/02/2001 2:27 AM Subject: Re: Extract data from MS Excel Spreadsheets. Can it be done? Steve Sapovits wrote: > If I'm wrong about this and someone's used either DBD::ADO or > DBD::ODBC on Sun/Solaris, I'd like to hear how you did it. One can't use DBD::ADO because that uses Win32::OLE which needs a Win32 Operating System. -- Simon Oliver
RE: Last fetch takes forever to undef under XBase
Phillip did send an example yesterday. Look in your email or the archives. Ilya Sterin -Original Message- From: Honza Pazdziora To: Phillip Perkins Cc: [EMAIL PROTECTED] Sent: 05/02/2001 1:46 AM Subject: Re: Last fetch takes forever to undef under XBase On Tue, May 01, 2001 at 11:36:49AM -0400, Phillip Perkins wrote: > Here's a question I hope someone can answer for me. When using DBI to > query a table, the query takes only milliseconds. However, if you do > any type of fetch on an sth, when you reach the EOF ($sth->fetch is > undef), it takes a while before it fails and, for instance, exits a > while loop. Same thing happens if you do a fetchall_arrayref. Can > anyone explain to me while this takes so long and possibly how to > overcome it? By the way, I'm using an XBase connection, so it may be > relative to that module. Moved to dbi-users. Please mention the name of the driver in Subject, I got to read this by a sheer luck. Please post five line example of this problem, together with version numbers of your software -- I'm unable to repeat the behaviour here. Thanks, -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, DBI, Oracle, MySQL, auth. WWW servers, DBD::XBase.
Oracle 8.1.7 and CLOB's in an ML db
We've encountered some trouble with fetching CLOBs using a 8.17 client vs. an Oracle utf8 DB. I'm pretty sure the bug's somewhere in the OCI, but I may be wrong, and anyway - maybe you've heard something about this. When running the following code I get the following output (including error) if the client is 8.17 and the DB utf8. If I change to 8.15 or change the DB to regular the error isn't raised. The output: Starting Length=9 --Step 1 done -- database now open Table created Insert Performed DBD::Oracle::st fetchrow_hashref failed: (DBD NEED_DATA: OCILobRead) at ./testClob.pl line 39. Issuing rollback() for database handle being DESTROY'd without explicit disconnect(). The error, as you can see, is fetched during the "fetchrow_hashref". Any ideas? #!/usr/bin/perl -w use strict; use DBI; use DBD::Oracle qw(:ora_types); my $clob="C" x 9; print "Starting Length=", length $clob, "\n"; print "--Step 1 done --\n"; my$dbh = DBI->connect("dbi:Oracle(PrintError=>0,AutoCommit=>0):". "SER_TST","GILAD","GILAD") || die "couldn't connect to db"; # drop table but don't die if not there $dbh->do("drop table foobar"); print "database now open\n"; $dbh->do("CREATE TABLE foobar (foo NUMBER, bar CLOB)"); print "Table created\n"; my $sth = $dbh->prepare("insert into foobar values (?, ?)"); $sth->bind_param(1, "3"); $sth->bind_param(2, $clob, {"ora_type", ORA_CLOB}); $sth->execute()|| die $DBI::errstr; $sth->finish; print "Insert Performed\n"; $dbh->{RaiseError} = 1; $dbh->{LongTruncOk} = 1; # truncation on initial fetch is ok $sth->{LongReadLen} = 214748647; $sth = $dbh->prepare("SELECT foo, bar FROM foobar WHERE foo = ?"); $sth->execute(3); my $row; print "***"; $row = $sth->fetchrow_hashref; print"\n";; $sth->finish; $dbh->commit(); $dbh->disconnect; print "Length=",length $row->{BAR}, "\n"; Thanks, Gilad.
Re: DBI and DBD:Sybase
According to the man page... The DBD::Sybase module is built on top of the Sybase Open Client Client Library API. This library makes use of the Sybase interfaces file (sql.ini on Win32 machines) to make a link between a logical server name (e.g. SYBASE) and the physical machine / port number that the server is running on. The OpenClient library uses the environment variable SYBASE to find the location of the interfaces file, as well as other files that it needs (such as locale files). The SYBASE environment is the path to the Sybase installation (eg '/usr/local/sybase'). If you need to set it in your scripts, then you must set it in a BEGIN{} block: BEGIN { $ENV{SYBASE} = '/opt/sybase/11.0.2'; } $dbh = DBI->connect('dbi:Sybase:', $user, $passwd); Specifying the server name The server that DBD::Sybase connects to defaults to SYBASE, but can be specified in two ways. You can set the DSQUERY environement variable: $ENV{DSQUERY} = "ENGINEERING"; $dbh = DBI->connect('dbi:Sybase:', $user, $passwd); Or you can pass the server name in the first argument to connect(): $dbh = DBI->connect("dbi:Sybase:server=ENGINEERING", $user, $passwd); Specifying other connection specific parameters It is sometimes necessary (or beneficial) to specify other connection properties. Currently the following are supported: server Specify the server that we should connect to $dbh = DBI->connect("dbi:Sybase:server=BILLING", $user, $passwd); The default server is SYBASE, or the value of the $DSQUERY environment variable, if it is set. database Specify the database that should be made the default database. $dbh = DBI->connect("dbi:Sybase:database=sybsystemprocs", $user, $passwd); This is equivalent to $dbh = DBI->connect('dbi:Sybase:', $user, $passwd); $dbh->do("use sybsystemprocs"); charset Specify the character set that the client uses. $dbh = DBI->connect("dbi:Sybase:charset=iso_1", $user, $passwd); language Specify the language that the client uses. $dbh = DBI->connect("dbi:Sybase:language=us_english", $user, $passwd); packetSize Specify the network packet size that the connection should use. Using a larger packet size can increase performance for certain types of queries. See the Sybase documentation on how to enable this feature on the server. $dbh = DBI->connect("dbi:Sybase:packetSize=8192", $user, $passwd); interfaces Specify the location of an alternate interfaces file: $dbh = DBI->connect("dbi:Sybase:interfaces=/usr/local/sybase/interfaces", $user, $passwd); loginTimeout Specify the number of seconds that DBI->connect() will wait for a response from the Sybase server. If the server fails to respond before the specified number of seconds the DBI->connect() call fails with a timeout error. The default value is 60 seconds, which is usually enough, but on a busy server it is sometimes necessary to increase this value: $dbh = DBI->connect("dbi:Sybase:loginTimeout=240", # wait up to 4 minutes $user, $passwd); timeout Specify the number of seconds after which any Open Client calls will timeout the connection and mark it as dead. Once a timeout error has been received on a connection it should be closed and re-opened for further processing. Setting this value to 0 or a negative number will result in an unlimited timeout value. See also the Open Client documentation on CS_TIMEOUT. $dbh = DBI->connect("dbi:Sybase:timeout=240", # wait up to 4 minutes $user, $passwd); scriptName Specify the name for this connection that will be displayed in sp_who (ie in the sysprocesses table in the program_name column). $dbh->DBI->connect("dbi:Sybase:scriptName=myScript", $user, $password); hostname Specify the hostname that will be displayed by sp_who (and will be stored in the hostname column of sysprocesses).. $dbh->DBI->connect("dbi:Sybase:hostname=kiruna", $user, $password); tdsLevel Specify the TDS protocol level to use when connecting to the server. Valid values are CS_TDS_40, CS_TDS_42, CS_TDS_46, CS_TDS_495 and CS_TDS_50. In general this is automatically negotiated between the client and the server, but in certain cases this may need to be forced to a lower level by the client. $dbh->DBI->connect("dbi:Sybase:tdsLevel=CS_TDS_42", $user, $password); NOTE: Setting the tdsLevel below CS_TDS_495 will disable a number of features, ?-style placeholders and CHAINED non-AutoCommit mode, in particular. These different parameters (as well as the server name) can be strung together by separating each entry with a semi-colon: $dbh = DBI->connect("dbi:Sybase:server=ENGINEERING;packetSize=8192;language=us_english;charset=iso_1", $user, $pwd);
quoting binaries (MYSQL)
Hi! Does anybody knows how to quote the binarie fields properly using the DBI.pm (on Mysql)? I used "SELECT ..." , then $dnh->quote(some data), then $dbh->do("INSERT ...") The last "do" leads to error. As I see, some funny characters selected from binary fields are not escaped. I compared it with the output generated with "mysqldump". Plese help! Yannis Livassov
DBI and DBD:Sybase
I'm trying to install DBD-Sybase. I already have DBI installed. So first, I installed freetds. Next, I was able to configure DBD-Sybase already. But I tried running "make test" to make sure everything is OK. I know I have to edit PWD. But what value should I put into SRV? Is it the IP or the server name we have assigned when running ASE? UID=sa PWD= SRV= Thanks, Lester
Re: Extract data from MS Excel Spreadsheets. Can it be done?
Steve Sapovits wrote: > If I'm wrong about this and someone's used either DBD::ADO or > DBD::ODBC on Sun/Solaris, I'd like to hear how you did it. One can't use DBD::ADO because that uses Win32::OLE which needs a Win32 Operating System. -- Simon Oliver
Re: Last fetch takes forever to undef under XBase
On Tue, May 01, 2001 at 11:36:49AM -0400, Phillip Perkins wrote: > Here's a question I hope someone can answer for me. When using DBI to > query a table, the query takes only milliseconds. However, if you do > any type of fetch on an sth, when you reach the EOF ($sth->fetch is > undef), it takes a while before it fails and, for instance, exits a > while loop. Same thing happens if you do a fetchall_arrayref. Can > anyone explain to me while this takes so long and possibly how to > overcome it? By the way, I'm using an XBase connection, so it may be > relative to that module. Moved to dbi-users. Please mention the name of the driver in Subject, I got to read this by a sheer luck. Please post five line example of this problem, together with version numbers of your software -- I'm unable to repeat the behaviour here. Thanks, -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, DBI, Oracle, MySQL, auth. WWW servers, DBD::XBase.