Re: [EXTERNAL] Re: DBI.pm - Memoryfault(coredump)

[David Nicol]

I wonder what the difference between "$sid" and "dbi:$db_driver:$db" is. If
nothing, it would look like Oracle::DBD might have some kind of weakness
regarding multiple active connections, if the fault happens later instead
of right at connect time, like on the second time through, with a different
value of $dbname.

On Wed, Oct 28, 2020 at 10:06 AM Srikantha  wrote:

>
> $connection_hash{$dbname} = DBI->connect( $sid, $user, $pass, {AutoCommit
> => $autocommit_flag}); -- Not working
> my $dbh = DBI->connect("dbi:$db_driver:$db", $user, $pass, { RaiseError =>
> 1, AutoCommit => 1 }) or die ("failed to login $db_user"); -- Working
>
-- 
If you are neutral in situations of injustice, you have chosen the side of
the oppressor." -- Bshp. Desmond Tutu

Re: DBI.pm - Memoryfault(coredump)

[David Nicol]

if the Oracle module is maintained by red hat, yum update should help. If
you built it in-house, you may need to yum update the perl development
environment and reinstall.

On Fri, Oct 23, 2020 at 11:27 PM Srikantha  wrote:

> Hello,
> We are having the below issue from one of the perl modules. We are trying
> to get Oracle query results via this perl package and that package is using
> DBI.pm.
> The perl script is executing end-end but at the end there is a
> segmentation fault error. We have recently upgraded our RHEL version and so
> perl got updated to v5.16.3 from v5.10.1. Below code was working fine
> with the older version.  Your help if any will be greatly appreciated.
> if I comment the code lines which is trying to get Oracle query results,
> then there is no Memory fault error. So I am pointing out issue to DBI.pm.
> And DBI version is 1.643.
>
> *Code snippet:*
>
> ${CODE_MOUNT_BASE}/scripts/audits/run_audit_dm_${TABLE}_${DATAMART}.pl
>
> exit_status=$?
>
> echo "exit status after pl call:  ${exit_status}"
>
>
>
> *Log message*:
>
> /u01/Projects/audits/scripts/run_audit_dm.ksh: line 12: 2613: Memory
> fault(coredump)
>
> exit status after pl call: 267
>
>
> *Regards*
> *Srikantha*
> *Mb-9980073640*
>


-- 
"You can be in my dream if I can be in yours." -- Bob Dylan

Re: Translate between DBI and SQL

[David Nicol]

Tie::Function can be used to bind $dbh->quote to a syntactical hash, so you
can interpolate arbitrary strings easier. When I do that I name the hash
%Q, and then it's safe to do things like

$sql_text = "select id from mytable where foo=$Q{$foo}";

rather than counting placeholders.



On Fri, Feb 8, 2019 at 4:37 PM Mike Martin  wrote:

> Has anyone done any work on converting SQL queries between RDBMS and perl?
>
> My particular interest is DBD::Pg but anything would be of use
>
> It would be very useful when I am testing complex SQL, it's very easy to
> miss a \ or quote between the two
>
> Thanks
> Mike
>
>

-- 
"I don't know about that, as it is outside of my area of expertise." --
competent specialized practitioners, all the time

Re: Fork DBD::mysql

[David Nicol]

It looks like this fork happened some time ago, and a DBD::maria is now
needed, to keep up. Is that not what it looks like?


-- 
everything has to be just so or the magic won't work

Re: sth->prepare() setting Active?

[David Nicol]

this isn't tested -- i'm writing it here in e-mail -- but it or something
very close to it might work. supporting prepare_cached or other forms of
sth reuse could require a way to clear the flag.


package DBIx::WrapActive;
our $AUTOLOAD;

### invoke like $sth = DBIx::WrapActive::wrap($sth);
sub wrap{ bless [$_[0], 0 ] };
sub active { $_[0]->[1] and $_[0]->active }
sub execute {
my $obj = shift;
$obj->[1] = 1;
$obj->[0]->execute(@_);
}
sub AUTOLOAD{
 my ($method) = $AUTOLOAD =~ m/::([^:]+)$/;
 *{$AUTOLOAD} = sub {
 my $obj = shift;
 $obj->[0]->$method(@_);
 };
 goto &$AUTOLOAD;
};

Re: sth->prepare() setting Active?

[David Nicol]

sorry, this one is better

   "Active"

   Type: boolean, read-only

   The "Active" attribute is true if the handle object is "active". This
   is rarely used in applications. The exact meaning of active depends on
   the database driver, but some aspects of the semantics are defined for
   interoperability. These include:

   o   For a database handle, active typically means that the handle is
   connected to a database ("$dbh->disconnect" sets "Active" off).

   o   For a statement handle it typically means that the handle is a
   "SELECT" that may have more data to fetch. (Fetching all the data
   or calling "$sth->finish" sets "Active" off.)

   o   Prepared statement handles that must have "execute" called on them
   before they will return data should not be active until that
   happens.
--- DBI.pm_orig 2016-09-30 15:28:20.0 -0500
+++ DBI.pm  2016-09-30 15:59:26.0 -0500
@@ -3595,13 +3595,29 @@
 Type: boolean, read-only
 
 The C attribute is true if the handle object is "active". This is 
rarely used in
-applications. The exact meaning of active is somewhat vague at the
-moment. For a database handle it typically means that the handle is
-connected to a database (C<$dbh-Edisconnect> sets C off).  For
-a statement handle it typically means that the handle is a C
+applications. The exact meaning of active depends on the database driver, but 
some aspects
+of the semantics are defined for interoperability. These include:
+
+=over
+
+=item
+
+For a database handle, active typically means that the handle is
+connected to a database (C<$dbh-Edisconnect> sets C off).
+
+=item
+
+For a statement handle it typically means that the handle is a C
 that may have more data to fetch. (Fetching all the data or calling 
C<$sth-Efinish>
 sets C off.)
 
+=item
+
+Prepared statement handles that must have C called on them before 
they will return data
+should not be active until that happens.
+
+=back
+
 =head3 C
 
 Type: boolean

Re: sth->prepare() setting Active?

[David Nicol]

On Fri, Sep 30, 2016 at 4:57 AM, Tim Bunce  wrote:
>> > > Should a call to prepare() return an Active statement? (i.e. 
>> > > $sth->{Active} == 1)
>> > >
>> > > This appears to be the behaviour of DBD::Sybase, but not DBD::Pg
>> >


>> That's unfortunate, because Class::DBI does.
>>
>> $ grep -r Active .
>> ./lib/Class/DBI.pm:   $sth->execute(@$args) unless $sth->{Active};
>>
>> (from sth_to_objects)
>>
>> As far as I can see, this is to work out whether the sth that's been
>> passed in has already had execute called on it (e.g. part of a multiple
>> result loop). Removing the $sth->{Active} check (and ensuring Ima::DBI
>> always calls prepare and not prepare_cached (I haven't figured out what's
>> going on there yet) gets Class::DBI working with DBD::Sybase.

> I'd take the view that $sth->{Active} shouldn't be true until after a
> successful execute().
>
> I'd happily take a doc patch that tightens up the docs in that direction.

Attached is a patch against current CPAN version that modifies the
documentation to the following:

   "Active"

   Type: boolean, read-only

   The "Active" attribute is true if the handle object is "active". This
   is rarely used in applications. The exact meaning of active depends on
   the database driver, but some aspects of the semantics are defined for
   interoperability. These include:

   For a database handle, active typically means that the handle is
   connected to a database ("$dbh->disconnect" sets "Active" off).

   For a statement handle it typically means that the handle is a "SELECT"
   that may have more data to fetch. (Fetching all the data or calling
   "$sth->finish" sets "Active" off.)

   Prepared statement handles that must have "execute" called on them
   before they will return data should not be active until that happens.



-- 
"Teaching radical novelties is our main safeguard against
dictatorships" -- Edsger W. Dijkstra
--- DBI.pm_orig 2016-09-30 15:28:20.0 -0500
+++ DBI.pm  2016-09-30 15:51:54.0 -0500
@@ -3595,13 +3595,19 @@
 Type: boolean, read-only
 
 The C attribute is true if the handle object is "active". This is 
rarely used in
-applications. The exact meaning of active is somewhat vague at the
-moment. For a database handle it typically means that the handle is
-connected to a database (C<$dbh-Edisconnect> sets C off).  For
-a statement handle it typically means that the handle is a C
+applications. The exact meaning of active depends on the database driver, but 
some aspects
+of the semantics are defined for interoperability. These include:
+
+For a database handle, active typically means that the handle is
+connected to a database (C<$dbh-Edisconnect> sets C off).
+
+For a statement handle it typically means that the handle is a C
 that may have more data to fetch. (Fetching all the data or calling 
C<$sth-Efinish>
 sets C off.)
 
+Prepared statement handles that must have C called on them before 
they will return data
+should not be active until that happens.
+
 =head3 C
 
 Type: boolean
@@ -6774,7 +6780,7 @@
 
 The C<\%attr> parameter may also contain the following attributes:
 
-=over
+=over 4
 
 =item C
 

Re: help to connect

[David Nicol]

Are you sure Oracle is expecting UTF8 for the password? Because it works
without accented chars in the password, the simplest thing might be to
change to a password without encoding issues.

On Thu, May 12, 2016 at 10:38 AM, Bruce Johnson <
john...@pharmacy.arizona.edu> wrote:

>
> > On May 12, 2016, at 4:12 AM, Jefferson Elias <
> jefferson.el...@chu.ulg.ac.be> wrote:
> >
> > Hi,
> >
> > I've been trying many differents things with the following error, but
> nothing seems to be working.
> >
> >
> > Let me start to explain my problem from the beginning.
> >
> > (Perl version: v5.10.)
> >
> > I use a configuration file that I parse using
> Config::General::ParseConfig. This file is encoded in UTF8 as shown below:
> >
> > $ file config/application.conf
> > config/application.conf: UTF-8 Unicode English text
> >
> >
> > In this configuration file, there is a field called
> 'target_db_list_default_password' which contains a default password to be
> used for my monitoring.
> > This password is then used in conjunction with DBI->connect() method
> primarily to contact an Oracle Database instance.
> >
> > I always get the following message at execution:
> >
> > Perl Error message: DBI connect('MY_DB','halfonz',...) failed:
> > ORA-01017: invalid username/password;
> > logon denied (DBD ERROR: OCISessionBegin) at
> /home/jeff.elias/Projects/DBA_SOURCES/branches/develop-next/Templates/Projet
> Perl simple/src/libs/Common/DbConnection.pm line 210
> >
> >
> > The password cannot be changed that easily. I've printed out the
> password I get back and copy-pasted it into a SQL Developer new connection
> dialog then tried to connect and it worked.
> > So, my conclusion is that the password can be considered as the OK.
>
> Make sure the Oracle environment variables, particularly the SID are the
> same for both SQL Developer and in your perl application; perhaps the
> config file is pointing to the wrong DB? (a development versus production
> thing? )
>
> remotely it MIGHT be  default NLS_LANG thing, but I don’t think so.
>
> A quick test would be to connect to the DB with hardcoded credentials in a
> test perl script? This will determine whether it’s perl/DBI or encoding
> issues from the config file.
>
> --
> Bruce Johnson
> University of Arizona
> College of Pharmacy
> Information Technology Group
>
> Institutions do not have opinions, merely customs
>
>


-- 
"Why would you want to be the last man alive on a sinking ship?" -- Elon
Musk

Re: Perl 6 and DBI

[David Nicol]

Perl 6 may need its own Tim Bunce, rather than somehow pressing the
original into service.



On Fri, Feb 6, 2015 at 4:40 PM, Greg Sabino Mullane g...@turnstep.com
wrote:


 -BEGIN PGP SIGNED MESSAGE-
 Hash: RIPEMD160


 Tim Bunce said:
  On MoarVM the perl5 DBI can be accessed via the Inline::Perl5 module.
  That probably counts as a reasonable working DBI :)

 Meh. More like working in a pinch via a hack but also
 says completely unsupported :(

  What's missing is a team of people with the right skill willing to work
  on it.  I've had little time to do more than the tinkering I've already
  done and I'm severely hampered by knowing ~zero perl6 or Java.

 Thanks for the answer. Sadly, what I am reading between the lines here
 is that DBI is not even on the radar for anyone working on Perl 6, much
 less being a blocker. FWIW, I'm also in the zero perl6 boat.

 - --
 Greg Sabino Mullane g...@turnstep.com
 End Point Corporation http://www.endpoint.com/
 PGP Key: 0x14964AC8 201502061739
 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

 -BEGIN PGP SIGNATURE-

 iEYEAREDAAYFAlTVQr0ACgkQvJuQZxSWSsgDngCffP6BLn4R5V5dKDuG0c/HODa+
 d7oAn3QbU5rKPHuuQmu5GgHxLjTb+9jt
 =nyAS
 -END PGP SIGNATURE-





-- 
There is a lot more low hanging fruit when you're tall.

Re: Perl 6 and DBI

[David Nicol]

Does this mean the floor is open for brainstorming? I'd like to see more
transparent integration, so p6+dbi would be like pl/sql or pro*C or
whatever that language Peoplesoft scripts used to be in that I was working
with when I wrote DBIx::bind_param_inline.

http://perlbuzz.com/2008/12/database-access-in-perl-6-is-coming-along-nicely.html

http://www.mail-archive.com/dbdi-dev@perl.org/maillist.html
doesn't have anything new since 2011.




 And in that case, there would/should be a discussion about what form this
 DBI
 for Perl 6 should take.

Re: Escaping placeholders

[David Nicol]

On Fri, Dec 19, 2014 at 6:12 AM, Alexander Foken alexan...@foken.de wrote:


 Problem was using named placeholders (:foo) in DBI and at the same time
 use PL/SQL code containing variables (:bar), DBI considered both :foo
 and :bar to be placeholders instead of leaving :bar alone and pass it
 to Oracle.


But the fundamental problem was not solved, there was and still is no way
 to escape placeholders.


I think the suggestion of making ::(\w+) become :$1 and exempting that from
placeholder recognition seems like a complete winner and DBD maintainers
could do that right away, and by do that I mean accepting, applying, and
redistributing patches..

Re: Escaping placeholders

[David Nicol]

Please disregard my previous. After reading Tim Bunce's earlier response
and thinking about this some more, although backslash escaping can be
tricky, that is how Perl does these things, and Perl coders are familiar
with the nuances of when they must be doubled to get through quoting.
Doubling of syntax characters is a database thing, but it would have to be
revised for every new escapable character. Backslash escaping can be
embraced once and will continue to work in potentialfutures where other
significant characters (aside from colon and question mark) might need to
be escaped too.

On Fri, Dec 19, 2014 at 10:27 AM, David Nicol davidni...@gmail.com wrote:



 I think the suggestion of making ::(\w+) become :$1 and exempting that
 from placeholder recognition seems like a complete winner and DBD
 maintainers could do that right away, and by do that I mean accepting,
 applying, and redistributing patches..

Re: Surprising DBD::Oracle error raised

[David Nicol]

On Wed, Feb 5, 2014 at 7:25 AM, Martin J. Evans
martin.ev...@easysoft.com wrote:

 That is indeed interesting. When I run the following with DBD::ODBC to MS
 SQL Server:

 use strict;
 use warnings;
 use DBI;

 my $h = DBI-connect();
 eval {
 $h-do(q/drop table mje/);
 };

 $h-do(q/create table mje (a int)/);
 my $s = $h-prepare(q/insert into mje values(?)/);
 $s-execute(1);
 $s-execute(2);

 $s = $h-prepare(q/select a from mje where a = 1/);
 $s-execute;
 my ($row) = $s-fetchrow_array;  # fetch result
 print $row\n;
 $row = $s-fetch;  # fetch status
 print $row\n;


 I get:

 1
 Use of uninitialized value $row in concatenation (.) or string at
 mje/fetch_off_end.pl line 20.

 However, I get the same with DBD::Oracle so how is you code different from
 the above.

 Martin

The code is the same. Rather, close enough, my second fetch was in
boolean context
while yours is in a storage context, but I don't think Perl cares,
both contexts are scalar.
And I've got RaiseError set. And my SQL, which joins two tables, has
three placeholders, bound with bind_param between the prepare_cached
and the execute.

And your warning verifies that the second fetch returned an undef, as
documented, while mine threw an exception falsely claiming that the
statement hadn't been executed yet:

An Oracle error has occurred :
DBD::Oracle::st fetch failed: ERROR no statement executing (perhaps
you need to call execute first)
 SQL Text 
select a from mje where a = 1
...


And you're certainly using more recent builds of everything.



-- 
The one L lama, he's a priest
The two L llama, he's a beast
And I will bet my silk pyjama
There isn't any three L lllama. -- Ogden Nash

Surprising DBD::Oracle error raised

[David Nicol]

$price_sth-execute;
my ($o_file_price) = $price_sth-fetchrow_array();
if ($price_sth-fetch) {
$this-log_error('ERROR: scalar select returned second row at
%s line %d', __FILE__, __LINE__);
}


I expected the fetch to return undef, but it throws an Oracle error.
My best ignorant guess here is that fetchrow_array does some cleanup
on one-row datasets, but that isn't documented.

Advise?


-- 
The one L lama, he's a priest
The two L llama, he's a beast
And I will bet my silk pyjama
There isn't any three L lllama. -- Ogden Nash

Re: Surprising DBD::Oracle error raised

[David Nicol]

the error message claimed I hadn't executed the statement.
I changed it to

if (eval { $price_sth-fetch}) {
$this-log_error('ERROR: scalar select returned second row at
%s line %d', __FILE__, __LINE__);
}

but I think I'll change it again to

if ($price_sth-{Active} and $price_sth-fetch) {
$this-log_error('ERROR: scalar select returned second row at
%s line %d', __FILE__, __LINE__);
}

but if that's the right thing to do, the documentation (in
DBD::Oracle) should mention it in fetchrow_array's section IMO.




-- 
The one L lama, he's a priest
The two L llama, he's a beast
And I will bet my silk pyjama
There isn't any three L lllama. -- Ogden Nash

Re: cross database queries?

[David Nicol]

On Wed, Jun 26, 2013 at 7:26 AM, Andrew Snyder a...@dancingjars.com wrote:

 I want to write a query like:

 select clients.client.client_id, columnar.sales.total_sales, web.page_hits
 from clients, columnar, web
 where clients.client_id = columnar.client_id
 and  clients.client_id = web.client_id

 in a system where 'clients' is actually one or more relational databases,
 'columnar' is one or columnar databases, and 'web' is the Apache logs on
 one or more web servers.  The dbi driver would be configured to connect to
 the correct databases and filter web hits based on 'client_id'.

 Has somebody written that already?

 Thanks,
 Andrew



it seems like the right thing to do here would be to do three queries,
against the three data sources, and store all the results in a hash of
arrays, then dump the results. Any solution that automates it will wind up
doing at least that anyway, and might not be optimized for the join.

Unless there really are so many client IDs that you need to process the
results as a stream or run out of memory, which is unlikely.


while (my ($c_id, $ar) = each %resultz){
  $ar-[0] or next;   # filter out client_id not appearing in
clients database
  print join( \t, $c_id, 0+$ar-[1], 0+$ar-[2]),\n;
}

Two parallel hashes containing the web and columnar results, accessed once
for each result from querying the clients table, would also work.

Re: Dereference a HASH reference

[David Nicol]

this is entirely off-topic for dbi-users. That said, what you're seeing is
due to $q-param('dow') called in array context returning some number
of things other than one thing. There are various ways to fix it,
depending on how $q works. The approach you tried, putting one of the
param lookups inside an anon arrayref constructor, is halfway there --
you need to reach into that array later when you use it.

The situation is also a subtle security flaw, as a user can in theory
construct funny form data with multiple entries for names and
overwrite
for instance shift_beg by giving three 'factory' data, the middle one
being 'shift_beg. Wrapping one of them in square brackets doesn't
help with the general exploit.

 my %attrs = (
        shift_name = $q-param('shift_name'),
        shift_beg = $q-param('shift_beg'),
        shift_end = $q-param('shift_end'),
        shift_days = $q-param('dow'),
        factory_id = $q-param('factory'),
 );

This:

my %attrs = (
        shift_name =  scalar($q-param('shift_name')),
        shift_beg =  scalar($q-param('shift_beg')),
        shift_end =  scalar($q-param('shift_end')),
        shift_days = scalar( $q-param('dow')),
        factory_id = scalar( $q-param('factory')),
);

will always give you exactly one thing in the value slots, then
depending on what you see there you can adjust.


-- 
[Feynman]'s wife was granted a divorce from him because of [his]
constantly working calculus problems in his head as soon as awake,
while driving car, sitting in living room, and so forth, and that his
one hobby was playing his African drums. His ex-wife reportedly
testified that on several occasions when she unwittingly disturbed
either his calculus or his drums he flew into a violent rage, during
which time he choked her, threw pieces of bric-a-brac about and
smashed the furniture. -- FBI

Re: same query, two different approaches, vastly different performance

[David Nicol]

On Mon, Feb 13, 2012 at 7:16 PM, Puneet Kishor



 a... that makes sense. I would like to confirm this, because, if true,
 then it is a strike against statements prepared with bind values.


the postgres reference about when LIKE statements get to use indices states
that they are only available for /constants/ ending with %. It seems like
this would be simple enough to fix, if you want to be a hero and get a
patch accepted into Postgres.

It's in the fourth paragraph at
http://www.postgresql.org/docs/8.4/static/indexes-types.html


Also there is interesting stuff about creating indices for functions, so
you can index on lower(a) and then queries using lower(a) will get
optimized.
After reading that bit of the postgres docs I wonder if doing something like

lower(a) like ('^' || ?)

would allow the index to be used. Probably not, as the result would not be
a constant, even though it would be anchored to the beginning.

Re: same query, two different approaches, vastly different performance

[David Nicol]

On Mon, Feb 13, 2012 at 9:52 PM, Greg Sabino Mullane

 When you do that prepare and execute, DBD::Pg is asking Postgres to
 create a prepared statement, such that it can send just the arguments,
 and not the full statement, each time execute() is called. You can
 force it to *not* do so by issuing:

 $dbh-{pg_server_prepare} = 0;

 You can also do this at the statement handle generation time: see the
 DBD::Pg docs for more information.


That's awesome. Here's the answer to the PK's other question:

The proposed patch to Postgres would cause plan selection to be deferred,
in case of LIKE placeholder, until after pg can look at the
placeholder(s), to see if it's anchored at the front. The resulting trick
pg would generate 2**n plans at prepare time, where n is the number of
placeholders that may or may not be anchored at the front. Extra extra
points for figuring out that '^'||placeholder will always be so anchored,
so it can use a sorted index, even though it might have to filter the
results from that. With such a hack in place, and turned on by default,
there would be one fewer situation where the pg_server_prepare attribute
could be left on.

-- 
on the outside of the bottom of the barrel, that's how much of an outlier
it is.

Re: is it possible to use two sth from same dbh at the same time ?

[David Nicol]

when it isn't possible, you can create two database handles, and they can
have different attributes.

my $dbhA = DBI-connect();
my $dbhB = DBI-connect();
my $sth1 = $dbhA-prepare( $sql_a );
my $sth2 = $dbhB-prepare( $sql_b );

$sth1-execute;
$dbhB-begin_work;  END { $dbhB-commit}


while ( my ($id) = $sth1-fetchrow_array ) {
 state $counter = 1;
 $sth2-execute( $id );
$counter++ % 2000 or $dbhB-commit;
};

Re: can $dbh-do take a prepared statement handle?

[David Nicol]

 I don't think the answer is yes at all and I don't think David was saying
 yes. I don't think you can pass a prepared statement handle to the do
 method, it needs to be some SQL text. The above example from David does not
 pass a statement handle to do. It checks if $_[0] is a ref (which it will be
 if it is a statement handle) but if it is not (i.e., it is sql text) it
 calls do.


Furthermore, my super_do would not work with an object that
stringifies into SQL code. Better might be

eval {$unknown-execute();1) or $dbh-do($unknown)

which would need to be adjusted to capture the returned values, if
needed, and would not work with an object other than a $sth that has
an execute method.

Re: can $dbh-do take a prepared statement handle?

[David Nicol]

=pod

if you've got enough control over the flow of control to have the code below
work safely, you probably don't need it

=cut

sub Super_do($){
   if (ref $_[0]){
$_[0]-execute();
   }else{
$dbh-do($_[0]);
   }
};

Re: Proposed change to disconnect in DBD::ODBC

[David Nicol]

A non-fatal warning is the right thing here.


   do nothing   |warn |die
notifies in new dev no  |  yes |   yes
darkpan safeyes |   probably |no





-- 
This is not a 'bug'. Being documented, it is merely a 'restriction'. --
Intercal manual

Re: read loop

[David Nicol]

 my @ids_ary = [];
 my @names_ary = [];
 my @age_ary   = [];

Why do you need an empty arrayref as the first element of these?


   $name_ary[$i] = $name;
   $i++;

push @name_ary, $name also works, and makes it more clear that you're
using a parallel arrays data structure.

here's an alternative:

  sub iID(){0} sub iNAME(){1} sub iAGE(){2}
  my @IdNameAge_pary = ( [],[],[] );
  while (my ($id,$name,$age) = @{$sth-fetch}){
 push @{$IdNameAge[iID]},$id;
 push @{$IdNameAge[iNAME]},$name;
 push @{$IdNameAge[iAGE]},$age;
  };
  ...


but seriously, loading everything into a temporary array just uses a
lot of memory and makes the code larger, so I avoid it when I can.
DBI's facility for providing a hashrefs for every result row makes it
easy to use names not numbers, and usually the kinds of processing
that you would need all the results in memory for (sorting, finding a
max or min) can be done with SQL (unless of course there aren't
enough, or the right, indexes.)

Re: SQLite Concurrency Issue

[David Nicol]

Are you trying to use Apache pooling within mod_perl? I tried that for a
while and gave up due to lock contention issues, switching to a paradigm
where I opened, transacted, closed without caching any database stuff and my
locking issues went away. This means among other things, no persistent
prepared statements.

-- 
“The aeroplane is fatally defective. It is merely a toy—a sporting
play-thing.  It can never become commercially practical. -- Nikola Tesla

Re: DBI Layer documentation

[David Nicol]

 please keep the list cc'ed.

I disagree with this sentiment when it comes to off-topic personal assertions.

 [sybase]

The sybase DBD builds against a TDS module of some kind; for writing C
front end and Sybase back-end unless there is an existing codebase to
copy more than the SQL from it might make more sense to skip the
middleman and look directly at FreeTDS or equivalent TDS library if
you've got one.

anyone accessing h2 java database with DBI?

[David Nicol]

I don't suppose anyone has bothered to construct a DBD for h2?
http://www.h2database.com/html/main.html
the unfortunate choice to name it the same as the level two header
html tag makes it somewhat tricky to search for.

a tool to reverse-engineer a schema

[David Nicol]

Has anyone extended DBD::Null to make a tool that after you run a mess
of prepare statements against the handle, it will give you some DDL to
create a schema that they will all make sense on?


-- 
I like to think that when I ramble on, I'm speaking for others who
share my opinions or point of view but who aren't as expressive.

Re: a tool to reverse-engineer a schema

[David Nicol]

I mean of course DBD::Mock, sorry
http://search.cpan.org/~drolsky/DBD-Mock-1.39/lib/DBD/Mock.pm

On Tue, Apr 27, 2010 at 9:24 AM, David Nicol davidni...@gmail.com wrote:
 Has anyone extended DBD::Null to make a tool that after you run a mess
 of prepare statements against the handle, it will give you some DDL to
 create a schema that they will all make sense on?

Re: Which SQLite DBD To Use

[David Nicol]

amalgamation includes the optional Full Text Search Engine module,
which is extremely cool if you need it.


-- 
question doubt

What is a good reference for what bind_col types to use when?

[David Nicol]

trial and error brings results, but seems sub-optimal.

Specifally, I tried lots of other things before finding that
SQL_DECIMAL allows 9223372036854775807 to enter into Perl as a string
rather than as 9.blahblahblahe+18. (useing sqlite.)

Are these things driver-specific or are there sane fallbacks?  For
instance, SQL_BIGINT might have gotten mapped to SQL_DECIMAL for this
driver.

inline placeholder DBIx now on CPAN

[David Nicol]

Enjoy!

http://search.cpan.org/~davidnico/DBIx-bind_param_inline-0.02/lib/DBIx/bind_param_inline.pm



-- 
Refusing to move when ordered, he was tragically mulched. -- The Onion

Re: security advice needed

[David Nicol]

On Wed, Nov 12, 2008 at 3:50 PM, Ulisses Montenegro
[EMAIL PROTECTED] wrote:
 Also, if you ever need to display those values in any other potentially
 interpreted format (such as a Web page -- browsers interpret and render
 HTML), remember to escape them. Even if you are protecting yourself
 against SQL injection, you might end up vulnerable to XSS/CSRF attacks
 by displaying unescaped data in a web document.

 Ulisses

see also
http://search.cpan.org/~rsavage/HTML-Entities-Interpolate-1.00/lib/HTML/Entities/Interpolate.pm

Re: DBIx::Chart real Candlestick

[David Nicol]

http://gnuplot.sourceforge.net/demo_4.3/candlesticks.html

On 7/31/07, Dean Arnold [EMAIL PROTECTED] wrote:
 Christian Maier wrote:
  Hello
 
  Do anybody know how to draw a real candlesick bar chart with open,
  high, low, close instead of high and low?

-- 
Prioritize based on common sense?
Is that some kind of joke?

Re: Cross-Database query engine?

[David Nicol]

On 7/24/07, Daniel Kasak [EMAIL PROTECTED] wrote:

doing cross-database queries is drop-dead simple in MS Access,


really?  please explain.

Re: Cross-Database query engine?

[David Nicol]

I'm no database expert but I believe I can answer your question.

On 7/24/07, Daniel Kasak [EMAIL PROTECTED] wrote:


Link some tables from one database server.
Link some tables from another database server.

Go to the query builder. Add tables from both database servers. Join
tables where appropriate. Execute query.


So you're saying that Access abstracts the handles to the multiple databases
in such a way that they appear to you as a single database, and you can use the
tools the way you would use on a single database on the combination of the
multiple databases.

I do not recall seeing such a tool discussed on this mailing list.  If
I were tasked
with such a situation I would unfold the various queries, possibly using one of
the perly abstractions such as Tie::DBI or DBIx::SimplePerl and using keys or
each to iterate.  At some level, Access must be doing that for you.

The closest thing a cursory CPAN search revealed was
http://search.cpan.org/~dwright/DBD-Multi-0.10/lib/DBD/Multi.pm
which appears to be concerned with load-balancing against data
sources containing identical data rather than abstracting multiple data sources
in a way that the database driver takes apart the queries and sends the various
pieces to the various databases.

Without knowing for certain that nobody has done what you are looking
for already,
it sounds to me like it would be a welcome addition to the DBI tool kit.

Go for it.  I expect that the devil will be in the optimization.

Re: Bar Code

[David Nicol]

if you're using a label printer, the label printer will have primitives
for that.  If you're using a normal high-resolution laser or bubblejet,
I would suggest looking into barcode support with the GD graphics
library.

Also, this issue is off-topic for dbi-users; after finding a bar code graphics
library that is supposed to work, follow up to their users list for
help with it.

Have a nice day

Re: When is a string a number?

[David Nicol]

On 10/4/06, Martin J. Evans [EMAIL PROTECTED] wrote:


With DBI/DBD::Oracle all values read from the database are scalars. As
everyone will know, whether something read from the database is a string
or a number in Perl purely depends on the context it is used in so:


internally, there are flags in the SV structure that hint as to what conversions
have been done on the SV already, for efficiency's sake.


I have to admit I don't know how the JSON module knows what is a number
and what is a string in Perl but I see the same issue with Data::Dumper
so I presume there must be some way to find out if a perl scalar is a
number or a string.


It seems like the pure perl Dumper tests values with a regex and optimizes
to numbers when the stringification matches a number template, while
the XS version checks the flags in the SV structure.


The problem gets a lot worse for me since I do some arithmetic on values
pulled from the database before converting them to JSON and this is
where Perl seems to change them into numbers e.g.



I don't want to have to do ($var +0) on all the number fields I pull
from the database (to turn them into numbers) and neither do I want to
do a '$var .= ' (to turn all the fields into strings).


you might have to do exactly that.  $var will produce a string version. Sorry.
Apparently you can tell the JSON module to make everything strings:
http://search.cpan.org/~makamaka/JSON-1.07/lib/JSON.pm#AUTOCONVERT


As an aside (and probably a perl question rather than a DBI one) does
anyone know why the type of a scalar changes when you use it on the
right side of an assignment:

perl -MData::Dumper -le '$a=1; print Dumper($a); $b += $a; print
Dumper($a);'
$VAR1 = '1';
$VAR1 = 1;


The has-been-evaluated-as-a-number flag got set on $a when it was
evaluated as a number; then Dumper, with both available, chose the
number format.


How does JSON and Data::Dumper know whether Perl thinks something is a
number or a string?


inspecting the flags; except pure-perl Dumper apparently uses a
regular expression
to identify numbers. Those are guesses.  The source is available for
your inspection.


--
The Country Of The Blind, by H.G. Wells
http://cronos.advenge.com/pc/Wells/p528.html

Re: Help with my perl script

[David Nicol]

On 8/23/06, Rutherdale, Will [EMAIL PROTECTED] wrote:


Again, I recommend simply using fork(), in a loop.


a rabbit doesn't need a loop...

 $start_time = time + 10;
  ... # one process
  fork; # two here
  fork; # four here
  fork; # eight here
  fork; # 16
  fork; # 32
  fork; # 64
  fork; # 128 here
  fork; # 256 here, load on something is high :)
  # fork; # 512 , etc
  # and whatever happens here will happen 256 times, in parallel,
  # or at least as parallel as your OS can give you
  select(undef,undef,undef,0.003) while(time  $start_time); #
wait for trigger
 ... # connect and crash server



--
David L Nicol
Dickenson on the flag
http://cronos.advenge.com/pc/EmilyDickenson/SecondBook/p39.html

Re: how to set a DEFAULT value !!

[David Nicol]

On 4/24/06, Greg Sabino Mullane [EMAIL PROTECTED] wrote:

 I've proposed adding something simlilar to DBI itself, but I don't recall 
 getting
 any feedback on it. Presumably once in place DBIx::Class will someday support 
 it.

DBI is complex enough, and AIUI the DBI philosophy opposes adding features
to the core that will cause implementation headaches for driver authors.

The standard perl idiom for default values is

   %hash = (key1 = 'defaultvalue1', key2 = 'defaultval2',  @_ );

Combining that with something like the insert_hash example from perldoc DBI
should give you a tidy function that inserts default values.

--
David L Nicol
Document what you do, then do what you documented

Re: NET::FTP

[David Nicol]

On 4/4/06, Gupta, Razat [EMAIL PROTECTED] wrote:

 We have a coonection through a NAT IP.
 Simultaneously , five or six scripts runs and fetch data from the same
 server.

 Sometimes we got files successfully while sometimes we are getting
 error :

 UNEXPECTED EOF FILE ON COMMAND CHANNEL.

 Everytime i am pulling the files manually , i am not getting any errors
 like that.
 But i am getting the above error only through scripts.
 Can anyone tell me whether its a script problem or some network issue.

I reccommend wrapping the transaction in question in an evaluation
and trying again -- perphaps after sleeping a few seconds -- when the
error happens.  That approach generally works  whenever facing a
mysterious intermittent error.

Also looking that the ftp server logs.  You might need to increase
the open files limit on the server or something like that.

I have hit FTP servers pretty hard (using DirDB::FTP) and have found that
they often aren't up to the task.  Trying a new FTP server might help.  There
are dozens of them listed on freshmeat.net.

and FTP is hell on NAT, too.  You could switch to a different data access method
entirely, such as a proper database.

--
David L Nicol
Should the bike shed have bunks?  Or maybe cots?

Re: Why can I find postings on google groups that I never saw on this list

[David Nicol]

On 12/6/05, Martin J. Evans [EMAIL PROTECTED] wrote:

 On 06-Dec-2005 Andy Hassall wrote:
  If this is the case, then it seems that Google Groups should either make
  their version of the group read-only (this would seem to be the most
  sensible option since it's really a mailing list you should subscribe to),
  or fix the apparent propagation problem from postings made on their
  interface back to the source (nntp.perl.org).

 I find it rather disturbing I can be posting to the dbi-user list and someone
 can answer on google groups and I don't see it. If google intend it to work
 this way I'm amazed and rather worried as I didn't see anything on google
 groups that made this clear.

As a former usenet admin I can attest that configuing the news server
to propoagate
the perl.* groups out through nntp.perl.org would be trival.  If
nntp.perl.org wants to
allow unverified content arriving through that channel to get copied out to
subsciber-only mailing lists, that's another story, as usenet can be
awfully spammy.

Google has pretty good junk filters though, so maybe opening that channel would
be a good thing, if googlegroups doesn't send any junk through.

Re: DBI v2 - The Plan and How You Can Help [DRAFT]

[David Nicol]

On 7/2/05, Dean Arnold [EMAIL PROTECTED] wrote:
  
 - Asynchronous queries (coroutines?  threads?)
 
 Threads. If you've ever done much Java/JDBC work, you'll
 realize how much simpler a solution to async it is.
 (Ignoring the rest of Java/JDBC's undesirable traits)

A couple quarters ago I submitted a proposal to write a general
pragma delivering sugar to simply wrap any module with message-passing,
including worker process/thread pooling and testing against DBI to the
TPF, I guess the proposal has expired by now.  Anyway this can be
solved in a general way that is larger than DBI, and get it solved in
DBI for free.




 5. A better set of metadata for DBDs to report the functionality
 they support. E.g., today, the only way to find out if a DBD
 is threadsafe (at runtime) seems to be try it and hope for the best.

This is another problem that would better be solved at a higher level than
for DBI exclusively.  There are other projects with plug-in architectures,
and there are meta.yaml files -- the problem turns into, who names the
functionalities and what are the standard names of the levels of support,
and back-documenting the massive base of CPAN modules that wouldn't
have function level listings in their metadata files.  Support for
modules indicating
in a better fashion what versions of Perl they require would be very
closely related.

Maybe the next META.yaml comittee meeting might produce a document describing
a way for modules to identify the levels at which they support names
functionalities
and the next DBI standard could mandate a set of functionalities
that would have to be listed in conforming database drivers.

Whew!

Re: DBI v2 - The Plan and How You Can Help [DRAFT]

[David Nicol]

On 7/5/05, Dean Arnold [EMAIL PROTECTED] wrote:
 
 I'm already implementing [a message-passing async] wrapper for DBI
 (DBIx::Threaded); not a pragma, and very specific to DBIv1, but hopefully it 
 solves
 at least 85-90% of the problem. (tho async cancel/abort isn't
 solvable at this point)
 
 BTW: the Pots::* modules already do what I *think* you're
 proposing (again, not as pragmas, and Perl5 based)
 
 Dean

I'm going to forward you the Asynchronous pragma proposal off this list for your
consideration of the syntax.  Elizabeth Mattijsen liked it, for what
that's worth.

I don't see what the advantage of using the Pots modules is over directly using
Thread::queue  -- it doesn't appear to me to save any coding, although using
Pots modules would enforce some structure, which can be helpful.


-- 
David L Nicol

Re: inserting data coming from a hash

[David Nicol]

On 4/21/05, Robert [EMAIL PROTECTED] wrote:
 This is my hash structure:
 
 Veterans Day = {
 date= '2005',
 type= 'US',
 federal = 'true',
 active  = 'true',
  },
 
 Would I just use a placeholder (?) in my statement and pass it in via that?
 It will be in a loop as I have quite a few.

assuming your holidays are a HoH called %Holidays,
you'd use five placeholders and call

execute ($HolidayName, @{$Holidays{$HolidayName}}{qw/date type
federal active/});

if you don't mind saving a wee bit of programmer time and spending
more hardware effort
you might be able to use Tie::DBI to allow something like

  $HolidayDatabase{$HolidayName} = $Holidays{$HolidayName};

provided the key names (and the boolean representations)  match.


-- 
David L Nicol
$ perl -wc -e 'use strict; the deer and antelope play ; print 1'
-e syntax OK

Re: cacheing problem

[David Nicol]

 
 It appears that DBI/DBD is caching the results.  How can I disable this?


the problem was not in DBI.

In case anyone else has this non-problem, 
  my PEBKAC was that user-provided data, which was
current with the previous fetch, was replacing the data from the newer
fetch.  Disabling
this for the special case of reloading from the database solved the problem.

cacheing problem

[David Nicol]

I am using dbd::mysql on activestate perl 5.8.  When I select a row in
my program,
change the row using the mysql administrator tool, and select the row
again in the
program, the results are not changed.  I can get the new data by
selecting a different
row, then selecting the changed row again, then I get the new results,
but running the
same select gives a stale result.

The result is the same regardless of if I use placeholders or not, if
I use a persistent
statement handle or make a new one, if I use a persistent connection
or call dbi-connect
for each select, and is not affected by using 'SELECT SQL_NO_CACHE' instead of
'SELECT.'

It appears that DBI/DBD is caching the results.  How can I disable this?



-- 
David L Nicol
You don't know how to maintain a station wagon either!

Proposing an API for the extension for simplifying database connections

[David Nicol]

  That's what DBI wrappers do, and I have one of those too.  But my DBI
  wrapper reads its connection information for each logical data source from
  a hash.  Then there's a build_dsn() method that assembles the pieces
  according to the name of the driver.
 
  If each DBD did that for me, I could just pass a reference to my connection
  info hash directly to DBI-connect() and then remove the build_dsn() code
  from my wrapper.
 
 Assuming that each DBD used the same names for elements with the same meaning
 (database vs dbname vs db vs sid etc etc).
 
 Tim.

mandating a new method for all compliand DBDs is, as ever, outrageeous.

Here's a proposed compromise:  A new DBD that takes the elements of
the attributes hash, and at connect time this DBD will build the DSN (or
croak and complain explicitly about what is missing in a well defined way)
and return a real $dbh resulting from calling DBI-connect with the build DSN.

DBI doc tells us:
   $dbh = DBI-connect($dsn, $user, $password,
  { RaiseError = 1, AutoCommit = 0 });


DBD::dsn (DBD::any?  DBD::DBH?) would work like this:
   $dbh = DBI-connect('DBI:DBH', $user, $password,
  { RaiseError = 1, AutoCommit = 0,
 host = $dbhost, dbd = 'pg', port = $dbport });

That way, DBI-connect doesn't need to be rewritten to allow a hash -- it
already allows the %attr hash -- and the intermediate DSN created by
the build_dsn method is hidden away in the inner workings.

Am I missing something?

Re: DBIx::DBH - Perl extension for simplifying database connections

[David Nicol]

instead of having to haul around the code to figure this out, why not create a
handy documentary web service somewhere where you fill out the blanks and
get an appropriate connection string?  Loading a module every time you start
the program just to create something that is a permanent
per-installation configuration
constant strikes me has if not ugly, at least hideously post-modern.

I'll even donate a spot of server space to the cause if you don't have
it already.


On Tue, 30 Nov 2004 15:58:48 -0500, Mark Stosberg [EMAIL PROTECTED] wrote:
 On Tue, Nov 30, 2004 at 08:53:51PM +, Tim Bunce wrote:
 
  I don't get it. Can someone give me some small but real examples
  of the problem that's being solved here?
 
 To give the database name with DBD::Pg, you use:
 
 dbname=$dbname
 
 With mysql, it's different
 
database=$database
 
 With this ConnectFoo module, it could be the same:
 
   dbname = $dbname,
 
 
 Mark
 
 


-- 
David L Nicol
Happy hacking!
http://www.amazon.com/exec/obidos/ASIN/0596002874/tipjartransactioA/