New info on an old Thread: Selecting a record from a table where a column might be null

[CAMPBELL, BRIAN D (BRIAN)]

I recently encountered some new info to add to an old thread to which I contributed.

 

Old Thread

 

Briefly I responded to a query someone made about generalizing WHERE clauses so that 
you could use either NULL or non-NULL values to bind to parameterized ?'s.  In other 
words, you construct a WHERE clause, prepare the statement once, then do multiple 
executes, and having the flexibility to bind either NULL or NON-null data each time. 

 

Background links (from April and May):

 

http://www.mail-archive.com/[EMAIL PROTECTED]/msg21050.html 
 

http://www.mail-archive.com/[EMAIL PROTECTED]/msg21053.html 
 

http://www.mail-archive.com/[EMAIL PROTECTED]/msg21082.html 
 

 

Several solutions were offered, but they appeared to use non-ANSI features, as far as 
I could tell.  Correct me if I'm wrong.  For example:

col = ?   (works only if your database supports COL = NULL)

NVL()

ISNULL(?, col)

col = ? OR (col IS NULL and ? IS NULL)

 

I don't believe I could use any of them with my Database (Informix) nor was I aware of 
any other solution for Informix.  My only recourse then was to create unique SQL and 
prepare it for each execute (customizing each SQL match to be either "col=value",  or 
"col IS NULL").

 

New Info

 

Now I am aware of two solutions that do work with Informix (and probably other 
databases as well).  So I thought I would take the liberty to share this info; perhaps 
it will help someone else.  These solutions are variations of the last example above, 
for which you have to bind a pair of values for each "nulls allowed" column in the 
where clause.

 

1. COL = ? OR (COL IS NULL AND ? = 1)

Here you bind a value (e.g. $val) and then a constant 1 or not 1 (e.g. 
defined($val)? 0 : 1).  This depends on the fact that constants can appear on the left 
side of "=" (whereas they can't in front of IS NULL).

 

2. COL = ? OR (COL IS NULL AND SP_ISNULL(?) = 1)

Here you bind the same value to both ?'s (e.g. $val, $val).  This depends on the 
ability to create a stored procedure SP_NULL, which can be defined to accept a NULL or 
NON-NULL value, test its NULL-ness, and then return 1 or 0.

 

Real Examples: 

 

1.  First approach:

 

my $emp1_sql = execute('D%', $mail_code, defined($mail_code)?0:1);

show_data($sth1);

  }

 

2.  Second approach:

 

my $emp2_sql = execute('D%', $mail_code, $mail_code);

show_data($sth2);

  }

 

 

FYI, here's where these approaches came from.  I've been working with ADO.NET 
recently, and made some observations.  In this data access model, data providers 
(analogous to DBDs in the DBI model, e.g. Oracle, Informix, MSSQL, etc.) are supposed 
to implement a family of classes, including one called a Command Builder.  This class 
takes a table and automatically generates a triplet of commands that support row 
changes (INSERT, DELETE, and UPDATE).  In order to help identify rows in the database 
with the original data, DELETE and UPDATE have WHERE clauses that include all columns, 
and for "nulls allowed" columns, must support one of the solutions above.  The intent 
of the command triplet is to support optimistic concurrency.  I could go on, but the 
main point is that the MSSQL provider uses the first approach in its Command Builder 
class.  But the technique works on Informix too.  The Informix provider actually uses 
the 2nd approach in its Command Builder class.

 

Re: DBD::mysql and mysql_use_result

[Patrick Galbraith]

On Aug 9, 2004, at 8:01 PM, Rudy Lippan wrote:
On Sat, 7 Aug 2004, Patrick Galbraith wrote:
As far as use_result vs. store result, the server prepared statements
(mysql 4.1 and greater) will always use 'store result', as this has no
affect on performance as per the API documentation.
I plan, if possible, to make "use result" the default with prepared 
statements,
my thinking here is this: if you are going to be using parepared 
statements, you
will more than likly be running in a persistant envionment, in which 
case,
memory usage becomes more of an issue. esp. in something like a 
mod_perl
envionment because when you use "store result" you will more than 
likely use 2x
the memory (or more) -- Think of an application that munges the result 
result
set and pases the munged data off to a templating system for a total 
of three
times memory usage of just the result set.

Rudy,
Hmm... I can see that point. Is there something similar to this in 
other database drivers? What setting do they default to? I would like 
to bring this up too with the fellow who implemented the server prepare 
statements in the server and see what he thinks.


And from my reading of the docs (though I have not had a chance to 
test this
yet), mysql_stmt_store_result() need not be called mysql_stmt_fetch(). 
Is this
the case?

Yes, an I just tested this with the latest code I've worked on, and it 
works without store_result. I'm curious to see what my benchmarking 
will show ;)

As far as the question about use_result, I'll have to test this.
regards,
Patrick

Rudy
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Patrick Galbraith Senior Software Developer
[EMAIL PROTECTED] http://www.mysql.com
"Whatever action a great man performs, common men follow. Whatever 
standards he sets by exemplary acts, all the world pursues"  -- 
Bhagavad Gita

Re: DBD::PgPP and PG8 beta

[Jeff Zucker]

Greg Sabino Mullane wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
 
 

Well it has finally happened. PG is native on Windows. Does anyone know if
DBD::PgPP will work with it or do I need to wait for an updated DBD?
 
DBD::Pg will work just fine against 8.0, although the soon-to-be-released
version (1.33) will have much more support for some of its features. As
far as DBD::PgPP I cannot say, but PG strives to be backwards-compatible,
so I doubt anything would prevent it from working against 8.0.
Thanks for the valuable info, Greg.
Could you also tell us a bit about the issue of installation on windows 
- will DBD::Pg be able to find the libraries it needs and compile on 
windows?  will there also be binary versions available for download? 
Many people are using DBD::PgPP on windows because of difficulties 
compiling DBD::Pg so the whole compile/install issue is probably more 
relevant for windows users than exactly which features it supports once 
working.

--
Jeff

Re: DBD::PgPP and PG8 beta

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
 
 
> Well it has finally happened. PG is native on Windows. Does anyone know if
> DBD::PgPP will work with it or do I need to wait for an updated DBD?
 
DBD::Pg will work just fine against 8.0, although the soon-to-be-released
version (1.33) will have much more support for some of its features. As
far as DBD::PgPP I cannot say, but PG strives to be backwards-compatible,
so I doubt anything would prevent it from working against 8.0.
 
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200408172017
 
-BEGIN PGP SIGNATURE-
 
iD8DBQFBIqAnvJuQZxSWSsgRAjfKAKDNPJAFdBxE5iBcQwviDfrLfX8gsACg+OXF
lmS/o13R/1y4MJJxeJ98iDA=
=ZP9b
-END PGP SIGNATURE-

RE: DBI, Oracle and Pro*C

[Reidy, Ron]

OCI::Oracle

--
Ron Reidy
Sr DBA
Array BioPharma, Inc


-Original Message-
From:   Ian Harisay [mailto:[EMAIL PROTECTED]
Sent:   Tue 8/17/2004 12:26 PM
To: [EMAIL PROTECTED]
Cc: 
Subject:DBI, Oracle and Pro*C
I'm working on a PERL project at work, but my boss wants me to do just
enough in Pro*C that it won't integrate with my PERL designs.

Is anyone aware of any PERL/C extentions that allow Pro*C functions to
be called by PERL?

Thanks,

Ian




This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended 
to be for the use of the individual or entity named above. If you are not the 
intended recipient, please be aware that any disclosure, copying, distribution 
or use of the contents of this information is prohibited. Please notify the
sender  of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: DBD::ADO connect error...

[amonotod]

> From: amonotod <[EMAIL PROTECTED]>
> Date: 2004/08/17 Tue PM 08:25:11 GMT
>
> Use of uninitialized value in subroutine entry at C:/Perl/site/lib/DBI.pm line 595.
> DBD::ADO::dr connect warning:  at C:/Perl/site/lib/DBI.pm line 595.

Sorry, I didn't mean to forget:
OS:   Windows 2000, SP4
Perl: ActiveState v5.8.3 built for MSWin32-x86-multi-thread, build 809
DBI:  1.43, built on 11 Aug 2004 
ADO:  $VERSION = '2.76';




--

`\|||/ amonotod@| sun|perl|windows
  (@@) charter.net  | sysadmin|dba
  ooO_(_)_Ooo
  _|_|_|_|_|_|_|_|

DBD::ADO connect error...

[amonotod]

Howdy,
  I have a script (attached) that creates an Access 2K database, 
connects to it, creates a table, and pops some data into it.  It all 
works, no problem.  However, I'm getting the following messages 
on the console (CMD prompt):

Use of uninitialized value in subroutine entry at C:/Perl/site/lib/DBI.pm line 595.
DBD::ADO::dr connect warning:  at C:/Perl/site/lib/DBI.pm line 595.

  I'd like to know how to get rid of the messages...  Do I need an 
error_handler for DBD::ADO?  Does it even support one?

Thanks,
amonotod


--

`\|||/ amonotod@| sun|perl|windows
  (@@) charter.net  | sysadmin|dba
  ooO_(_)_Ooo
  _|_|_|_|_|_|_|_|


ADO_Execute.pl
Description: Binary data

Re: Oracle connection question

[Robert]

"Tim Bunce" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Tue, Aug 17, 2004 at 10:24:00AM -0400, Robert wrote:
> > In the DBD::Oracle docs it states this:
> >
> > ==
> > "If you use the host=$host;sid=$sid style syntax, for example:
> >   $dbh = DBI->connect("dbi:Oracle:host=myhost.com;sid=ORCL", $user,
> > $passwd);then DBD::Oracle will construct a full connection descriptor
string
> > for you and Oracle will not need to consult the tnsname.ora file.
> >
> > If a port number is not specified then the descriptor will try both 1526
and
> > 1521 in that order (e.g., new then old). You can check which port(s) are
in
> > use by typing ``$ORACLE_HOME/bin/lsnrctl stat'' on the server."
> >
> > ===
> >
> > Does that mean I do not have to have Oracle client installed?
>
> No.
>
> > Or I do have to have the client installed because of libraries needed
> > but I don't have to have a tsnames.ora file?
>
> Yes.
>
> Tim.
>
Thanks! I was thinking "thin" type Java connection and it was messing with
my mind. Java tends to do that to me.

Robert

RE: Trouble with bind variables

[amonotod]

> From: "Bart Kersteter" <[EMAIL PROTECTED]>
> Date: 2004/08/17 Tue PM 03:06:53 GMT
> 
> my $db_list = new IO::File("< /home/oracle/utils/metrics_db_list.txt")
> or die "Can't open oratab file";
> 
> while (<$db_list>) {
>my @db_info = split(/:/);
>my $orasid = $db_info[0];
>my $server = $db_info[1];

May I suggest DBI:CSV?

amonotod


--

`\|||/ amonotod@| sun|perl|windows
  (@@) charter.net  | sysadmin|dba
  ooO_(_)_Ooo
  _|_|_|_|_|_|_|_|

DBI, Oracle and Pro*C

[Ian Harisay]

I'm working on a PERL project at work, but my boss wants me to do just
enough in Pro*C that it won't integrate with my PERL designs.

Is anyone aware of any PERL/C extentions that allow Pro*C functions to
be called by PERL?

Thanks,

Ian

[DBI] RE: Trouble with bind variables

[Dave Cash]

On Tue, 17 Aug 2004, Bart Kersteter wrote:

> Thanks to all for your suggestions... I've finally gotten things
> working.  The following is a working example.  The one issue I
> have left to handle is to add some error chceking and parameterize
> a few things to make the script more transportable.  One little
> glitch is that the final fetch for the source handler appears to
> be an empty set, and I'm getting the following error even with the
> check in place to see if userid is empty:
>
> Use of uninitialized value in string eq at ./collect_user_metrics.pl line 62,  
> line 1.

This doesn't look like an error; it looks like a warning.  Is making
your script die?  Is line 62 the line where you check for $userid?

> *--
> use strict;
> use DBI;
> use IO;
> use POSIX qw(uname);
>
> my $db_list = new IO::File("< /home/oracle/utils/metrics_db_list.txt") or die "Can't 
> open oratab file";
>
> my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", 'metrics', 'metr1cs',
>   {RaiseError => 1, AutoCommit => 1}) || die DBI->errstr;
>
> my $insert_sql = qq{ insert into user_history_stats (database, server, year, week, 
> username, cpu, io)
> values (?, ?, ?, ?, ?, ?, ?) };
>
> my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't prepare insert 
> statement: " . $target_dbh->errstr;;
>
>
> while (<$db_list>) {
>my @db_info = split(/:/);
>my $orasid = $db_info[0];
>my $server = $db_info[1];
>my $source_dbh = DBI->connect( "dbi:Oracle:$orasid", 'metrics', 'metr1cs',
>  {RaiseError => 1, AutoCommit => 0}) || die DBI->errstr;
>
>my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", 'metrics', 'metr1cs',
>   {RaiseError => 1, AutoCommit => 0}) || die DBI->errstr;

I think you forgot to excise the above line when you rewrote this.

>#  set up bind variables
>
>my $source_sql = qq{ SELECT username,  to_char(sample_Date, '') ,  
> to_char(sample_date, 'IW') , sum(cpu) , sum(IO)
>   from daily_user_stats
>   group by username, to_CHAR(sample_date, 'IW') , 
> to_char(sample_Date, '')
>   order by 1,2 };
>
>my $source_sth = $source_dbh->prepare( $source_sql ) or die "Can't prepare source 
> statement: " . $source_dbh->errstr;
>$source_sth->execute;

I'm sure this is one of the error-checking things you were talking
about, but the above should probably be written:

$source_sth->execute or die "Can't execute source statement: " . 
$source_dbh->errstr;

>my ($userid, $year, $week, $cpu, $io);
>$source_sth->bind_columns( \$userid, \$year, \$week, \$cpu, \$io );
>
>while ($source_sth->fetch) {
>   if ( not $userid eq "" ) {

This could be written more simply as

if ( $userid ne '' ) {

or even

if ( $userid ) {

>   $target_sth->execute($orasid, $server,  $year, $week, $userid, $cpu, $io);
>   }
>}
>
># Clean up and exit loop
>$target_dbh->commit();
>$source_dbh->disconnect();
> }
>
> $target_dbh->disconnect();

Take care,

Dave

/L\_/E\_/A\_/R\_/N\_/T\_/E\_/A\_/C\_/H\_/L\_/E\_/A\_/R\_/N\
Dave Cash  Power to the People!
Frolicking in Fields of Garlic   Right On-Line!
[EMAIL PROTECTED]  Dig it all.

MacOSX DBD::Oracle install problems with libclntsh

[Tim Barrass]

Hi all- hopefully I'm not just repeating a problem everyone knows about, had
a search but not found much- anyhow:

Installing DBD::Oracle fails for me on generating the Makefile- it's related
to some manual modification of libclntsh required, which I believe I've done
correctly. Session and output below, here are my details:

Darwin Kernel Version 7.5.0 (Mac OSX 10.3.5)

Perl v5.8.1-RC3 built for darwin-thread-multi-2level

Oracle client runtime installation
10g Release 1 (10.1.0.3) for Apple MAC OS X

DBD::Oracle 1.15

Feel free to reply tersely with a pointer to the solution if necessary..

Many thanks,
Tim





$ export $ORACLE_HOME=/Users/oracle/OraHome_1/
$ cd /sw/lib/perl5/5.8.1/DBD-Oracle-1.15
$ nmedit -R ./hints/macos_lib.syms
/Users/oracle/OraHome_1/lib/libclntsh.dylib.10.1

medit: symbols names listed in: ./hints/macos_lib.syms not in:
/Users/oracle/OraHome_1/lib/libclntsh.dylib.10.1
_dlclose
_dlsym
_poll

$ perl Makefile.PL 
Using DBI 1.43 (for perl 5.008001 on darwin-thread-multi-2level)
installed in /Library/Perl/5.8.1/darwin-thread-multi-2level/auto/DBI

 Configuring DBD::Oracle ...

>>> Remember to actually *READ* the README file!
Especially if you have any problems.

Using Oracle in /Users/oracle/OraHome_1/

WARNING: could not decode oracle version from
/Users/oracle/OraHome_1//orainst/inspdver, or
/Users/oracle/OraHome_1//install/unix.rgs
or from ORACLE_HOME path /Users/oracle/OraHome_1/.
Oracle version based logic in Makefile.PL may produce erroneous results.

ERROR: symbol table needs modification in Oracle library:
/Users/oracle/OraHome_1//lib/libclntsh.dylib
Manual modification required - see README.macosx

RE: last chance for perl:dbi

[Jeff Urlwin]

Taken of dbi-dev list as that's not the appropriate place.
> 
> 
> I got somethink new, 
> a hint whats the fault with my odbc-driver. 
> The parser stops at:
> typedef void FAR* PTR;
> 
> ( /REFDB_HOME/referenz/adabas_d/include/isql.h:10: error: 
> parse error before 
> '*' token
> /REFDB_HOME/referenz/adabas_d/include/isql.h:10: warning: 
> data definition has 
> no type or storage class )
> 
> FAR is a hangover of 16bit times, and my gcc don't know it. 
> Do I have to hack it manualy into the code, or should I 
> include another 
> header which visualise this type? 
> 
> Is this somethink for the [EMAIL PROTECTED] - list?

No -- 

It looks like you are trying to build DBD::Adabas.  What database are you
really using?  It may make sense to use DBD::ODBC...DBD::ODBC builds on both
Unix and Windows.  I'm sure it works with UnixODBC and *should* work with
various versions of iODBC, but iODBC is not something I test with on a
regular basis.

Regards,

Jeff

Re: [DBI] RE: Trouble with bind variables

[Bart Kersteter]

Thanks to all for your suggestions... I've finally gotten things working.  The 
following is a working example.  The one issue I have left to handle is to add some 
error chceking and parameterize a few things to make the script more transportable.  
One little glitch is that the final fetch for the source handler appears to be an 
empty set, and I'm getting the following error even with the check in place to see if 
userid is empty:

Use of uninitialized value in string eq at ./collect_user_metrics.pl line 62,  
line 1.


Thanks again for your help and suggestions.

Bart



*--
use strict;
use DBI;
use IO;
use POSIX qw(uname);

my $db_list = new IO::File("< /home/oracle/utils/metrics_db_list.txt") or die "Can't 
open oratab file";

my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", 'metrics', 'metr1cs',
  {RaiseError => 1, AutoCommit => 1}) || die DBI->errstr;

my $insert_sql = qq{ insert into user_history_stats (database, server, year, week, 
username, cpu, io)
values (?, ?, ?, ?, ?, ?, ?) };

my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't prepare insert 
statement: " . $target_dbh->errstr;;


while (<$db_list>) {
   my @db_info = split(/:/);
   my $orasid = $db_info[0];
   my $server = $db_info[1];
   my $source_dbh = DBI->connect( "dbi:Oracle:$orasid", 'metrics', 'metr1cs',
 {RaiseError => 1, AutoCommit => 0}) || die DBI->errstr;

   my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", 'metrics', 'metr1cs',
  {RaiseError => 1, AutoCommit => 0}) || die DBI->errstr;

   #  set up bind variables

   my $source_sql = qq{ SELECT username,  to_char(sample_Date, '') ,  
to_char(sample_date, 'IW') , sum(cpu) , sum(IO)
  from daily_user_stats
  group by username, to_CHAR(sample_date, 'IW') , 
to_char(sample_Date, '')
  order by 1,2 };

   my $source_sth = $source_dbh->prepare( $source_sql ) or die "Can't prepare source 
statement: " . $source_dbh->errstr;
   $source_sth->execute;
   my ($userid, $year, $week, $cpu, $io);
   $source_sth->bind_columns( \$userid, \$year, \$week, \$cpu, \$io );
  
   while ($source_sth->fetch) {
  if ( not $userid eq "" ) {
  $target_sth->execute($orasid, $server,  $year, $week, $userid, $cpu, $io);
  }
   }

   # Clean up and exit loop
   $target_dbh->commit();
   $source_dbh->disconnect();
}

$target_dbh->disconnect();



Bart Kersteter

Senior DBA - Corporate Database
Assurant
576 Bielenberg Drive
Woodbury, MN 55125
[EMAIL PROTECTED]
(651) 361-5796


**
This e-mail message and all attachments transmitted with it may contain legally 
privileged and/or confidential information intended solely for the use of the 
addressee(s). If the reader of this message is not the intended recipient, you are 
hereby notified that any reading, dissemination, distribution, copying, forwarding or 
other use of this message or its attachments is strictly prohibited. If you have 
received this message in error, please notify the sender immediately and delete this 
message and all copies and backups thereof.

Thank you.
**

Re: possible problem with prepare_cached & LongReadLen

[Tim Bunce]

On Tue, Aug 10, 2004 at 12:01:48PM +0200, Leandro C. Hermida wrote:
> Hello,
> 
> I am not sure if this is a bug but I have used DBI and DBD::Oracle
> for a long time without any probllem and I am almost certain that
> something might be wrong.  Both modules are awesome and this is the
> first time that I have ever seen any unexpected behavior.  The issue:
> 
> 1) if I set $dbh->{LongReadLen} = 1000;
> 2) then I do my $sth = $dbh->prepare_cached('SELECT my_clob FROM table');
> ...
> 3) then after a while I change $dbh->{LongReadLen} = 5000;
> 4) and then I call the same old statement
>my $sth = $dbh->prepare_cached('SELECT my_clob FROM table');
> 
> Sometimes the $sth I get back was not prepared with the new 5000 LongReadLen
> but the old 1000 LongReadLen.  It checked out because after doing step 4
> I looked at $sth->{LongReadLen} and it was equal to 1000.  Its like the
> prepare_cached is not inheriting the \%attr it needs for the $sth from the $dbh.

A feature not a bug. See the DBI docs for how prepare_cached acts.

> From the documentation it says that prepare_cached will
> return the old cached $sth if the SQL statement and \%attr are exactly the
> same.  Is there a bug with prepare_cached not inheriting \%attr from
> the $dbh to the $sth?

The \%attr refers to the \%attr parameter of prepare_cached and not to
the current attribute settings of the handle. I'll clarify the docs.

Tim.

Re: DBD::mysql and mysql_use_result

[Tim Bunce]

On Fri, Aug 13, 2004 at 11:17:51PM +0100, Alan Burlison wrote:
> Tim Bunce wrote:
> 
> >Does the binary protocol allow the link to be used for other actions while
> >there are still rows being sent to the client?
> >
> >If not "use result" can't be the default as too much existing code would 
> >break.
> 
> My experementation would suggest that it doesn't - if you turn 
> use_result on with the existing driver than then try to do a nested 
> prepare/execute/fetch within the outer loop, to get an error - I can't 
> remember the exact wording, but the gist is 'You can't do that now'.

I thought so. Thanks for confirming it. (The standard workaround of using
multiple dbh applies.)

Tim.

Re: Oracle connection question

[Tim Bunce]

On Tue, Aug 17, 2004 at 10:24:00AM -0400, Robert wrote:
> In the DBD::Oracle docs it states this:
> 
> ==
> "If you use the host=$host;sid=$sid style syntax, for example:
>   $dbh = DBI->connect("dbi:Oracle:host=myhost.com;sid=ORCL", $user,
> $passwd);then DBD::Oracle will construct a full connection descriptor string
> for you and Oracle will not need to consult the tnsname.ora file.
> 
> If a port number is not specified then the descriptor will try both 1526 and
> 1521 in that order (e.g., new then old). You can check which port(s) are in
> use by typing ``$ORACLE_HOME/bin/lsnrctl stat'' on the server."
> 
> ===
> 
> Does that mean I do not have to have Oracle client installed?

No.

> Or I do have to have the client installed because of libraries needed
> but I don't have to have a tsnames.ora file?

Yes.

Tim.

Re: DBD driver modules

[Jeff Zucker]

Naim Haldeda wrote:
Hi,
Thank you very much for the explanations,
No problem.  In the future though, please reply to the mailing list 
rather than to an individual - that way more people can learn from the 
answers, and others are more likely to know about DBD::Oracle than I am.

I have 2 more questions if it's possible for you .
a) To be able to install DBD::Oracle module  in my own user
directory, Do I manually have to modify Makefile.PL  or automatically it 
will be installed in the same folder where I compile it ?
You need to add arguments when you call Makefile.PL, not  actually edit 
it.  See http://www.perldoc.com/perl5.8.4/pod/perlmodinstall.html for 
details.

b) Is DBI::PurePerl and DBD::Oracle free ? I red documents which it says 
to read README perl which I could not find it , Pleas can you make clearer.
I believe there are some restrictions on DBD::Oracle in terms of 
redistribution but those should be found in the DBD::Oracle 
documentation.  For the general perl license, see 
http://www.perldoc.com/perl5.8.4/README.html.

Good luck!
--
Jeff
 
Thanks again.
Naim.

*/Jeff Zucker <[EMAIL PROTECTED]>/* wrote:
Naim Haldeda wrote:
 > Hi,
 >
 > I'm trying to use pure perl emulation of the DBI internals but
without success.
 > I'm in AIX 5.2 and I can not install compiled version of standard
DBI since I've not addmin rights.
Compiled versions of modules don't require root access, they can be
installed in your own user directory unless your host has a policy
against it.
 > I've done the steps mentioned in
http://search.cpan.org/~timb/DBI-1.43/lib/DBI/PurePerl.pm
 > Can you please tell me what should I do next.?
 > Do I have to install an Oracle driver ?
DBI::PurePerl is mostly useful with pure perl drivers and that doesn't
include DBD::Oracle. You could use DBD::PgPP or DBD::mysqlPP or
DBD::AnyData. Or, perhaps you can use DBD::Proxy and connect to a
remote Oracle server. All of those options would work with
DBI::PurePerl.
 > Can I use pure perl emulation of DBI without installing an oracle
driver since I can not install an oracle driver ?
Only with a proxy to another place that has the driver.
 > Can I manually install Oracle driver i.e copy all the files I
need into destination (since I've not admin rights )?
No, but again, the module can be compiled and installed in your own
user
directory without needing special rights.
-- 
Jeff

Post your free ad now! *Yahoo! Canada Personals* 

Re: [DBI] RE: Trouble with bind variables

[Dave Cash]

On Tue, 17 Aug 2004, Bart Kersteter wrote:

> Here's the full main loop script, I'm still relatively new to DBI,
> so I may have messed things up a bit.  I have gone through the
> perldocs a few times.

Your understanding of DBI looks pretty good to me.  I'm not sure
what's causing the bound variable problems, but I have a few
suggestions below to improve the performance of your code.

> my $db_list = new IO::File("< /home/oracle/utils/metrics_db_list.txt")
> or die "Can't open oratab file";
>
> while (<$db_list>) {
>my @db_info = split(/:/);
>my $orasid = $db_info[0];
>my $server = $db_info[1];
>my $source_dbh = DBI->connect( "dbi:Oracle:$orasid", '',
> ',
>  {RaiseError => 1, AutoCommit => 0}) || die DBI->errstr;
>
>my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", '',
> '',
>   {RaiseError => 1, AutoCommit => 0}) || die
> DBI->errstr;

It doesn't look to me like you need to reconnect to this database
each time through the loop.  I think the above statement should come
right before this loop.

>my $source_sql = qq{ SELECT username,  to_char(sample_Date, '')
> ,  to_char(sample_date, 'IW') , sum(cpu) , sum(IO)
> from daily_user_stats
> group by username, to_CHAR(sample_date, 'IW')
> , to_char(sample_Date, '')
>   order by 1,2 };
>
>my $source_sth = $source_dbh->prepare( $source_sql ) or die "Can't
> prepare source statement: " . $source_dbh->errstr;
>$source_sth->execute;
>my ($userid, $year, $week, $cpu, $io);
>$source_sth->bind_columns(\$userid, \$year, \$week, \$cpu, \$io);
>
>
>my $insert_sql = qq { insert into user_history_stats (database,
> server, year, week, username, cpu, io)
> values ('$orasid', '$server', ?, ?, ?, ?, ?)
> };
>
>my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't
> prepare insert statement: " . $target_dbh->errstr;;

Likewise, these two statements (the 'my $insert_sql = ...' and the
'my $target_sth = ...') should follow the 'my $target_dbh = ...'
outside the loop.

>while ($source_sth->fetch) {
>   $target_sth->bind_param(1, $year);
>   $target_sth->bind_param(2, $week);
>   $target_sth->bind_param(3, $userid);
>   $target_sth->bind_param(4, $cpu);
>   $target_sth->bind_param(5, $io);
>
>   $target_sth->execute;
>
>}

I think this could be simplified to:

while ($source_sth->fetch) {
$target_sth->execute($year,$week,$userid,$cpu,$io);
}

># Clean up and exit loop
>$target_dbh->commit();
>$source_dbh->disconnect();
>$target_dbh->disconnect();

And you'll need to move the $target_dbh->disconnect out of the loop
as well, if you take my above suggestions.

> }

One suggestion to track down the bound variable problem: What do you
see when you print out $userid, $year, $week, $cpu and $io right
after the call to $source_sth->fetch at the top of your while loop?
Perhaps these variables aren't getting populated.  If this is the
case, perhaps you could try rewriting that loop another way, like
this:

while (my @info = $source_sth->fetchrow_array) {
$target_sth->execute(@info);
}

and lose the whole bind_columns approach altogether.  This approach
is also more easily extensible: all you have to do to include more
stats is to change your $source_sql and $insert_sql statements and
@info here will expand or contract accordingly.

Hope that helps.

Take care,

Dave

/L\_/E\_/A\_/R\_/N\_/T\_/E\_/A\_/C\_/H\_/L\_/E\_/A\_/R\_/N\
Dave Cash  Power to the People!
Frolicking in Fields of Garlic   Right On-Line!
[EMAIL PROTECTED]  Dig it all.

RE: Trouble with bind variables

[Hardy Merrill]

I don't see anything glaringly wrong, and in fact I don't know why it's
not working for you.  Maybe I'm just overlooking something, but why not
include the $orasid and $server as bind variables?  Like this:

   my $insert_sql = qq { insert into user_history_stats (database,
server, year, week, username, cpu, io)
values (?, ?, ?, ?, ?, ?, ?)
};

   my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't
prepare insert statement: " . $target_dbh->errstr;;
   
   while ($source_sth->fetch) {
  $target_sth->bind_param(1, $orasid);
  $target_sth->bind_param(2, $server);
  $target_sth->bind_param(3, $year);
  $target_sth->bind_param(4, $week);
  $target_sth->bind_param(5, $userid);
  $target_sth->bind_param(6, $cpu);
  $target_sth->bind_param(7, $io);

  $target_sth->execute;

   }

???

HTH.

Hardy Merrill




>>> "Bart Kersteter" <[EMAIL PROTECTED]> 08/17/04 11:06AM
>>>
Here's the full main loop script, I'm still relatively new to DBI, so
I
may have messed things up a bit.  I have gone through the perldocs a
few
times.


my $db_list = new IO::File("< /home/oracle/utils/metrics_db_list.txt")
or die "Can't open oratab file";

while (<$db_list>) {
   my @db_info = split(/:/);
   my $orasid = $db_info[0];
   my $server = $db_info[1];
   my $source_dbh = DBI->connect( "dbi:Oracle:$orasid", '',
',
 {RaiseError => 1, AutoCommit => 0}) || die DBI->errstr;

   my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", '',
'',
  {RaiseError => 1, AutoCommit => 0}) || die
DBI->errstr;

   my $source_sql = qq{ SELECT username,  to_char(sample_Date, '')
,  to_char(sample_date, 'IW') , sum(cpu) , sum(IO) 
  from daily_user_stats
  group by username, to_CHAR(sample_date, 'IW')
, to_char(sample_Date, '')
  order by 1,2 };

   my $source_sth = $source_dbh->prepare( $source_sql ) or die "Can't
prepare source statement: " . $source_dbh->errstr;
   $source_sth->execute;
   my ($userid, $year, $week, $cpu, $io);
   $source_sth->bind_columns(\$userid, \$year, \$week, \$cpu, \$io);

 
   my $insert_sql = qq { insert into user_history_stats (database,
server, year, week, username, cpu, io)
values ('$orasid', '$server', ?, ?, ?, ?, ?)
};

   my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't
prepare insert statement: " . $target_dbh->errstr;;
   
   while ($source_sth->fetch) {
  $target_sth->bind_param(1, $year);
  $target_sth->bind_param(2, $week);
  $target_sth->bind_param(3, $userid);
  $target_sth->bind_param(4, $cpu);
  $target_sth->bind_param(5, $io);

  $target_sth->execute;

   }

   # Clean up and exit loop
   $target_dbh->commit();
   $source_dbh->disconnect();
   $target_dbh->disconnect();
}


The literal 'FD1P' you referred to is the SID of one of my databases. 

Thanks,

Bart



Bart Kersteter

Senior DBA - Corporate Database
Assurant
576 Bielenberg Drive
Woodbury, MN 55125
[EMAIL PROTECTED] 
(651) 361-5796


**
This e-mail message and all attachments transmitted with it may contain
legally privileged and/or confidential information intended solely for
the use of the addressee(s). If the reader of this message is not the
intended recipient, you are hereby notified that any reading,
dissemination, distribution, copying, forwarding or other use of this
message or its attachments is strictly prohibited. If you have received
this message in error, please notify the sender immediately and delete
this message and all copies and backups thereof.

Thank you.
**

RE: Trouble with bind variables

[Bart Kersteter]

Here's the full main loop script, I'm still relatively new to DBI, so I
may have messed things up a bit.  I have gone through the perldocs a few
times.


my $db_list = new IO::File("< /home/oracle/utils/metrics_db_list.txt")
or die "Can't open oratab file";

while (<$db_list>) {
   my @db_info = split(/:/);
   my $orasid = $db_info[0];
   my $server = $db_info[1];
   my $source_dbh = DBI->connect( "dbi:Oracle:$orasid", '',
',
 {RaiseError => 1, AutoCommit => 0}) || die DBI->errstr;

   my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", '',
'',
  {RaiseError => 1, AutoCommit => 0}) || die
DBI->errstr;

   my $source_sql = qq{ SELECT username,  to_char(sample_Date, '')
,  to_char(sample_date, 'IW') , sum(cpu) , sum(IO) 
  from daily_user_stats
  group by username, to_CHAR(sample_date, 'IW')
, to_char(sample_Date, '')
  order by 1,2 };

   my $source_sth = $source_dbh->prepare( $source_sql ) or die "Can't
prepare source statement: " . $source_dbh->errstr;
   $source_sth->execute;
   my ($userid, $year, $week, $cpu, $io);
   $source_sth->bind_columns(\$userid, \$year, \$week, \$cpu, \$io);

 
   my $insert_sql = qq { insert into user_history_stats (database,
server, year, week, username, cpu, io)
values ('$orasid', '$server', ?, ?, ?, ?, ?)
};

   my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't
prepare insert statement: " . $target_dbh->errstr;;
   
   while ($source_sth->fetch) {
  $target_sth->bind_param(1, $year);
  $target_sth->bind_param(2, $week);
  $target_sth->bind_param(3, $userid);
  $target_sth->bind_param(4, $cpu);
  $target_sth->bind_param(5, $io);

  $target_sth->execute;

   }

   # Clean up and exit loop
   $target_dbh->commit();
   $source_dbh->disconnect();
   $target_dbh->disconnect();
}


The literal 'FD1P' you referred to is the SID of one of my databases. 

Thanks,

Bart



Bart Kersteter

Senior DBA - Corporate Database
Assurant
576 Bielenberg Drive
Woodbury, MN 55125
[EMAIL PROTECTED]
(651) 361-5796


**
This e-mail message and all attachments transmitted with it may contain legally 
privileged and/or confidential information intended solely for the use of the 
addressee(s). If the reader of this message is not the intended recipient, you are 
hereby notified that any reading, dissemination, distribution, copying, forwarding or 
other use of this message or its attachments is strictly prohibited. If you have 
received this message in error, please notify the sender immediately and delete this 
message and all copies and backups thereof.

Thank you.
**

Oracle connection question

[Robert]

In the DBD::Oracle docs it states this:

==
"If you use the host=$host;sid=$sid style syntax, for example:
  $dbh = DBI->connect("dbi:Oracle:host=myhost.com;sid=ORCL", $user,
$passwd);then DBD::Oracle will construct a full connection descriptor string
for you and Oracle will not need to consult the tnsname.ora file.

If a port number is not specified then the descriptor will try both 1526 and
1521 in that order (e.g., new then old). You can check which port(s) are in
use by typing ``$ORACLE_HOME/bin/lsnrctl stat'' on the server."

===

Does that mean I do not have to have Oracle client installed? Or I do have
to have the client installed because of libraries needed but I don't have to
have a tsnames.ora file?

Robert

RE: Trouble with bind variables

[Ronald J Kimball]

Bart Kersteter [mailto:[EMAIL PROTECTED] wrote:

> I am using this statement to try and insert the data:
> *---
> 
> my ($userid, $year, $week, $cpu, $io);
> 
> $source_sth->bind_columns(\$userid, \$year, \$week, \$cpu, \$io);
> 
> my $insert_sql = qq{ insert into user_history_stats (database, server,
> year, week, username, cpu, io)
> values ('$orasid', '$server', ?, ?, ?, ?, ?)
> };
> 
> my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't
> prepare insert statement: " . $target_dbh->errstr;;

Is this your actual code?  You're calling bind_columns() on $source_sth,
then preparing $target_sth.  Where do you bind the values to $target_sth?
What is $source_sth?

Why aren't you using placeholders for database and server?

Ronald

RE: Trouble with bind variables

[Reidy, Ron]

See below ...


-Original Message-
From:   Bart Kersteter [mailto:[EMAIL PROTECTED]
Sent:   Tue 8/17/2004 8:39 AM
To: [EMAIL PROTECTED]
Cc: 
Subject:Trouble with bind variables
Hello,

I am working on a small script that pulls user metrics from databases on a number of 
different servers and pushes them into a 'metrics warehouse' of sorts for use in trend 
analysis among other things.  I am having trouble with my insert statement and would 
like some help from all of you.

I am using this statement to try and insert the data:
*---

my ($userid, $year, $week, $cpu, $io);

$source_sth->bind_columns(\$userid, \$year, \$week, \$cpu, \$io);

my $insert_sql = qq{ insert into user_history_stats (database, server, year, week, 
username, cpu, io)
values ('$orasid', '$server', ?, ?, ?, ?, ?) };

This is not bind variables --^---^
So of course you must place single ticks ("'") around these vars.
  
my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't prepare insert 
statement: " . $target_dbh->errstr;;

*-


$orasid and $server are loaded from a text file that I loop through to get the list of 
databases that I'm pulling metrics from.

My problem is this:  When I put the single quotes around $orasid and $server, I get 
the following error:

*---

DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into 
("METRICS"."USER_HISTORY_STATS"."YEAR") (DBD ERROR: OCIStmtExecute) [for Statement " 
insert into user_history_stats (database, server, year, week, username, cpu, io)
values ('fd1p' , 'u1ecm301' , ?, ?, ?, ?, ?) " with 
ParamValues: :p4=undef, :p5=undef, :p1=undef, :p2=undef, :p3=undef] at 
./collect_user_metrics.pl line 64,  line 1.

All your vars are undef (NULL).  Are NULL values allowed in these columns?

DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into 
("METRICS"."USER_HISTORY_STATS"."YEAR") (DBD ERROR: OCIStmtExecute) [for Statement " 
insert into user_history_stats (database, server, year, week, username, cpu, io)
values ('fd1p' , 'u1ecm301' , ?, ?, ?, ?, ?) " with 
ParamValues: :p4=undef, :p5=undef, :p1=undef, :p2=undef, :p3=undef] at 
./collect_user_metrics.pl line 64,  line 1.

Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
*-

The Bind variables aren't being set properly for some reason.  If I take the single 
quotes out, I get this:  

*-
DBD::Oracle::st execute failed: ORA-00984: column not allowed here 
(DBD ERROR: error possibly near <*> indicator at char 121 in ' insert into 
user_history_stats (database, server, year, week, username, cpu, io)
values (fd1p , <*>u1ecm301 , :p1, :p2, :p3, :p4, :p5) ') [for 
Statement " insert into user_history_stats (database, server, year, week, username, 
cpu,io)
values (fd1p , u1ecm301 , ?, ?, ?, ?, ?) " with ParamValues: 
:p4='187.31', :p5='8', :p1='2004', :p2='16', :p3='AGDBA'] 
at ./collect_user_metrics.pl line 64,  line 1.

DBD::Oracle::st execute failed: ORA-00984: column not allowed here (DBD ERROR: error 
possibly near <*> indicator at char 121 in ' inse
rt into user_history_stats (database, server, year, week, username, cpu, io)
values (fd1p , <*>u1ecm301 , :p1, :p2, :p3, :p4, :p5) ') [for 
Statement " insert into user_history_stats (data
base, server, year, week, username, cpu, io)
values (fd1p , u1ecm301 , ?, ?, ?, ?, ?) " with ParamValues: 
:p4='187.31', :p5='8', :p1='2004', :p2='16', :p3=
'AGDBA'] at ./collect_user_metrics.pl line 64,  line 1.
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().

Of course this is an error.  What is the literal "fd1p"?

*--

As you can see here, the database is rightly rejecting the first two values for not 
being enclosed in quotes, but the bind variables are all set correctly. 

I have not been able to find the right way to load all of this data.  Can anyone see 
something I'm doing wrong, or perhaps suggest a better way to set up the INSERT 
statement?

Did you read the DBI docs?  It looks to me like your sequence of processing is not 
correct:

1.  Your call to bind_param() is wrong (looks like the wrong handle).
2.  Your call to execute() does not bind vars to the statement when called, nor does 
the statement handle have vars bound befor the call to exeute().



Thanks in advance,

You're welcome.

--
Ron Reidy
Sr DBA
Array BioPharma, Inc.


Bart





Bart Kersteter

Senior DBA - Corporate Database
Assur

Trouble with bind variables

[Bart Kersteter]

Hello,

I am working on a small script that pulls user metrics from databases on a number of 
different servers and pushes them into a 'metrics warehouse' of sorts for use in trend 
analysis among other things.  I am having trouble with my insert statement and would 
like some help from all of you.

I am using this statement to try and insert the data:
*---

my ($userid, $year, $week, $cpu, $io);

$source_sth->bind_columns(\$userid, \$year, \$week, \$cpu, \$io);

my $insert_sql = qq{ insert into user_history_stats (database, server, year, week, 
username, cpu, io)
values ('$orasid', '$server', ?, ?, ?, ?, ?) };
  
my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't prepare insert 
statement: " . $target_dbh->errstr;;

*-


$orasid and $server are loaded from a text file that I loop through to get the list of 
databases that I'm pulling metrics from.

My problem is this:  When I put the single quotes around $orasid and $server, I get 
the following error:

*---

DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into 
("METRICS"."USER_HISTORY_STATS"."YEAR") (DBD ERROR: OCIStmtExecute) [for Statement " 
insert into user_history_stats (database, server, year, week, username, cpu, io)
values ('fd1p' , 'u1ecm301' , ?, ?, ?, ?, ?) " with 
ParamValues: :p4=undef, :p5=undef, :p1=undef, :p2=undef, :p3=undef] at 
./collect_user_metrics.pl line 64,  line 1.

DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into 
("METRICS"."USER_HISTORY_STATS"."YEAR") (DBD ERROR: OCIStmtExecute) [for Statement " 
insert into user_history_stats (database, server, year, week, username, cpu, io)
values ('fd1p' , 'u1ecm301' , ?, ?, ?, ?, ?) " with 
ParamValues: :p4=undef, :p5=undef, :p1=undef, :p2=undef, :p3=undef] at 
./collect_user_metrics.pl line 64,  line 1.

Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
*-

The Bind variables aren't being set properly for some reason.  If I take the single 
quotes out, I get this:  

*-
DBD::Oracle::st execute failed: ORA-00984: column not allowed here 
(DBD ERROR: error possibly near <*> indicator at char 121 in ' insert into 
user_history_stats (database, server, year, week, username, cpu, io)
values (fd1p , <*>u1ecm301 , :p1, :p2, :p3, :p4, :p5) ') [for 
Statement " insert into user_history_stats (database, server, year, week, username, 
cpu,io)
values (fd1p , u1ecm301 , ?, ?, ?, ?, ?) " with ParamValues: 
:p4='187.31', :p5='8', :p1='2004', :p2='16', :p3='AGDBA'] 
at ./collect_user_metrics.pl line 64,  line 1.

DBD::Oracle::st execute failed: ORA-00984: column not allowed here (DBD ERROR: error 
possibly near <*> indicator at char 121 in ' inse
rt into user_history_stats (database, server, year, week, username, cpu, io)
values (fd1p , <*>u1ecm301 , :p1, :p2, :p3, :p4, :p5) ') [for 
Statement " insert into user_history_stats (data
base, server, year, week, username, cpu, io)
values (fd1p , u1ecm301 , ?, ?, ?, ?, ?) " with ParamValues: 
:p4='187.31', :p5='8', :p1='2004', :p2='16', :p3=
'AGDBA'] at ./collect_user_metrics.pl line 64,  line 1.
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().

*--

As you can see here, the database is rightly rejecting the first two values for not 
being enclosed in quotes, but the bind variables are all set correctly. 

I have not been able to find the right way to load all of this data.  Can anyone see 
something I'm doing wrong, or perhaps suggest a better way to set up the INSERT 
statement?

Thanks in advance,


Bart





Bart Kersteter

Senior DBA - Corporate Database
Assurant
576 Bielenberg Drive
Woodbury, MN 55125
[EMAIL PROTECTED]
(651) 361-5796

**
This e-mail message and all attachments transmitted with it may contain legally 
privileged and/or confidential information intended solely for the use of the 
addressee(s). If the reader of this message is not the intended recipient, you are 
hereby notified that any reading, dissemination, distribution, copying, forwarding or 
other use of this message or its attachments is strictly prohibited. If you have 
received this message in error, please notify the sender immediately and delete this 
message and all copies and backups thereof.

Thank you.
**

RE: Error while reading socket: Connection reset by peer at /usr/local/lib/perl5/site_perl/5.6.1/RPC/PlServer/Comm.pm line 110.

[Fenaughty, Kevin M, ALABS]

H.

Is the resolved in 5.8 ?

Perl 5.6.1 on Win32 leaks socket handles - maybe that's the reason.


Windows Server - $VERSION = '1.012';

Unix Machine - $VERSION = '2.08';

-Original Message-
From: Steffen Goeldner [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 17, 2004 8:58 AM
To: Jochen Wiedmann
Cc: [EMAIL PROTECTED]; Woodrow, Michael A, ALABS; Sackner, Beth A,
ALABS; Morello, Anthony, ALABS; Long, Dan, ALABS; Esser, Marc A, ALABS
Subject: Re: Error while reading socket: Connection reset by peer at
/usr/local/lib/perl5/site_perl/5.6.1/RPC/PlServer/Comm.pm line 110.


Jochen Wiedmann wrote:

>
> >  I'm having a problem utilizing the perl DBI::Proxy to go between a
> UNIX (Solaris)
> >  and a Windows SQL server. We started getting an error message
> >  "Error while reading socket: Connection reset by peer at
> >  /usr/local/lib/perl5/site_perl/5.6.1/RPC/PlServer/Comm.pm line
110."
> >  that I can't seem to resolve. Only solution is to restart the
windows
> service.
>
> The most possible reason I can think of, is different versions of the
> Storable module on both ends. If that is not the issue: Please note,
> that the DBD::Proxy module has got a new maintainer recently. (See the
> list archives of dbi-users.)

Perl 5.6.1 on Win32 leaks socket handles - maybe that's the reason.


Steffen

Re: last chance for perl:dbi

[Christian Stalp]

I got somethink new, 
a hint whats the fault with my odbc-driver. 
The parser stops at:
typedef void FAR*   PTR;

( /REFDB_HOME/referenz/adabas_d/include/isql.h:10: error: parse error before 
'*' token
/REFDB_HOME/referenz/adabas_d/include/isql.h:10: warning: data definition has 
no type or storage class )

FAR is a hangover of 16bit times, and my gcc don't know it. 
Do I have to hack it manualy into the code, or should I include another 
header which visualise this type? 

Is this somethink for the [EMAIL PROTECTED] - list?

Gruss Christian Stalp

Re: Error while reading socket: Connection reset by peer at /usr/local/lib/perl5/site_perl/5.6.1/RPC/PlServer/Comm.pm line 110.

[Steffen Goeldner]

Jochen Wiedmann wrote:

>
> >  I'm having a problem utilizing the perl DBI::Proxy to go between a
> UNIX (Solaris)
> >  and a Windows SQL server. We started getting an error message
> >  "Error while reading socket: Connection reset by peer at
> >  /usr/local/lib/perl5/site_perl/5.6.1/RPC/PlServer/Comm.pm line 110."
> >  that I can't seem to resolve. Only solution is to restart the windows
> service.
>
> The most possible reason I can think of, is different versions of the
> Storable module on both ends. If that is not the issue: Please note,
> that the DBD::Proxy module has got a new maintainer recently. (See the
> list archives of dbi-users.)

Perl 5.6.1 on Win32 leaks socket handles - maybe that's the reason.


Steffen

last chance for perl:dbi

[Christian Stalp]

Hello again,
I still have the problem to build a ODBC-Driver for Adabas. 
I got a last chance to manage it with perl, next week I have to do it with c.
Im sure this has something to do with the bad installation of Adabas, but the 
person who made this, is no longer available. So I have to fix it.  

My enviroment:
HPUX 10.20 on PA-RISC
Perl v5.8.3
DBI-1.42
DBD-ODBC-1.09

I send you the output of make and the first Header which cause a parse-error 
- isql.h

Makefile.PL made everything ok.

This is the output I got from make:

[imserv03]/home/STALP/DBD-ODBC-1.09 > make
/usr/local/bin/gcc -c -I/REFDB_HOME/referenz/adabas_d/include  -I. 
-I/usr/local/lib/perl5/5.8.3/PA-RISC2.0/auto/DBI  -D_HPUX_SOURCE 
-fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -O2
-DVERSION=\"1.09\"  -DXS_VERSION=\"1.09\" -fPIC 
"-I/usr/local/lib/perl5/5.8.3/PA-RISC2.0/CORE"  
-I/REFDB_HOME/referenz/adabas_d/include ODBC.c
In file included from /REFDB_HOME/referenz/adabas_d/include/isqlext.h:4,
 from dbdodbc.h:6,
 from ODBC.h:9,
 from ODBC.xs:1:
/REFDB_HOME/referenz/adabas_d/include/isql.h:10: error: parse error before 
'*' token
/REFDB_HOME/referenz/adabas_d/include/isql.h:10: warning: data definition has 
no type or storage class
/REFDB_HOME/referenz/adabas_d/include/isql.h:12: error: parse error before 
'*' token
/REFDB_HOME/referenz/adabas_d/include/isql.h:12: warning: data definition has 
no type or storage class
/REFDB_HOME/referenz/adabas_d/include/isql.h:13: error: parse error before 
'*' token
/REFDB_HOME/referenz/adabas_d/include/isql.h:13: warning: data definition has 
no type or storage class
/REFDB_HOME/referenz/adabas_d/include/isql.h:14: error: parse error before 
'*' token
/REFDB_HOME/referenz/adabas_d/include/isql.h:14: warning: data definition has 
no type or storage class
In file included from dbdodbc.h:6,
 from ODBC.h:9,
 from ODBC.xs:1:
/REFDB_HOME/referenz/adabas_d/include/isqlext.h:300: error: parse error 
before "CALLBACK"
/REFDB_HOME/referenz/adabas_d/include/isqlext.h:300: error: parse error 
before "UWORD"
/REFDB_HOME/referenz/adabas_d/include/isqlext.h:300: warning: data definition 
has no type or storage class
/REFDB_HOME/referenz/adabas_d/include/isqlext.h:301: error: parse error 
before "CALLBACK"
/REFDB_HOME/referenz/adabas_d/include/isqlext.h:301: error: parse error 
before "SWORD"
/REFDB_HOME/referenz/adabas_d/include/isqlext.h:301: warning: data definition 
has no type or storage class
In file included from /REFDB_HOME/referenz/adabas_d/include/sql.h:1,
 from dbdodbc.h:7,
 from ODBC.h:9,
 from ODBC.xs:1:
/REFDB_HOME/referenz/adabas_d/include/sqltypes.h:35:1: warning: "SQL_API" 
redefined
In file included from /REFDB_HOME/referenz/adabas_d/include/isqlext.h:4,
 from dbdodbc.h:6,
 from ODBC.h:9,
 from ODBC.xs:1:
/REFDB_HOME/referenz/adabas_d/include/isql.h:21:1: warning: this is the 
location of the previous definition
In file included from /REFDB_HOME/referenz/adabas_d/include/sql.h:1,
 from dbdodbc.h:7,
 from ODBC.h:9,
 from ODBC.xs:1:
/REFDB_HOME/referenz/adabas_d/include/sqltypes.h:106: error: redefinition of 
`UCHAR'
/REFDB_HOME/referenz/adabas_d/include/isql.h:4: error: `UCHAR' previously 
declared here
/REFDB_HOME/referenz/adabas_d/include/sqltypes.h:118: error: redefinition of 
`SDWORD'
/REFDB_HOME/referenz/adabas_d/include/isql.h:5: error: `SDWORD' previously 
declared here
/REFDB_HOME/referenz/adabas_d/include/sqltypes.h:122: error: redefinition of 
`SWORD'
/REFDB_HOME/referenz/adabas_d/include/isql.h:6: error: `SWORD' previously 
declared here
/REFDB_HOME/referenz/adabas_d/include/sqltypes.h:126: error: redefinition of 
`UDWORD'
/REFDB_HOME/referenz/adabas_d/include/isql.h:7: error: `UDWORD' previously 
declared here
/REFDB_HOME/referenz/adabas_d/include/sqltypes.h:130: error: redefinition of 
`UWORD'
/REFDB_HOME/referenz/adabas_d/include/isql.h:8: error: `UWORD' previously 
declared here
/REFDB_HOME/referenz/adabas_d/include/sqltypes.h:166: error: `PTR' redeclared 
as different kind of symbol
/REFDB_HOME/referenz/adabas_d/include/isql.h:10: error: previous declaration 
of `PTR'
/REFDB_HOME/referenz/adabas_d/include/sqltypes.h:168: error: `HENV' 
redeclared as different kind of symbol
/REFDB_HOME/referenz/adabas_d/include/isql.h:12: error: previous declaration 
of `HENV'
/REFDB_HOME/referenz/adabas_d/include/sqltypes.h:169: error: `HDBC' 
redeclared as different kind of symbol
/REFDB_HOME/referenz/adabas_d/include/isql.h:13: error: previous declaration 
of `HDBC'
/REFDB_HOME/referenz/adabas_d/include/sqltypes.h:170: error: `HSTMT' 
redeclared as different kind of symbol
/REFDB_HOME/referenz/adabas_d/include/isql.h:14: error: previous declaration 
of `HSTMT'
/REFDB_HOME/referenz/adabas_d/include/sqltypes.h:173: error: redefinition of 
`RETCODE'
/REF

Re: Error while reading socket: Connection reset by peer at /usr/local/lib/perl5/site_perl/5.6.1/RPC/PlServer/Comm.pm line 110.

[Jochen Wiedmann]

 I'm having a problem utilizing the perl DBI::Proxy to go between a 
UNIX (Solaris)
 and a Windows SQL server. We started getting an error message
 "Error while reading socket: Connection reset by peer at
 /usr/local/lib/perl5/site_perl/5.6.1/RPC/PlServer/Comm.pm line 110."
 that I can't seem to resolve. Only solution is to restart the windows 
service.
The most possible reason I can think of, is different versions of the 
Storable module on both ends. If that is not the issue: Please note, 
that the DBD::Proxy module has got a new maintainer recently. (See the 
list archives of dbi-users.)

Jochen