Entering a TimeStamp value into DB2
I am having a problem using the Perl DBD::DB2 module to enter a 'TimeStamp' value into a DB2 table. I have been able to write a perl script which can enter an 'integer' or 'varchar' value into a DB2 table. I also need to enter a 'TimeStamp' value into the same table. I have not been able to correctly format the 'TimeStamp' value in my script so that the 'INSERT' command executes without errors. Does anyone have a perl example script for entering 'TimeStamp' values into a DB2 table? * Richard Rice Continental Tire North America, Inc. (704) 583-8668 *
Re: [Dbdpg-general] bug in DBD::Pg
On Mar 23, 2005, at 4:57 PM, Greg Sabino Mullane wrote: This has been fixed in CVS, and will be in 1.41. We hope to release it in the next few weeks. Until then, you can specifically bind the parameter types with bind_param, or you can upgrade your PostgreSQL server (this bug only affects older versions). Shouldn't we get a new release out sooner than that. Like maybe next week? This bug is really annoying people. Cheers, David
Re: hang with mysql in execute
On 2005-03-23 07:26:35 -0600, Jones Robert Civ TTMS Keesler wrote: If this is due to a timeout issue then you want to increase the timeout settings on your system. That would be something you will need to configure on the client side in the OS settings. No, he would have to configure that on the firewall: A firewall is terminating my connection with a mysql ^^ database after a period of inactivity. The firewall is probably not under his control, though (Even if it is, it may not be feasible to increase the timeout to a large enough value). The best solution is probably to use some keepalive mechanism. TCP keepalive would be the simplest, but there is no obvious way to enable it on either the client or the server side, so he has to change his application to issue a dummy query every $n minutes. hp -- _ | Peter J. Holzer \Beta means we're down to fixing misspelled comments in |_|_) | Sysadmin WSR \the source, and you might run into a memory leak if | | | [EMAIL PROTECTED] \you enable embedded haskell as a loadable module and __/ | http://www.hjp.at/ \write your plugins upside-down in lisp. [EMAIL PROTECTED] pgpVTOpeHvoKO.pgp Description: PGP signature
Re: hang with mysql in execute
Hi All, The 'proper' solution IMO is Peter's first suggestion. The firewall is doing it's job and forced wrapper timeouts can be a bit of a bodge. We're lowering the inactivity timeout on our data source servers (MySQL and openLDAP - we're seeing the same thing on both connections) to around 5 minutes - no real reason why it should be higher. Our client code will then be able to detect a gracefully terminated connection and reconnect without a hang. Thanks for everyones help on this. Chris --- Peter J. Holzer [EMAIL PROTECTED] wrote: On 2005-03-23 07:26:35 -0600, Jones Robert Civ TTMS Keesler wrote: If this is due to a timeout issue then you want to increase the timeout settings on your system. That would be something you will need to configure on the client side in the OS settings. No, he would have to configure that on the firewall: A firewall is terminating my connection with a mysql ^^ database after a period of inactivity. The firewall is probably not under his control, though (Even if it is, it may not be feasible to increase the timeout to a large enough value). The best solution is probably to use some keepalive mechanism. TCP keepalive would be the simplest, but there is no obvious way to enable it on either the client or the server side, so he has to change his application to issue a dummy query every $n minutes. hp -- _ | Peter J. Holzer \Beta means we're down to fixing misspelled comments in |_|_) | Sysadmin WSR \the source, and you might run into a memory leak if | | | [EMAIL PROTECTED] \you enable embedded haskell as a loadable module and __/ | http://www.hjp.at/ \write your plugins upside-down in lisp. [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
RE: Strange DBI Bug
It does work with SQL+. But I may well be wrorg and it mayl not be a DBI bug. The Oracle kernel already has a parsed version of the statement that causes the deadlock. The working statement is a different one and Oracle treats it differently. Best regards Peter BSI Business Systems Integration AG Täfernstrasse 16a CH-5405 Baden-Dättwil Phone (direct): +41 56 484 16 86 http://www.bsiag.com http://www.bsiag.com/
Re: Entering a TimeStamp value into DB2
Try googling next time for an answer to your question. I went to google and searched for db2 timestamp and this reference had some good info: http://www.craigsmullins.com/dbu_1200.htm Here's a snippet: Formatting If formatting the date and time data is a major consideration for your application then DATE and TIME may be a better solution. DB2 provides formatting options for DATE and TIME columns via local DATE and TIME exits. This means that you can specify a DB2 subsystem-wide default for the way that date and time values should be displayed. Likewise the CHAR function and DATE and TIME precompiler options exist for formatting DATE and TIME data. If the date and time information is to be extracted and displayed on a report or by an online application, the availability of these DB2-provided facilities for DATE and TIME columns should be considered when making your decision. These facilities are not available for TIMESTAMP columns. There is a single format for a timestamp value in DB2, and it looks like the sample shown previously. A timestamp value always begins with the date component, in the format -MM-DD combined using a hyphen (-) to the time component in the format: hh.mm.ss.zz. HTH. Hardy Merrill [EMAIL PROTECTED] 03/23/05 11:21 PM I am having a problem using the Perl DBD::DB2 module to enter a 'TimeStamp' value into a DB2 table. I have been able to write a perl script which can enter an 'integer' or 'varchar' value into a DB2 table. I also need to enter a 'TimeStamp' value into the same table. I have not been able to correctly format the 'TimeStamp' value in my script so that the 'INSERT' command executes without errors. Does anyone have a perl example script for entering 'TimeStamp' values into a DB2 table? * Richard Rice Continental Tire North America, Inc. (704) 583-8668 *
[PATCH] DBD::NullP
--- NullP.pm.orig 2005-03-24 13:17:24.09375 +0300
+++ NullP.pm2005-03-24 13:17:29.265625000 +0300
@@ -72,7 +72,7 @@
# else pass up to DBI to handle
if ($attrib eq 'AutoCommit') {
return 1 if $value; # is already set
- croak(Can't disable AutoCommit);
+ Carp::croak(Can't disable AutoCommit);
}
return $dbh-SUPER::STORE($attrib, $value);
}
--
Sergey Skvortsov
mailto: [EMAIL PROTECTED]
Bug in $dbh-clone
DBI Version 1.42
Can't set DBI::db=HASH(0x8fd1834)-{User}: unrecognised attribute or
invalid value at //ms/dist/perl5/PROJ/DBI/1.42-5.8/lib/perl5/DBI.pm line
634, DATA chunk 1.
NOTICE: If received in error, please destroy and notify sender. Sender does
not waive confidentiality or privilege, and use is prohibited.
RE: Bug in $dbh-clone
Problem occurs in version 1.47 as well.
-Original Message-
From: Anderson, James H (Company IT)
Sent: Thursday, March 24, 2005 11:55 AM
To: dbi-users@perl.org
Subject: Bug in $dbh-clone
DBI Version 1.42
Can't set DBI::db=HASH(0x8fd1834)-{User}: unrecognised attribute or
invalid value at //ms/dist/perl5/PROJ/DBI/1.42-5.8/lib/perl5/DBI.pm line
634, DATA chunk 1.
NOTICE: If received in error, please destroy and notify sender. Sender does
not waive confidentiality or privilege, and use is prohibited.
Newbie Question: DBI and MySQL Connection Issues
Hi I am using DBI to connect to MySQL. However I am getting the following error: Couldn't connect to database: Client does not support authentication protocol requested by server; consider upgrading MySQL client when DBI tries to connect to the database. I've read about the solution which requires changing the password to an old-style password. However I was wondering if there was a cleaner way to do this. Any help will be greatly appreciated. Thanks, Qaseem __ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs
Re: [PATCH] DBD::NullP
Thanks, applied.
Tim.
On Thu, Mar 24, 2005 at 06:46:42PM +0300, Sergey Skvortsov wrote:
--- NullP.pm.orig 2005-03-24 13:17:24.09375 +0300
+++ NullP.pm 2005-03-24 13:17:29.265625000 +0300
@@ -72,7 +72,7 @@
# else pass up to DBI to handle
if ($attrib eq 'AutoCommit') {
return 1 if $value; # is already set
- croak(Can't disable AutoCommit);
+ Carp::croak(Can't disable AutoCommit);
}
return $dbh-SUPER::STORE($attrib, $value);
}
--
Sergey Skvortsov
mailto: [EMAIL PROTECTED]
Re: Bug in $dbh-clone
The 'bug' is that your driver is setting an attribute that's not valid.
The official attribute is called Username not User.
(But it's not really fair to call it a driver bug as many drivers
do that because they simply copied early drivers that did that.)
Tim.
On Thu, Mar 24, 2005 at 11:55:04AM -0500, Anderson, James H (Company IT) wrote:
DBI Version 1.42
Can't set DBI::db=HASH(0x8fd1834)-{User}: unrecognised attribute or
invalid value at //ms/dist/perl5/PROJ/DBI/1.42-5.8/lib/perl5/DBI.pm line
634, DATA chunk 1.
NOTICE: If received in error, please destroy and notify sender. Sender does
not waive confidentiality or privilege, and use is prohibited.
Re: Bug in $dbh-clone
On Thu, 2005-03-24 at 18:30, Tim Bunce wrote:
The 'bug' is that your driver is setting an attribute that's not valid.
The official attribute is called Username not User.
(But it's not really fair to call it a driver bug as many drivers
do that because they simply copied early drivers that did that.)
Indeed :-)
I'll fix it for the next release.
Michael
On Thu, Mar 24, 2005 at 11:55:04AM -0500, Anderson, James H (Company IT)
wrote:
DBI Version 1.42
Can't set DBI::db=HASH(0x8fd1834)-{User}: unrecognised attribute or
invalid value at //ms/dist/perl5/PROJ/DBI/1.42-5.8/lib/perl5/DBI.pm line
634, DATA chunk 1.
NOTICE: If received in error, please destroy and notify sender. Sender
does not waive confidentiality or privilege, and use is prohibited.
--
Michael Peppler - [EMAIL PROTECTED] - http://www.peppler.org/
Sybase DBA/Developer
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html
compiling under windows and question mark in fields
Hey list folks, Does anyone have a good pointer on what's required to compile DBI under Windows (using the ActiveState base)? And, while I submitted a bug fix req quite a while ago and had it fixed for *nix (the ability to use question marks in field names primarily by hacking the back-tick operator as a quoting character) I was wondering if anyone came across said problem under Windows and managed to solve it... thx...
RE: compiling under windows and question mark in fields
Does anyone have a good pointer on what's required to compile DBI under Windows (using the ActiveState base)? The general rule is you need the same compiler that was used to compile the Perl you're using. For ActiveState, that's Microsoft Visual C++. Other than that, there's not much in the way of prerequisites. This mail reminded me to upgrade to 1.48 on this machine, and all I needed to do was open a Visual Studio .NET command prompt (i.e. just the shortcut to a normal command prompt but it runs vcvars32.bat) and run cpan DBI which fetches, compiles and installs it. And, while I submitted a bug fix req quite a while ago and had it fixed for *nix (the ability to use question marks in field names primarily by hacking the back-tick operator as a quoting character) I was wondering if anyone came across said problem under Windows and managed to solve it... Can't say I've ever thought about having question marks in field names other than with a sense of horror ;-) but shouldn't that be down to the DBD and database's native quoted-identifier syntax (i.e. double quotes as standard, or backticks on MySQL, and doesn't Access use square brackets?) rather than up in the DBI level? -- Andy Hassall [EMAIL PROTECTED] / Space: disk usage analysis tool http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space
Re: Newbie Question: DBI and MySQL Connection Issues
On Thu, 24 Mar 2005 08:17:35 -0800 (PST), Qaseem Shaikh wrote: Hi Qaseem Couldn't connect to database: Client does not support authentication protocol requested by server; consider upgrading MySQL client And when you googled for the error msg, it took you to this page, right? http://dev.mysql.com/doc/mysql/en/Old_client.html -- Cheers Ron Savage, [EMAIL PROTECTED] on 25/03/2005 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
Sending Mail
Hi All,
Finally, a newsgroup where I haven't got a clue (most of the time) what you
lot are talking about!
Anyway, above statement proving my newbie status so I need help with :
Retrieve email addresses from 'email_address' field in 'users' database.
Send out email from email.txt file to all those addresses.
Seems simple , and I can do it no problem by reteiving the email addresses
from another addresses.txt file
but not from the database. It is obviously the syntax that is wrong, but
being new to perl I can't figure
it out.
Can someone point me as to what is wrong with this :-
#!/usr/bin/perl
use CGI::Carp qw(fatalsToBrowser);
use CGI qw(:standard);
use DBI;
$|=1;
open(MESSAGE, /home/site/public_html/cgi-bin/email.txt) or die cannot
open message: $!;
flock(MESSAGE, 2) or die cannot lock message exclusively: $!;
my @msgfile = MESSAGE;
close (MESSAGE) or die cannot close message file: $!;
my ($dbh, $sth, @row);
$dbh = open_dbi();
my $sth = $dbh-prepare(SELECT email_address FROM users);
$sth-execute();
while(@row = $sth-fetchrow_array){
$address = $row[0];
chomp($address);
open (SENDMAIL, |/usr/sbin/sendmail -t) or die cannot open sendmail: $!;
print SENDMAIL To:$address\n;
print SENDMAIL From: [EMAIL PROTECTED];
print SENDMAIL Subject: ITgazette $address\n\n;
print SENDMAIL @msgfile;
}
sub open_dbi
{
my $host = 'localhost';
my $db = 'databasename';
my $db_user = 'username';
my $db_password = 'password';
my $dbh = DBI-connect(dbi:mysql:$db:$host, $db_user, $db_password,
{RaiseError = 0, PrintError = 0} )
or err_trap(Cannot connect to the database);
return $dbh;
}# end: open_dbi
sub err_trap
{
my $error_message = shift(@_);
die $error_message\n
ERROR: $DBI::err ($DBI::errstr)\n;
}# end: err_trap
$sth-finish;
$dbh-disconnect();
print Location: http://sitename/index.php\n\n;;
---
It does not produce an error message, it just does not send the emails
out,but it does redirect to the location.
Thanks for your help,
Gav...
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.1 - Release Date: 23/03/2005
Re: Sending Mail
On 03/24/2005 05:31 PM, Gav said:
Can someone point me as to what is wrong with this :-
#!/usr/bin/perl
use CGI::Carp qw(fatalsToBrowser);
use CGI qw(:standard);
use DBI;
$|=1;
open(MESSAGE, /home/site/public_html/cgi-bin/email.txt) or die cannot
open message: $!;
flock(MESSAGE, 2) or die cannot lock message exclusively: $!;
my @msgfile = MESSAGE;
close (MESSAGE) or die cannot close message file: $!;
I don't think you need to lock the file to read it, especially since you
are closing it so quickly. It might make some sense if the file is
updated frequently by other processes.
my ($dbh, $sth, @row);
$dbh = open_dbi();
my $sth = $dbh-prepare(SELECT email_address FROM users);
$sth-execute();
while(@row = $sth-fetchrow_array){
Since you aren't checking for errors in any of the three lines above,
you haven't a clue whether an error occured or not.
I would either set RaiseError or do something like this:
my $sth = $dbh-prepare(SELECT email_address FROM users)
or die prepare() failed, $DBI::errstr\n;
$sth-execute();
or die execute() failed, $DBI::errstr\n;
while(@row = $sth-fetchrow_array){
...
}
die fetchrow_array() failed, $DBI::errstr\n if $DBI::err;
$address = $row[0];
chomp($address);
The chomp() shouldn't be needed for values stored in the database.
open (SENDMAIL, |/usr/sbin/sendmail -t) or die cannot open sendmail: $!;
print SENDMAIL To:$address\n;
print SENDMAIL From: [EMAIL PROTECTED];
print SENDMAIL Subject: ITgazette $address\n\n;
print SENDMAIL @msgfile;
}
sub open_dbi
{
my $host = 'localhost';
my $db = 'databasename';
my $db_user = 'username';
my $db_password = 'password';
my $dbh = DBI-connect(dbi:mysql:$db:$host, $db_user, $db_password,
{RaiseError = 0, PrintError = 0} )
or err_trap(Cannot connect to the database);
If you are going to disable error reporting when you connect, you need
to explicitly check for errors at nearly every DBI method call. You
would probably be better off with {RaiseError = 1}.
http://search.cpan.org/~timb/DBI/DBI.pm#Simple_Examples
I don't find your DSN syntax in the DBD::mysql manual, but since this is
the one place where you're checking for errors, I guess it is working.
http://search.cpan.org/~rudy/DBD-mysql/lib/DBD/mysql.pm
return $dbh;
}# end: open_dbi
sub err_trap
{
my $error_message = shift(@_);
die $error_message\n
ERROR: $DBI::err ($DBI::errstr)\n;
}# end: err_trap
$sth-finish;
$dbh-disconnect();
print Location: http://sitename/index.php\n\n;;
---
It does not produce an error message, it just does not send the emails
out,but it does redirect to the location.
You are getting an error somewhere, but you aren't checking for errors
except in the connect().
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
