Re: Is this a DBD::Pg problem or Postgres problem?
On 2023-10-15 23:26:03 +, Johnson, Bruce E - (bjohnson) wrote:
> I’ve move an application to Postgres for the database and am having a problem
> with using named parameters.
>
> My code:
>
> my $csr_newinv =$lda->prepare("insert into inventory (inv_id, instid, av_id,
> vials, volume, expiration_date, update_date) values (nextval(invid_seq),:INST,
> :AV,:VI,:VO, to_date('$exp','MM-DD-'), now()) returning inv_id as
> :NEWID");
> my $newid;
> $csr_newinv->bind_param(':INST',$instid);
> $csr_newinv->bind_param(':AV',$av_id);
> $csr_newinv->bind_param(':VI',$vials);
> $csr_newinv->bind_param(':VO',$volume);
> $csr_newinv->bind_param_inout(':NEWID',\$newid,"SQL_NUMERIC");
> $csr_newinv->execute();
That doesn't look correct. PostgreSQL returns the result of "...
returning ..." as a result set.
So you would normally do something like
my $csr_newinv = $lda->prepare("insert ... returning inv_id")
$csr_newinv->execute()
$result = $csr_newinv->fetchrow_hashref();
$newid = $result->{inv_id};
> [Sun Oct 15 16:01:01.059801 2023] [cgi:error] [pid 814746:tid 814746] [client
> 10.139.39.203:49782] AH01215: DBD::Pg::st execute failed: ERROR: syntax error
> at or near "$5": /home/allwebfiles/perl/edit_inst_inv2.pl, referer: https://
> avi.pharmacy.arizona.edu/a/edit_inst_inv.pl
> [Sun Oct 15 16:01:01.059943 2023] [cgi:error] [pid 814746:tid 814746] [client
> 10.139.39.203:49782] AH01215: LINE 1: ...te('10-15-2023','MM-DD-'), now())
> returning inv_id as $5: /home/allwebfiles/perl/edit_inst_inv2.pl, referer:
> https://avi.pharmacy.arizona.edu/a/edit_inst_inv.pl
>
> Is $5 referring to :NEWID ?
Yes. And a parameter isn't allowed in this position, only an identifier
(column alias).
> based on my reading of the Postgres Insert syntax I think it should be
> correct..
I think something like that works in PL/PgSQL. But in SQL you need to
fetch the result.
> (also, I do not know if the error logging is a DBD::Pg thing or Postgres
> thing,
> but it very hard to follow these kinds of errors compared to DBD::Oracle )
Having nested output from three different systems in the same line is a
bit confusing, yes. It becomes clearer if you know where the boundaries
are:
Apache: [Sun Oct 15 16:01:01.059801 2023] [cgi:error] [pid 814746:tid 814746]
[client 10.139.39.203:49782] AH01215:
Perl/DBD: DBD::Pg::st execute failed:
Postgres: ERROR: syntax error at or near "$5"
Perl/DBD: /home/allwebfiles/perl/edit_inst_inv2.pl,
Apache: referer: https:// avi.pharmacy.arizona.edu/a/edit_inst_inv.pl
Also, PostgreSQL error messsages contain context, so you know that the
> LINE 1: ...te('10-15-2023','MM-DD-'), now()) returning inv_id as $5
tells you wich line the error occured in (which is very handy on long
multi-line SQL queries) and the next line (which you didn't quote) shows
you where in the line the error was (with an ^ character pointing at
$5).
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
Re: Advice or best practice for updating multiple rows with a single query
On 2023-08-28 08:57:51 +0200, Ritter wrote:
> here is my example code trying to update multiple rows with a single
> query. Since the number of lines to be updated varies constantly, both
> the placeholders and the data need to be generated dynamically with
> each call.
>
> Based on the following example
> (https://www.geeksengine.com/database/data-manipulation/update-multiple-rows-one-query-part1.php)
> I tried to map it with the following code.
>
> — code —
[...]
>my $when_clause = join"\n\t", map { "when ? then ?" } (keys
> %{$db_values_to_be_updated});
>my @placeholders = ( join',', ('?') x (keys %{$db_values_to_be_updated}));
[...]
>my $sql = <<"EOF_INPUT";
> UPDATE condats SET
> fw_obj_id =
> CASE condats.cust_id
> $when_clause
> ELSE fw_obj_id
> END
> WHERE condats.cust_id IN (@placeholders);
> EOF_INPUT
[...]
>my @x = map { $_, $db_values_to_be_updated->{$_} } keys
> %{$db_values_to_be_updated};
[...]
>my @y = (keys %{$db_values_to_be_updated});
[...]
>my $sth = $dbh->prepare($sql);
>$sth->execute((map { $_, $db_values_to_be_updated->{$_} } (keys
> %{$db_values_to_be_updated})), keys %{$db_values_to_be_updated}) or die
> $DBI::errstr;
[...]
> }
>
> ———
>
> What I don't like is the awkward way of dynamically creating the
> placeholders for "$sth = $dbh->prepare($sql)" and how the data is
> passed to $sth->execute(). There is one part „feeding" the values for
> CASE and another part „feeding“ the values for "WHERE condats.cust_id
> IN ('1001','1234','4321’)“.
>
> Possibly only a "smarter" data structure is needed, from which DBI on
> the one hand takes the data for the CASE part and from another part of
> the data structure data for the WHERE clause?
>
> I wonder if there is a smarter way to create the DBI-placeholder and
> how to hand over the data to $sth->execute() in one shot.
I don't think there is a much better way to do it with DBI alone. It is
after all relatively low-level. There may be a higher level "SQL
construction" package out there, maybe something like SQLAlchemy for
Python (which I don't particularly like, but clearly many people do).
> Any recommendations/best practices?
What I would do differently than you is arrange the code so that
construction of the placeholders is right next to construction of the
parameter lists. for example, in your code there are 40 lines between
>my $when_clause = join"\n\t", map { "when ? then ?" } (keys
> %{$db_values_to_be_updated});
and
>my @x = map { $_, $db_values_to_be_updated->{$_} } keys
> %{$db_values_to_be_updated};
which makes it hard to ascertain that those two lines actually match (the
non-descriptive name @x doesn't help).
So I would change that to something like
my $when_clause = ...
my @when_params = ...
my $where_clause = ...
my @where_params = ...
and then tie it all together
my $sql = <<"EOF_INPUT";
UPDATE condats SET
fw_obj_id =
CASE condats.cust_id
$when_clause
ELSE fw_obj_id
END
WHERE $where_clause
EOF_INPUT
my $sth = $dbh->prepare($sql);
$sth->execute(@when_params, @where_params) or die $DBI::errstr;
There are a few other nitpicks I have with your code (like, why do you
use map in one line and x in the next for the same purpose? Why is
@placeholders a list with one member, why do you assign to variables and
then compute the same thing again?), but I realise that this is
throw-away test code and those details have nothing to do with your
question.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
Re: Strange issue with inserting varchar2()
On 2021-05-27 23:35:32 +, Bruce Johnson wrote: > The column in question is a varchar2(4000) so I am truncating the > input to 4000 bytes. > > (via use “bytes; $string=substr($orig,0,4000); ” in the section where > I actually truncate the string.) > > When I do the insert I get an ORA-12899: value too large for column > "AWARD"."PRECEPT_NOMINATIONS"."ONCOLOGY_COMMENTS" (actual: 4054, > maximum: 4000) error Is $orig actually UTF-8 encoded at that time? If it only contains codepoints <= U+00FF, it might be 1 byte/character, but when sent to Oracle the codepoints between U+0080 and U+00FF will still be encoded as two bytes. Something like: my $bs = encode_utf8($orig); my $sbs = substr($bs, 0, $len); my $truncated = decode_utf8($sbs, FB_QUIET); would be safer. Also, waht encoding do you use in Oracle? UTF8 isn't really UTF-8: It's a weird double-encoding which may take up to 6 six bytes for non-BMP characters. To get real UTF-8, use AL32UTF8. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Hunting down (possible) memory leak in DBD::Oracle
On 2017-12-19 20:55:30 +, Fennell, Brian wrote: > And, also with the log level set to 15 here are the LAST 200 lines [...] > -> fetchrow_array for DBD::Oracle::st > (AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010 > dbd_st_fetch 6 fields... > dbd_st_fetched 6 fields with status of 0(SUCCESS) > field #1 with rc=0(OK) > > 3abd340 (field=0): '1105427' > field #2 with rc=0(OK) > > 3abd340 (field=1): '1268254' > field #3 with rc=0(OK) > > 3abd340 (field=2): 'sampl...' > field #4 with rc=25196(UNKNOWN RC=25196)) > OCIErrorGet(3b535c8,1,"",7ffd7b72b7cc,"ORA-01403: no data found > ",1024,2)=SUCCESS > OCIErrorGet after ORA-25196 error on field 4 of 6, ora_type 2 > (er1:ok): -1, 1403: ORA-01403: no data found > > OCIErrorGet(3b535c8,2,"",7ffd7b72b7cc,"ORA-01403: no data found > ",1024,2)=NO_DATA > -- HandleSetErr err=1403, errstr='ORA-01403: no data found (DBD ERROR: > ORA-25196 error on field 4 of 6, ora_type 2)', state=undef, undef > > 3abd340 (field=3): undef Can you check the actual contents of any rows in tableA with field1=1268254 and field2='sampl...'? The error occurs in field3 which is numeric, but it's adjacent to field4 which is VARCHAR2(4000). I wonder if an overflow is possible if field4 is actually close to 4000 characters and it is expanded further by character encoding. Which raises the next question: What are the database and client encodings set to? hp -- _ | Peter J. Holzer | I think we need two definitions: |_|_) | WSR - Softwaredevelopment | 1) The problem the *users* want us to solve | | | und Projektunterstützung | 2) The problem our solution addresses. __/ | h...@wsr.ac.at |-- Phillip Hallam-Baker on spam signature.asc Description: PGP signature
Re: (Fwd) DBI Dilemma
On 2015-08-18 14:21:39 +, Adkins, Blake wrote: My script works on the .csv file using dbi:CSV:f_dir and pointing to different files for different projects. [...] I'm using the DBI module in Perl, would this be better with the other modules mentioned? [the other module mentioned being DBD::CSV] The DBD modules are database specific drivers for DBI. So if you use DBI to connect to dbi:CSV:f_dir=foo, it will call DBD::CSV behind the scenes (similarily, if you connected to dbi:Pg:dbname=foo, it would call DBD::Pg, etc.). The DBD modules are not intended to be used standalone, you always use them indirectly through DBI. See the perldoc page for DBI which explains the architecture quite well. hp -- _ | Peter J. Holzer | I think we need two definitions: |_|_) | WSR - Softwaredevelopment | 1) The problem the *users* want us to solve | | | und Projektunterstützung | 2) The problem our solution addresses. __/ | h...@wsr.ac.at |-- Phillip Hallam-Baker on spam signature.asc Description: Digital signature
Re: Help with using Triggers MySql/Innodb
On 2015-02-26 06:42:33 -0800, david wrote: This may be the wrong mailing list for this question, so I apologize in advance for breaking some rule. Strictly speaking yes. The trigger syntax is independent of the method used to access the database (DBI in our case). I'm trying to get a trigger to work. My environment is a Perl DBI interface to a MySql database on Centos 6. I use the InnoDB database. I have two tables involved in this operation: table: House, with fields: HouseID INT AUTO_INCREMENT PeopleCount INT other fields not relevant to this discussion Table: People, with fields PeopleID INT AUTO_INCREMENT HouseID INT (shows which house the person belongs to) Name VARCHAR(30) other fields not relevant to this discussion Whenever I insert (or delete) a row to the People table, I want the PeopleCount in the House (identified by the HouseID in the people table) to be incremented or decremented. My latest attempt is a trigger: CREATE TRIGGER after_insert_people AFTER INSERT ON People FOR EACH ROW BEGIN UPDATE House SET PeopleCount = PeopleCount + 1 WHERE HouseID = People.HouseID; END The trigger gets triggered, but a diagnostic says that People.HouseID is an undefined column. See http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html: | Within the trigger body, the OLD and NEW keywords enable you to access | columns in the rows affected by a trigger. OLD and NEW are MySQL | extensions to triggers; they are not case sensitive. So that should be UPDATE House SET PeopleCount = PeopleCount + 1 WHERE HouseID = NEW.HouseID hp -- _ | Peter J. Holzer | I think we need two definitions: |_|_) | WSR - Softwaredevelopment | 1) The problem the *users* want us to solve | | | und Projektunterstützung | 2) The problem our solution addresses. __/ | h...@wsr.ac.at |-- Phillip Hallam-Baker on spam signature.asc Description: Digital signature
Re: I have a weird issue with a script running under cron
On 2014-02-19 11:14:30 -0800, Bill Ward wrote:
Remember that use is a kind of BEGIN block. You have to set the ENV
variables in a BEGIN block before the use line.
Also, on several Unix-like systems (including Solaris and Linux/x86_64),
changing LD_LIBRARY_PATH while a process is running has no effect. So
this:
On Feb 19, 2014 9:23 AM, Bruce Johnson john...@pharmacy.arizona.edu
wrote:
$ENV{ORACLE_HOME}=/usr/lib/oracle/11.2/client64;
$ENV{ORACLE_SID}=phmweb;
$ENV{LD_LIBRARY_PATH}=/usr/lib/oracle/11.2/client64/lib;
use DBI;
[...]
my $dbh = DBI-connect( dbi:Oracle:host=$dbhost, $login, $dbpass,
{ RaiseError = 1 } );
doesn't work even with a BEGIN {} block around the assignments to %ENV.
You have to set LD_LIBRARY_PATH in a wrapper script.
hp
--
_ | Peter J. Holzer | I think we need two definitions:
|_|_) | WSR - Softwaredevelopment | 1) The problem the *users* want us to solve
| | | und Projektunterstützung | 2) The problem our solution addresses.
__/ | h...@wsr.ac.at |-- Phillip Hallam-Baker on spam
signature.asc
Description: Digital signature
Re: I have a weird issue with a script running under cron
On 2014-02-17 22:43:58 +, Bruce Johnson wrote: On Feb 17, 2014, at 2:48 PM, John D Groenveld jdg...@elvis.arl.psu.edu wrote: The OP shouldn't need to set a LD_LIBRARY_PATH so long as he built DBD::Oracle with the correct runtime link path, but a simple shell script to see which libraries aren't resolving would be a useful test: #!/bin/ksh /bin/env - /usr/bin/ldd /usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so Everything works on the command line interactively. The error only happens when the script is run via cron; Yes, so obviously you should run John's little script via cron, too. What is the result? hp -- _ | Peter J. Holzer | I think we need two definitions: |_|_) | WSR - Softwaredevelopment | 1) The problem the *users* want us to solve | | | und Projektunterstützung | 2) The problem our solution addresses. __/ | h...@wsr.ac.at |-- Phillip Hallam-Baker on spam signature.asc Description: Digital signature
Re: PostgreSQL driver DBD::Pg releases version 3.0.0
On 2014-02-06 12:30:15 +1100, Ron Savage wrote:
'Major change in UTF-8 handling' is right. I was:
I haven't checked the docs yet, just my gut feeling how it should work.
o Declaring the Pg db as utf8
I guess this means setting pg_enable_utf8?
o Encoding outgoing data
Eg: map{($_ = encode('utf-8', $$hashref{$_}) )} keys %$hashref
That seems superfluous to me.
If you set pg_enable_utf8 I would expect the DBD to translate between
perl character strings (aka 'utf8 strings', but you aren't supposed to
know that and in newer perls the difference shouldn't matter any more)
and whatever encoding the database expects.
o Decoding incoming data
$$item{$_} = decode('utf-8', $$item{$_} || '') for keys %$item
Same here. With pg_enable_utf8 I would expect to get character strings,
not byte strings, so no decode should be necessary.
hp
--
_ | Peter J. Holzer | I think we need two definitions:
|_|_) | WSR - Softwaredevelopment | 1) The problem the *users* want us to solve
| | | und Projektunterstützung | 2) The problem our solution addresses.
__/ | h...@wsr.ac.at |-- Phillip Hallam-Baker on spam
signature.asc
Description: Digital signature
Re: ODBC Driver failing?
On 2013-06-26 14:55:31 -0500, Dan Bent wrote: $ strace -o strace.log isql -v prod1 user password usage: [ mid sid level] ... The strace equivalent to strace is called tusc on HP-UX. I have it installed in /usr/local/bin which implies that I compiled it myself (almost exactly 10 years ago), but I don't seem to have the source code any more and don't remember where I got it. Unless one of your co-admins installed it you'll probably have to google (or duckduckgo or whatever) for it. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Prepared statements- variable number of placeholders
On 2012-10-22 15:16:47 +0800, Tim Bowden wrote:
Is it possible, and if so, how can I set the number of placeholders in a
prepared statement at runtime?
IE, given something like:
my $sth = $dbh-prepare(INSERT INTO $table ($fields) VALUES (?,?,?));
which is fine if I know how many fields I'm inserting into, but what if
I don't know till runtime how many fields? How can I put the (?,?,?)
into a variable and have it recognised as placeholders?
You could do something like
my $placeholders = '(?,?,?)';
my $sth = $dbh-prepare(INSERT INTO $table ($fields) VALUES $placeholders);
but I guess you thought of that and your question is really
How can I get $placeholders from $fields?
If I have a variable number of fields I usually have them in array, not
a scalar, so I can just count the members:
$fields = join(',', @fields);
$placeholders = '(' . join(',', map('?', @fields)) . ')';
my $sth = $dbh-prepare(INSERT INTO $table ($fields) VALUES $placeholders);
(and then you can get rid of $fields and $placeholders and do it all in
a single line)
hp
--
_ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern.
__/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
Re: DBD::mysql::st bind_param failed: Illegal parameter number
On 2012-08-10 17:08:07 -0400, Rudolf Lippan wrote: On Friday, August 10, 2012 at 03:30:16 PM, Seven Reeds wrote: I am on a RedHat box with perl 5.8.8. I have just started usinf bind_param but am having an issue. Is RedHat still shipping Perl 5.8.8? The current release of RHEL (6) is shipping with perl 5.10.1, but RHEL 5 will be supported until 2017, so people aren't in a hurry to upgrade. Some are probably still running RHEL 4 or even RHEL 3. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: DBD::Oracle build on aix 6.1 ( host is 64 bit db server )
On 2012-05-20 16:05:31 -0400, John Scoles wrote: Short answer no. I would not see any reason why you would need to install the instance client as well. In the long run it depends on what you are doing. If you are going to use perl for only loacal access then no need for the instanct cleint. If you want to use Perl for say connecting via a web server or alike using the instant client may be what you want. Connect via a web server? How does that work? Apart from that, I think the instant client is either just the same as the normal client distributed with the RDBMS (just differently packaged) or a subset. It is certainly possible to connect to a remote database with the normal client. IMHO there are two reasons why you may want to install instant client even on a server where the RDBMS is already installed: * It's nice to have the same client everywhere. It also allows you to upgrade the server independently of the client (and vice versa). * In a server installation the client parts (libraries, sqlplus, etc.) are by default only accessible by the dba group. Your DBA may be unwilling to change that. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Creating new database handles with mod_perl
On 2012-05-19 20:06:44 -0400, Schultz, Len wrote: I've run into an issue when stress testing mod_perl that the database connections are going away. I suspect that processes are sharing database connections, causing the issue. But I've followed all instructions for Apache::DBI, and can't figure this out. I'm making the connections in the child process and not in startup.pl. But when I examine the $dbh returned by each child from the DBI-connnect, the address is the same for every httpd process. Firstly, if this is working properly and reconnecting for each process, should the address returned by DBI-connect be different for each child process? No. Each process has its own address space. The same address in two different processes does not (generally) refer to the same memory location. If both processes allocate the handle shortly after the fork it is very likely that both get the same address. To see whether they are using the same connection, you should use lsof (or netstat) to look at the TCP connections (I assume you are using the TCP driver and not the bequeath driver): The tuple (local address, local port, remote address, remote port) is unique for each connection. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: [DBD::Oracle] ORA-02005 on array insert
On 2012-03-20 15:25:43 +0100, Peter J. Holzer wrote: On 2012-03-20 13:19:14 +, Martin J. Evans wrote: On 20/03/12 12:12, Peter J. Holzer wrote: Anyway, since the upgrade sometimes (not always) array inserts returned the error DBD::Oracle::st execute_array failed: ORA-02005: implicit (-1) length not valid for this bind or define datatype (DBD ERROR: OCIBindByName) Here is a test script which semi-reliably (= 50% of the time) produces the error message: [...] I've run this a couple of dozen times and it never has failed for me. I was using Linux Ubuntu 10.10, Perl 5.10.1 (with threads), DBD::Oracle (from subversion trunk) and Oracle Instant Client 11.2. Upgrading the client to 11.2 would have been one of the next things on my list of things to try. Currently we use instant client 10.2.0.5 (the database is also 10.2). I'll try that tomorrow. It's probably still worth investigating if the problem exists only with a 10.2 client. Just tried it with instant client 11.2.0.3.0. I can still reproduce the problem. So it doesn't seem a 10.2 vs. 11.2 issue. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
[DBD::Oracle] ORA-02005 on array insert
A few months ago we upgraded two of our servers from RHEL 4 to RHEL6.
Unfortunately the upgrade involved the OS, the Perl version (5.8.8 -
5.10.1), the Oracle client version (still 10g, but now instant client)
and DBD::Oracle (1.19 - 1.30 (now 1.38)), and we didn't note the
problem at once, so it's unclear which of the many changes is the
culprit, but I suspect it's a bug in DBD::Oracle.
Anyway, since the upgrade sometimes (not always) array inserts returned
the error
DBD::Oracle::st execute_array failed: ORA-02005: implicit (-1)
length not valid for this bind or define datatype (DBD ERROR:
OCIBindByName)
Here is a test script which semi-reliably (= 50% of the time) produces
the error message:
#!/usr/bin/perl
use warnings;
use strict;
use DBI;
my $dbi_credential_file = $ARGV[0];
my ($data_source, $username, $auth) = read_cred($dbi_credential_file);
my $conn_attr = {
AutoCommit = 0,
PrintError = 0,
RaiseError = 1
};
$ENV{NLS_LANG} = '.AL32UTF8'; # real UTF-8
my $dbh = DBI-connect($data_source, $username, $auth, $conn_attr);
$dbh-{FetchHashKeyName} = 'NAME_lc';
$dbh-do(create table bug_ora_02005_$$ (
set_id number not null,
data_id number not null,
time number,
period_start number,
period_end number,
real number,
string number,
coordtypenumber,
constraint bug_ora_02005_${$}_pk primary key(data_id, set_id)
)
);
my $n = 900;
my $ct_none = undef;
for my $run (1 .. 1_000) {
print STDERR run: $run\n;
my $data_ids;
my @set_ids;
for (1 .. $n) {
push @$data_ids, $_;
push @set_ids, $run;
}
my $sth = $dbh-prepare_cached(insert into bug_ora_02005_$$(period_start,
period_end, data_id, set_id, coordtype)
values(?,
?, ?, ?, ?));
$sth-execute_array({}, undef, undef, $data_ids, \@set_ids, $ct_none);
}
$dbh-commit();
$dbh-do(drop table bug_ora_02005_$$);
$dbh-disconnect();
sub read_cred {
my ($fn) = @_;
open(my $fh, $fn) or die cannot open $fn: $!;
my $line = $fh;
my @cred = split(/[\s\n]+/, $line);
return @cred;
}
__END__
Some notes:
ARGV[0] is supposed to be the name of a file containing space-separated
connect data, e.g.:
dbi:Oracle:ORCL scott tiger
When the script fails, it always fails on the second iteration of the
loop. If it gets through the second iteration it completes all 1000
iterations successfully.
When the call to prepare_cached is replaced by a simple prepare, the
script fails less often.
I have run the script with DBI_TRACE=4, but I don't see any significant
difference between successful and unsuccessful runs.
hp
--
_ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern.
__/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
Re: [DBD::Oracle] ORA-02005 on array insert
On 2012-03-20 13:19:14 +, Martin J. Evans wrote: On 20/03/12 12:12, Peter J. Holzer wrote: A few months ago we upgraded two of our servers from RHEL 4 to RHEL6. Unfortunately the upgrade involved the OS, the Perl version (5.8.8 - 5.10.1), the Oracle client version (still 10g, but now instant client) and DBD::Oracle (1.19 - 1.30 (now 1.38)), and we didn't note the problem at once, so it's unclear which of the many changes is the culprit, but I suspect it's a bug in DBD::Oracle. Anyway, since the upgrade sometimes (not always) array inserts returned the error DBD::Oracle::st execute_array failed: ORA-02005: implicit (-1) length not valid for this bind or define datatype (DBD ERROR: OCIBindByName) Here is a test script which semi-reliably (= 50% of the time) produces the error message: [...] I've run this a couple of dozen times and it never has failed for me. I was using Linux Ubuntu 10.10, Perl 5.10.1 (with threads), DBD::Oracle (from subversion trunk) and Oracle Instant Client 11.2. Upgrading the client to 11.2 would have been one of the next things on my list of things to try. Currently we use instant client 10.2.0.5 (the database is also 10.2). I'll try that tomorrow. It's probably still worth investigating if the problem exists only with a 10.2 client. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Maintaining simultaneous support for two Oracle versions in DBI
On 2011-12-05 07:08:01 -0500, John Scoles wrote: From: b...@wards.net Date: Sun, 4 Dec 2011 19:15:20 -0800 Subject: Re: Maintaining simultaneous support for two Oracle versions in DBI To: smi...@latfor.state.ny.us CC: dbi-users@perl.org Aren't the Oracle driver libraries backward compatible? If you link DBD to the Oracle 11 drivers, won't it still be able to connect to an Oracle 8 server? No they usually only go back one version. So 9 can connect to 8~10, 10 to 9~11 ect at least that has been the trend with the last few clients at least with OCI. We access an Oracle 8.1 database with 10.2 clients and a 10.2 database with an 8.1 client, so that works at least for simple stuff (IIRC you have to be careful about charsets and there are some problems with LOBs, so YMMV). Accessing an 8.1 database with an 11g client definitely doesn't work. I'm not sure whether an 11g client can access a 9i database. For a transition from a 9i to an 11g database I would first try to use an 11g client and fall back to a 10g client if that doesn't work. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: DB2-DBD error messages
On 2011-03-17 23:47:13 -0700, Jonathan Leffler wrote: On Thu, Mar 17, 2011 at 04:03, computacenter.geisselbre...@daimler.comwrote: do anyone has experience with such messages when i try to install DB2-DBD driver ? It happens after perl Makefile.PL command was executed. zwtxbt05@videv263perl Makefile.PL Usage: Cwd::fastcwd() at /usr/opt/perl5/lib/5.8.8/aix-thread-multi/DynaLoader.pm line 253. BEGIN failed--compilation aborted at /usr/opt/perl5/lib/site_perl/5.8.8/aix-thread-multi/DBI.pm line 268. Compilation failed in require at Makefile.PL line 13. BEGIN failed--compilation aborted at Makefile.PL line 13. DB2_Home was exported to /home/zwtxbt05/sqllib I've not seen that message, but it appears that something tried to use the function 'fastcwd()' from a package 'Cwd' that is not available with your Perl. You appear to be using Perl 5.8.8. The module is part of the Perl core (and fastcwd() is part of the module) according to http://perldoc.perl.org/Cwd.html. However, was it in 5.8.8? % corelist Cwd Cwd was first released with perl 5 Cwd::fastcwd has also existed for a long time and I see no indication in the docs that it may not be available on some platforms. But the error message is Usage: Cwd::fastcwd() so I guess that the function is available but called incorrectly (probably with a parameter although it doesn't expect one). hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: How to set LD_LIBRARY_PATH
On 2010-05-31 15:52:21 +0200, Peter J. Holzer wrote:
On Linux (at least i386[1]) the loader uses the current value of
LD_LIBRARY_PATH when it is asked to load an .so file. So you can change
LD_LIBRARY_PATH just before you want to load the library.
On Solaris the loader uses the value of LD_LIBRARY_PATH at program
startup. Changing LD_LIBRARY_PATH during runtime has no effect on the
process itself, just its children. I don't know why this is the case,
but I guess that LD_LIBRARY_PATH is split into components when the first
library (probably libc) and after that the components aren't used any
more.
hp
[1] I seem to remember that I ran into problems either on x86_64 or on
power, but I don't remember the details. If it's x86_64, I should be
able to test it (don't have access to power-based systems any more).
Just for the record: That problem does exist on Linux/x86_64.
I just ran into the issue moving a bunch of CGI scripts from a
Linux/i386 box to a Linux/x86_64 box.
They do something like:
if (open(E, $offl_root/dbi/env)) {
while (E) {
chomp;
my ($k, $v) = split(/=/);
$ENV{$k} = $v;
printlog('DEBUG', setting $k=$v);
}
}
my ($data_source, $username, $auth) = read_cred($offl_root/dbi/usradm);
my $dbh;
eval {
$dbh = DBI-connect($data_source, $username, $auth,
{ AutoCommit = 0,
PrintError = 1,
RaiseError = 1
}
);
};
if ($@) {
...
}
where $offl_root/dbi/env contains several environment variables, among
them ORACLE_HOME and LD_LIBRARY_PATH.
On Linux/i386 this works fine. On Linux/x86_64 it fails with the error
message
connection to dbi:Oracle:wpdb.wsr.ac.at as usradm failed:
install_driver(Oracle) failed: Can't load
'/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so'
for module DBD::Oracle: libocci.so.10.1: cannot open shared object file:
No such file or directory at
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.
despite the fact that libocci.so.10.1 is in the LD_LIBRARY_PATH.
If I set LD_LIBRARY_PATH in the Apache config, it works as expected.
So I conclude that Linux/x86_64, like Solaris, but unlike Linux/i386,
ignores changes to LD_LIBRARY_PATH after a program is started.
hp
--
_ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern.
__/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
Re: DBD::Oracle dbd_st_execute slow speed
On 2010-12-16 07:15:02 -0500, John Scoles wrote:
On 16/12/2010 7:06 AM, Ludwig, Michael wrote:
-Original Message-
From: John Scoles
More likely SQLplus is spawning a thread while DBD::Oracle does not.
You mean performing the actual work in the background while making
the prompt available for the user to enter the next command?
yep It might I could ask an oracle buddy of mine who works on it if
you want?
Its been a while since the last time I tried to get OCI treads to
work but in the case of an update statement it would make perfect
sense to use them for that as there is no 'return' from the DB like
'select' statement.
Sqlplus does display the result of the insert (either 1 row created.
or a suitable error message (like ORA-1: unique constraint
(FIWPROD.SYS_C0028271) violated) before the next prompt, so I doubt
very much that it does anything in the background.
SQLplus might also be using the array interface under the hood for
all inserts which could be faster.
It might, but for a single row that shouldn't make much difference.
The 10046 trace will be interesting ...
My guess is that oracle uses an index when the query comes from sqlplus,
but doesn't when the query comes from perl. It is sometimes hard to
determine why Oracle chooses a specific plan.
Oh, and I think it hasn't been mentioned that you can display plans for
queries which have already been executed.
First find the query:
sys...@dbi:Oracle:fiw select sql_id, child_number from v$sql
where sql_text= 'select * from setcoords sc where sc.base_set=:p1';
+-++
|SQL_ID |CHILD_NUMBER|
+-++
|9bvzsg998zgy5|0 |
|9bvzsg998zgy5|1 |
|9bvzsg998zgy5|2 |
+-++
[3 rows of 2 fields returned]
then get the plan for the query:
sys...@dbi:Oracle:fiw SELECT * FROM
table(DBMS_XPLAN.DISPLAY_CURSOR('9bvzsg998zgy5', 2));
+---+
|PLAN_TABLE_OUTPUT
|
+---+
|SQL_ID 9bvzsg998zgy5, child number 2
|
|-
|
|select * from setcoords sc where sc.base_set=:p1
|
|
|
|Plan hash value: 1863347061
|
|
|
|---|
|| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
||
|---|
|| 0 | SELECT STATEMENT | | | | 7529 (100)|
||
||* 1 | TABLE ACCESS FULL| SETCOORDS | 87312 | 1961K| 7529 (2)| 00:01:31
||
|---|
|
|
|Predicate Information (identified by operation id):
|
|---
|
|
|
| 1 - filter(SC.BASE_SET=TO_NUMBER(:P1))
|
|
|
+---+
[18 rows of 1 fields returned]
You need special privileges for that, though. I don't think a normal
user can do it even for their own queries.
hp
--
_ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern.
__/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
Re: SQL errors not making it back to Perl
On 2010-09-10 07:01:26 -0400, Martin Gainty wrote: Your SQLNET.ORA file should contain the following lines to produce a client side trace file: trace_level_client = 10 I don't think SQLNET.ORA will have much effect on MS SQL Server ;-). hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Reg DBI Problem
On 2010-09-06 17:50:44 +0530, Raghavendra Kalimisetty wrote:
Hope you will provide solution to our problem.
We have recently moved from Oracle 10g to Oracle 11g. Now we are facing an
issue when trying to connect to DB through Perl.
Here is the error we got:
DBI connect('host=localhost;sid=omc','omcdbsys',...) failed: ORA-30136:
Drop-in compatibility is not supported for this application (DBD ERROR:
OCIServerAttach) at mvipack.pl line 60
We have DBI-1.50 and DBD-1.16 installed. Let us know how to proceed to
overcome the above error.
DBD::Oracle 1.16 is about 6 years old and I think older than Oracle 11g,
so in addition to what John wrote it might help to upgrade to a current
release.
hp
--
_ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern.
__/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
Re: Out of memory! Out of memory! Callback called exit at ...
On 2010-08-12 10:19:37 -0700, Bob McGowan wrote: On 08/12/2010 09:55 AM, Jared Still wrote: On Thu, Aug 12, 2010 at 12:50 AM, Ludwig, Michael michael.lud...@delphi-mb.de wrote: Do you have a minimal example to reproduce the failure so people can try this at home, or rather, inspect the code? [...] In point of fact, which I failed to mention (mia culpa), I've debugged down to the point where the call is made to 'selectall_arrayref()'. How many rows does this return? (Or rather: How many rows would it return if it didn't run out of memory?) If each row contains a 20 MB blob you don't need many rows to run out of memory. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Problem with a conection...
On 2010-06-08 15:41:11 +0100, Martin Hall wrote: It sounds to me as if you're running into something else. As far as I know, the 2396 error should not be triggered during a select as the connection is not 'idle'. Could it be that your select statement is finishing more quickly than you thought and you're not picking it up? If that was the case, then your session could be described as 'idle' I think it is more likely that the select in question is the first select *after* the connection has been closed because it is idle. As long the script is busy doing something else it won't notice that connection has gone away, but when it then tries to send the next request to the db it gets the error. On 08/06/2010 14:54, Juan Carlos García wrote: I have a program that takes around six or seven hours to execute. I don't kwon when but, during the execution of a SELECT statement, I get this error: DBD::Oracle::db prepare failed: ORA-02396: exceeded maximum idle time, please connect again (DBD ERROR: OCIStmtExecute/Describe) [for Statement The cause of the error is clear for me. But the problem is, how to resolve it? Either ask your DBA to increase the allowed idle time or change your script to reconnect if necessary. A variant of the latter is to explicitely close the connection before the long idle time and reopen it when you need the database again. What should I do to reconnect with the database when I suffer a disconnection? The same thing you did to connect to the database in the first place. You may also have to redo the transaction which was in progress at the time of the disconnect (so it's probably a good idea to structure your application in such a way that there are no long idle times in the middle of a transaction). hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Some DBI question -
On 2010-05-30 17:33:39 +1200, newbie01 perl wrote: Am trying out DBI for Oracle and just want to know if for example, I need to include column formatting stuff etc., is it best to just put them into a .sql file and then execute the .sql file instead of placing them in the Perl DBI script? By column formatting stuff you mean commands like column foo format a20 ? These are sqlplus commands, not sql commands, i.e., they are features of the query tool, not the database. Consequently, they are not available in perl. To format data in Perl, use Perl features, like printf, formats, various templating toolkits, etc. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: How to set LD_LIBRARY_PATH
On 2010-05-30 10:47:50 -0700, Marilyn Sander wrote: On May 29, 2010, at 6:24 AM, Paul Johnson wrote: On Fri, May 28, 2010 at 11:23:32AM -0700, Marilyn Sander wrote: On May 28, 2010, at 9:33 AM, Paul Johnson wrote: On Fri, May 28, 2010 at 06:14:38AM -0400, John Scoles wrote: You will have to set those values before your modules load. So you should stick them in the BEGIN and that should work ... except where it doesn't, such as on Solaris for example. Here, LD_LIBRARY_PATH (at least) really does need to be set before the process starts. You can do this by writing a shell wrapper, or re-execing your perl script if the value is not already set. Have you considered doing a require instead of a use. With require, the loading is done at run time, and would be governed by the setting of LD_LIBRARY_PATH, at the time the require statement is executed. Just set LD_LIBRARY_PATH before doing the require. I'm afraid that you may have misunderstood what I wrote. There are times when you really do need to set the variable before the process starts. I did not misunderstand what you wrote. My reasoning was that the thing being loaded is a shared object (.so file). The system loader (ld) has to be invoked for loading a shared object. The .so file must be loaded into the (already running) perl process. While that may be possible from an external process, it is simpler to do it from the same process - so on the systems I know (Linux, Solaris) the shared library loader is just a library function, not an external tool. On Linux (at least i386[1]) the loader uses the current value of LD_LIBRARY_PATH when it is asked to load an .so file. So you can change LD_LIBRARY_PATH just before you want to load the library. On Solaris the loader uses the value of LD_LIBRARY_PATH at program startup. Changing LD_LIBRARY_PATH during runtime has no effect on the process itself, just its children. I don't know why this is the case, but I guess that LD_LIBRARY_PATH is split into components when the first library (probably libc) and after that the components aren't used any more. hp [1] I seem to remember that I ran into problems either on x86_64 or on power, but I don't remember the details. If it's x86_64, I should be able to test it (don't have access to power-based systems any more). -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: anyone accessing h2 java database with DBI?
On 2010-05-05 20:10:47 +0200, Alexander Foken wrote: On 05.05.2010 14:24, John Scoles wrote: That is what I would say you would have to go with as I could not find a C or C++ interface for it on my quick look at the site. There must be one someplace though?? Why should there be a C/C++ interface for a Database written entirely in Java? For the same reason why you might want a Java interface to a database written in C: So that you aren't forced to write your applications in the same language as the database. In the Unix world, ABIs are typically defined via a C interface: It's stable, it's compiler-independent, and every language has a way to call it. So when you have a C interface, you can write your application in any language you want (including Java). The same is not necessarily true for other languages (including Java). Of course for a server you don't need an ABI: You can (and maybe should) publish the network protocol instead and let people write their own client libraries. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Trying to safely compare user input name against database
On 2010-05-04 08:53:01 -0700, Larry W. Virden wrote: On May 4, 11:22 am, martin.ev...@easysoft.com (Martin Evans) wrote: I'd really avoid concatenating user supplied strings into your SQL. Use parameters instead: select * from my_table where last_name like ? then use bind_param or exeute(my_parameter_list). That sounds reasonable. I inherited this code, written about 10 years ago - it doesn't surprise me that a freshening is needed. You don't say what database this is. It is not always UPPER especially when the strings might be unicode (Oracle uses nls_upper). Other than that upper(column) = upper(?) is ok. This is ORACLE, and so perhaps it would be better to use nls_upper instead of upper. It depends. nls_upper has an extra parameter which lets you specify how the conversion should be done. This is for the cases where a simple 1:1 mapping is not good enough. I currently know of only two of them: * In German, lower case ß (one letter) maps to upper case SS (two letters), but the conversion is not reversible. * In most languages, lower case i (with dot) maps to upper case I (without dot), but in Turkish languages, i (with dot) maps to İ (with dot) and ı (without dot) maps to I (without dot). If your application has to deal with these characters AND has a way to distinguish which language-specific rules should be used, then use nls_upper. Especially the second condition is important: If there is no way (for the user or admin) to select the right rules, nls_upper gains you nothing and you might as well use upper. There is also the question of what you want to achieve by using upper: It looks like you want to find a string regardless of capitalization. But maybe you also want to ignore accents or other spelling variants (for example, in German ö can also be written oe). Now it depends not only on the language but on context and on the expectations of the user. And once you do that you might want to tolerate spelling errors ... hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: CLOB select with ora_pers_lob (truncation issue)
On 2010-04-20 09:50:48 -0400, Martin Gainty wrote: some confusion on the oracle documentation http://www.scribd.com/doc/21329014/Oracle10g-Develop-PLSQL-Program-Units-VOL2 which states CLOB Datatypes is for single byte character stream NCLOB is multibyte character stream based upon byte length of the supplied database character set I think this isn't quite correct. The distinction is that every Oracle database uses two character sets: The database character set and the national database character set. The former is used for storing char, varchar2 and clob data, and also for sql commands, stored procedures, etc. The latter is used for nchar, nvarchar2 and nclob data. Because the database character set is also used for parsing SQL, etc. it is more restricted: All the characters which are used in SQL keywords must be single-byte, and I think (but I'm not sure) they must also be US-ASCII on ASCII-based platforms. So UTF-8 can be used as a database character set, because in UTF-8 SELECT is 53 45 4c 45 43 54, same as in US-ASCII, but UTF-16 cannot, because in UTF-16(-BE) SELECT is 00 53 00 45 00 4c 00 45 00 43 00 54, and the SQL parser can't handle this. Before UTF-8 was supported, nvarchar and nclob (well, nlong actually, nclob didn't yet exist) were important, because for many languages there was no character set which was usable as a database character set *and* included all characters needed for the language. These days you can just use UTF-8 (AL32UTF8 in Oracle) and be done with it, although for some languages and some applications it may still be useful to distinguish between the two character sets. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Help on using on *nix and Windows
On 2010-04-19 21:21:45 +1200, newbie01 perl wrote: I need to convert a lot of UNIX scripts into Perl. No, you are trying to convert a Perl script written for Unix into a Perl script which runs on Windows. Most of them uses the UNIX's EOF/EOL functionality. These scripts are mostly used to connect to Oracle databases. At the moment, installing a DBI is not an option. Then why do you ask on the DBI mailing list? The scripts will be running locally on the servers so technically it should be able to connect to the database without using a password. I don't see what that has to do with your problem. Example of a very simple test script is as below. If I run the script on UNIX, it is all good. But on Windows, it gives the error was unexpected at this time. I assume that error comes from cmd.exe when it tries to execute the line sqlplus -S /as sysdba SQLEND It has been a long time since I wrote DOS/Windows style batch files, but I doubt that cmd.exe has learned about here documents since then. Can anyone please advise what I needed to change to get both running for UNIX and Windows? Use DBI. Seriously. use DBI. If you really can't, you could write the input to sqlplus into a temporary file and then invoke it like sqlplus -S /as sysdba $tmpfile. Or you could use IPC::Open2 (but I'm not sure whether that works on Windows). hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Perl coredump when using ora_dbh_share to connect to Oracle DB's
On 2010-04-01 09:43:32 +0200, Wesley Schwengle wrote: On 01.04.10 03:02 John Scoles wrote: Hard to say exatly are you sure you are connecting to oracle corretly?? use DBI; my $dsn = 'DBI:Oracle:host=localhost;port=1527;sid=XXX'; is a rather odd dsn I always the the method above, although I don't define the host and port part, since it is known in tnsnames. But the dsn part looks normal to me :) If you want to use tnsnames, shouldn't that be: my $dsn = 'DBI:Oracle:XXX'; instead of my $dsn = 'DBI:Oracle:sid=XXX'; ? hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: parameter issues in DBD:Oracle
On 2010-03-26 11:13:14 -0700, Bruce Johnson wrote:
On Mar 26, 2010, at 11:01 AM, Bruce Johnson wrote:
Does the DBD enclose all parameters in ''s?
Not really. It passes the parameters separately from the query.
Am I actually trying to execute:
select reserved_id, reserver, purpose, to_char(starttime, 'HH:MI'),
to_char(stoptime, 'HH:MI') from reservedroomtest where roomid in
('105, 106, 110')
To answer my own question, yes it does appear to be that way.
If I put the clause inline in the SQL (as 'in($rlist)' ) rather than as a
parameter (as 'in(?)' ), it works as expected (or at least it does when I
fix my most common datetime conversion error: MM == Months, MI==
Minutes, yah idjit Johnson!)
Is there a way to pass an unquoted list as a parameter?
No. A parameter is a parameter. You want to pass a list of parameters.
If you put it inline, you do the same:
select reserved_id, reserver, purpose, to_char(starttime, 'HH:MI'),
to_char(stoptime, 'HH:MI')
from reservedroomtest
where roomid in (105, 106, 110)
^^^
|||
||+-- 3rd parameter
|+--- 2nd parameter
+ 1st parameter
I can't do it as
'in(?,?,?,?)' etc, because the number of list elements varies from
execution to execution.
Sure you can:
$qry = select * from reservedroomtest where roomid in (
. join(,, map ?, @list)
. );
$sth = $dbh-prepare_cached($qry);
$sth-execute(@list);
hp
--
_ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern.
__/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
Re: Perl 5.6.1 supports DBI module
On 2010-03-18 16:30:11 -0500, Michael Nhan wrote: DBI-1.605 was the last version not to require perl 5.8. Even though the Readme doesn't state it, version 1.606 and newer requires 5.8.10 in the ITYM 5.8.1 hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Which SQLite DBD To Use
On 2010-03-16 17:14:32 -0500, Will Rutherdale (rutherw) wrote: I am about to ask someone to install DBI and DBD-SQLite on a platform. I noticed there are a number of related repos on CPAN: DBD-SQLite, DBD-SQLite2, DBD-SQLite-Amalgamation. Is DBD-SQLite the right one to get? Yes. Also, the DBD-SQLite package has a funny description: Self-contained RDBMS in a DBI Driver. This doesn't mean that DBD-SQLite re-implements all of SQLite, does it? No. It doesn't reimplement it, it just bundles it. Or does it just mean that it is the Driver for the Self-contained RDBMS of SQLite? No. The drivers includes an SQLite version and will ignore any other version which may already be installed on the system. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Which SQLite DBD To Use
On 2010-03-17 10:27:45 -0500, Will Rutherdale (rutherw) wrote: -Original Message- From: Peter J. Holzer [mailto:h...@wsr.ac.at] On 2010-03-16 17:14:32 -0500, Will Rutherdale (rutherw) wrote: Also, the DBD-SQLite package has a funny description: Self-contained RDBMS in a DBI Driver. This doesn't mean that DBD-SQLite re-implements all of SQLite, does it? No. It doesn't reimplement it, it just bundles it. Or does it just mean that it is the Driver for the Self-contained RDBMS of SQLite? No. The drivers includes an SQLite version and will ignore any other version which may already be installed on the system. Interesting. In embedded systems there is often a lot of fighting over flash space, and if people are using SQLite then it has already been installed somewhere. Is there any way to get the driver to use the existing SQLite and to avoid the cost of installing the extra one that comes with DBD-SQLite? Almost certainly yes. In fact on Debian, libdbd-sqlite3-perl doesn't contain another copy of libsqlite3.so but reuses the lib from libsqlite3-0. If it isn't obvious how to do that from DBD::SQLite you may want to have a look at the Debian package. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: How to extract db hostname from the $dbh handle?
On 2010-03-12 18:25:42 -, Greg Sabino Mullane wrote:
If I have a $dbh handle, is it possible to know which
hostname is that $dbh connected to?
Not in generic DBI, but some drivers (e.g. Postgres via DBD::Pg)
provide a custom attribute:
my $host = $dbh-{pg_host};
If your driver doesn't have that, bug the author, or perhaps
even petition to have it added to DBI.
This may not be possible in the general case. For some databases (e.g.,
Oracle, probably ODBC) you pass in an opaque identifier which is
resolved by the library, not the DBD.
hp
--
_ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern.
__/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
Re: Verify Compatibility
On 2010-03-09 07:12:16 -0500, Martin Gainty wrote: [question about compatibility between versions of MySQL, perl, DBI and DBD::mysql] you need to contact HP-US support for these questions http://welcome.hp.com/country/us/en/contact_us.html They will be able to assign a case number for you and determine if you have pirated chinese software or legitimate software from HP-US Huh? Since when are Perl, DBI, and DBD::mysql property of Hewlett Packard? And how do you pirate Perl? hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: (Fwd) DBD::Oracle - Issues Compiling DBI
On 2010-02-01 16:43:27 -0600, Prindle, Douglas E wrote: Hello, Being first time through this process when I did the make it was looking for the gcc compiler. My company has made that End Of Life due to it being a sunsetted product. So I switched to the Forte 11 compiler from Sun which we have had no issues with compiling other existing c code. Martin already commented that. However, when I try to make the DBI after pointing it to the new Forte compiler I am getting a series of errors that seem to point to the DBI.c and DBI.xs files as having issues. [...] Content-Description: Forte_Compiler_Errors.TXT sun4-cross: make /xenv/Forte/sun4/11.0/5.8p8/prod/bin/CC -c /xenv/OS/sun4/5.8p8/sun4u/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -xarch=v8 -D_TS_ERRNO -xO3 -xspace -xildoff-DVERSION=\1.53\ -DXS_VERSION=\1.53\ -KPIC -I/usr/perl5/5.8.4/lib/sun4-solaris-64int/CORE -DDBI_NO_THREADS DBI.c CC is a strange name for a C compiler. C compilers are normally called cc (lower case) or c89 or something similar. Is this maybe a C++ compiler? If so, please note that C++ is not a simple superset of C, it is a different language. DBI.xs, line 149: Warning: String literal converted to char* in initialization. One minor difference between C and C++ is the type of string literals (char[] vs. const char[]). This warning supports the suspicion that you are trying to compile C code with a C++ compiler. Don't do that. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Help needed.
[quoting rearranged into sensible order]
On 2010-01-15 17:15:04 +0800, Agarwal, Gopal K wrote:
Aplogies for the ambiguous question below. I'll explain it further.
I am connecting the Oracle DB with perl DBI. For short queries
(execution time 2 sec) I am able to fetch the data but for long
queries (7 sec) my perl scripts hangs.
I am writing the perl program which will analyze the Data Base
performance across different vendors. The amount of data is in orders of
Giga Bytes.
I want to select the data from table and want to see the number of
rows,data volume and time in fetching the data.
Here is the code snippet for this:
my $sth = $dbh-prepare($query)
or die Couldn't prepare statement: . $dbh-errstr;
$sth-execute();
$ref = $sth-fetchall_arrayref;
print \nNumber of rows:, 0 + @{$ref};
It is taking too much time to retrieve the data which is in order of 25
million records.
There is no timeout in DBI, and, unless you have done something peculiar
to your database, there isn't any in Oracle, either. I often have
queries which are running for several minutes and sometimes even for
several hours.
If you are retrieving 25 million records with fetchall_arrayref, the
problem is much more likely to be memory instead of time. Even if the
rows are very short we are talking about approximately 3 GB of RAM here,
if each row has lots of fields it can be a lot more. Unless you need all
that data in memory at the same time, use fetchrow_arrayref in a loop
instead.
hp
--
_ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern.
__/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
Re: Looking for clever solution: Different versions of Oracle .so files within single Perl build
On 2009-12-14 07:18:41 -0500, jeff wrote: I need to connect simultaneously to both Oracle 8 and oracle 10 from the same script using 'external' connection (ie, no username or password). The Oracle libraries, from my experimentations, are not compatible for external connections. Oracle 8 uses system ID and Oracle 10 uses wallet and they do not overlap. [...] Anyway, since Oracle DBD can only be built against 1 at a time , this presents a problem. I have been trying to use DBI Proxy in a PAR standalone for Oracle 8 connections and the usual setup for Oracle 10 connections with some limited success. What would be ideal would be to have 2 different Oracle DBD connections from within the same perl build. Any suggestions or ideas for potential solutions out there ? Split the script into two processes: One of them talks the Oracle 8 DB and the other one talks to the Oracle 10 DB. Let them communicate over a pipe or socket. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Info needed about version compatibility
On 2009-11-27 14:16:02 +0100, Robert Roggenbuck wrote: Have a look at http://matrix.cpantesters.org/?dist=DBI+1.609 for DBI and http://matrix.cpantesters.org/?dist=DBD-Oracle+1.23 for DBD::Oracle. It doen't look good for DBD::Oracle but it seems to me that the passed tests are not reported ... it can't be such a bad code ;-) To test DBD::Oracle you need an Oracle client and an Oracle database with a usable test user (and unless that user is scott, you need to set an environment variable). It's possible that none of the smoke testers has a system which is set up to build and test DBD::Oracle, so that all of the tests are in state unknown. -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: MySQL client ran out of memory
On 2009-11-16 15:17:15 +, Kamna Ramakrishnan wrote: Every time I attempt running my script it comes up with the above mentioned error message. The script works perfectly fine on a pilot dataset. My actual dataset is about 5000k. If I just run the sql command from the mysql client with the --quick parameter it does work. Is there a way of using the quick parameter in DBI? I think you may be looking for the database handle attribute mysql_use_result. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Does Pg's psql have an equivalent to Oracle's spool cmd?
On 2009-10-20 15:54:30 +0800, Tim Bowden wrote: On Tue, 2009-10-20 at 18:07 +1100, Ron Savage wrote: Hi Folks [for those who don't know the sqlplus (or dbish) spool command: It's similar to the Unix tee(1) command: It copies all output which would normally go onto the screen into a file. If it does, I can't see it in the Ref Man # 1. Any ideas? copy. Note there is both an sql copy command and a psql copy command. I don't think so: Input/Output \copy ... perform SQL COPY with data stream to the client host And the postgresql copy command does something very different. \o [FILE] send all query results to file or |pipe is more like spool, but there are two important differences: 1) it writes the results *only* to the file, not the screen. 2) it affects only the results, not the commands. You should be able to get around the first limitation by invoking \o |tee file but I don't see any way around the second. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: DBD::ODBC and character sets
On 2009-09-30 08:45:47 +0200, Stephan Austermühle wrote: By the way, UTF-16 != UCS-2LE? Yes. UCS-2 is restricted to Unicode codepoints = 0x. UTF-16 is an extension of UCS-2 which encodes the codepoints bertween 0x1_ and 0x10_ (IIRC) as a series of two 16-bit values. Both can be little endian (LE) or big endian (BE). hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Install issues. Os/x 10.5 and DBD-Oracle-1.23
On 2009-09-25 12:28:08 -0400, Danilson, John wrote: I am trying to install DBD::Oracle on my mac and I¹m having issues with the make test. But I think the issues begin earlier. Mac os/x 10.5.8 (intel) DBD-Oracle-1.23 [...] make returns the following warnings. I think this is the root of my issues but don¹t know how to resolve: ld warning: in /Users/oracle/product/10.2.0/db_1/lib//libclntsh.dylib, file is not of required architecture ld warning: in /Users/oracle/product/10.2.0/db_1/lib//libclntsh.dylib, file is not of required architecture Assuming you haven't installed the PowerPC version of Oracle, this looks like you have either installed the 64bit Intel version and your perl is a 32 bit executable or vice versa. While a 64 bit Intel system can run both 32-bit and 64-bit executables, you cannot build a single executable which contains 32-bit and 64-bit code. So you should probably find out what kind of executable your perl is (run perl -V or file `which perl` to find out) and then find a matching Oracle client. Alternatively, you can build perl yourself to match your oracle libraries. There is also the possibility that your Oracle installation already contains both types of libraries and Makefile.PL failed to detect the correct version. If there is a lib64 or lib32 subdirectory somewhere that's probably the case. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Retrieving warnings after executing a MySQL INSERT statement
On 2009-07-28 09:19:05 -0700, David Goodman wrote:
I suggest that you check to see if the string $DBI::errstr is empty
rather than just whether the 'do' function executed correctly.
It seems that the SQL is correctly submitted from the DBI side but the
database server actually produces an error message.
The server does not produce an error message. It only produces a
warning. You can see the same behaviour in the mysql command line
client:
mysql insert into foo(name, age) values('foo2', 'bar2');
Query OK, 1 row affected, 1 warning (0.00 sec)
The query is ok, there is no error, but 1 warning.
mysql show warnings;
+-+--+---+
| Level | Code | Message
|
+-+--+---+
| Warning | 1366 | Incorrect integer value: 'bar2' for column 'age' at row
1 |
+-+--+---+
1 row in set (0.00 sec)
This is MySQL specific, so you have to check the documentation of
DBD::mysql. And sure enough, the first occurrence of warning is:
mysql_warning_count
The number of warnings generated during execution of the SQL
statement.
However, the DBI does know about warnings and information, too:
A driver may return 0 from err() to indicate a warning condition after
a method call. Similarly, a driver may return an empty string to
indicate a ’success with information’ condition. In both these cases
the value is false but not undef. The errstr() and state() methods may
be used to retrieve extra information in these cases.
hp
--
_ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern.
__/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
Re: Very slow mail-transport to the list
On 2009-07-01 21:44:00 +0200, rrogg...@uni-osnabrueck.de wrote: Regarding the snippets: I presented all lines conatining time information. I am not so familiar with the mail-protocol to know taht other lines are relevant too. Sorry for that and That was my fault, sorry. I should have compared the headers from your mail with other mails from the list. The mailing list software in use at perl.org seems to remove all Received: headers from incoming mails. So the headers you posted looked rather incomplete, but not because you snipped too much but because the information wasn't there in the first place. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: Insert records in table with perl DBD::Mysql
On 2009-06-29 02:58:18 -0700, Jannis Kafkoulas wrote:
In the DBD::Mysql docu it says:
# INSERT some data into 'foo'. We are using $dbh-quote() for
# quoting the name.
$dbh-do(INSERT INTO foo VALUES (1, . $dbh-quote(Tim) . ));
# Same thing, but using placeholders
$dbh-do(INSERT INTO foo VALUES (?, ?), undef, 2, Jochen);
Note that there are *three* arguments after the sql query, not two.
When I now use the statement:
$dbh-do(insert into $objtbl values (?,?,?,?,?,?),
$name,$type,$ip,$mask,$comment,$mark);
in my Perl script I get the error message:
DBI::db=HASH(0x82a6388)-do(...): attribute parameter
'g-ef_epn-iers-ica-citrix-clients' is not a hash ref
at dbd_ldtbl.pl line 51, OBJ line 2.
where g-ef_epn-iers-ica-citrix-clients ist the value of the $name variable.
Why the hell is here a hash ref expected?
Because it says so in perldoc DBI:
$rows = $dbh-do($statement, \%attr, @bind_values) or die ...
The second argument is a hashref containing attributes, and the
bind values come after that. So you want
$dbh-do(insert into $objtbl values (?,?,?,?,?,?), {},
$name,$type,$ip,$mask,$comment,$mark);
or
$dbh-do(insert into $objtbl values (?,?,?,?,?,?), undef,
$name,$type,$ip,$mask,$comment,$mark);
(but I prefer the former).
I'm afraid I didn't quite understand how it realy works:-(.
Reading perldoc DBI helps :-).
hp
--
_ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern.
__/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
DBD::Oracle 1.23 and instant client 10.2.0.4
Somewhat related to Jeff's mail. I recently built DBD::Oracle 1.23 against Oracle instant client on a 32 bit linux box and got the following warning: Reading /usr/share/oracle/10.2.0.4/client/demo.mk WARNING: Oracle /usr/share/oracle/10.2.0.4/client/demo.mk doesn't define a 'build' rule. WARNING: I will now try to guess how to build and link DBD::Oracle for you. This kind of guess work is very error prone and Oracle-version sensitive. It is possible that it won't be supported in future versions of DBD::Oracle. *PLEASE* notify dbi-users about exactly _why_ you had to build it this way. Found header files in /usr/include/oracle/10.2.0.4/client. The rules defined by demo.mk are: all buildoci buildocci plus a bunch of others which don't look relevant. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
LD_LIBRARY_PATH on x86_64 linux
This isn't really a DBI problem, but it hit me with DBD::Oracle, so it
may be helpful for some of you (unless I've missed something obvious, in
which case I'll just confuse everybody):
For a long time I've used a construct like this:
if (open(E, $offl_root/dbi/env)) {
printlog(reading environment from $offl_root/dbi/env);
while (E) {
chomp;
my ($k, $v) = split(/=/);
$ENV{$k} = $v;
printlog(setting $k=$v);
}
}
my $dbh;
$dbh = DBI-connect($data_source, $username, $auth,
{ AutoCommit = 0,
PrintError = 1,
RaiseError = 1
}
);
to set up a suitable environment (ORACLE_HOME, NLS_LANG,
LD_LIBRARY_PATH, ...) before connecting to the database. I was aware
that this wasn't completely portable (doesn't work on Solaris), but it
worked on HP-UX and i386 Linux, and I sort of assumed that it would work
on other Linux platforms, too.
I was naive. It doesn't work on x86_64 Linux (at least not 2.6.18).
A change to LD_LIBRARY_PATH after program startup seems to be ignored
and consequently libclntsh.so.10.1 isn't found. If I set LD_LIBRARY_PATH
before invoking the perl script it works, so the workaround is obvious,
but it's ugly.
Have others noticed the same thing? Does anyone know why x86_64 differs
from i386 Linux in this regard?
hp
--
_ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern.
__/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd
pgp6iZ40yiWYm.pgp
Description: PGP signature
Re: unable to write from a select into outfile
On 2009-05-13 08:14:09 -0700, Bruce Ferrell wrote: Alexander Foken wrote: On 13.05.2009 02:59, Brian Manning wrote: Can you tell me why I'm getting this error: DBD::mysql::st execute failed: Can't create/write to file '/tmp/result.csv' (Errcode: 2) [...] I figured it out and you guys are gonna love this. Yes, it was permissions; No, not on the web server machine where the CGI was executing. To Recap, CGI/web server on one machine, database on a remote machine. SQL executed from MySQL command line on the CGI/Webserver drops the outfile in the expected place... happy, joyous and free. The SQL, when executed from the CGI program drops the outfile on the database machine. The permission errors were because the target directory didn't exist on the database machine. Now, the question is how do I get the CGI to drop the outfile on the machine where the CGI executes? I think you mean: How do I get the *database* to drop the outfile on the machine where the CGI executes? You don't. The database can only write files within its own file system. There are three possibilities: 1) write the file to a file system accessible over the network from both the database and the webserver. This might be a directory on one of the two machines exported to the other via NFS (or whatever network filesystem you mistrust least) or on a file server accessible by both. 2) copy the file from the database server to the web server, for example with scp or rsync. 3) forget about select into outfile. Use a normal select and process the results in the CGI script. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd pgpFUl903Qyct.pgp Description: PGP signature
Re: Incorrect required version in readme for DBI 1.5 and 1.6
On 2009-05-06 10:18:05 -0400, Tom Sorensen wrote: The readme files for versions 1.51 and up state that Perl 5.6 and up is required. The Makefile.PL says otherwise. For versions 1.606 and up it simply will not compile: chloe DBI-1.606 0 perl Makefile.PL Perl v5.8.10 required--this is only v5.6.1, stopped at Makefile.PL line 10. 5.8.10 isn't even out yet (5.8.9 is the latest release in the 5.8.x series according to http://www.cpan.org/src/README.html), so I think this must be a typo. Maybe that should read 5.8.1? hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd pgpGoLt96Tt76.pgp Description: PGP signature
Re: Problem with Oracle ODBC connection
On 2009-03-31 09:55:42 -0700, Bruce Johnson wrote: If sqlplus DOES return data: What version of Oracle ODBC and Oracle client are you using, and what version is oracle on the far end? This is similar to some aggravating results I've gotten on my (admittedly) very esoteric system: connecting from Oracle 11 via database link to an RDB database made available via an RDB OCI server. I have to use an ancient (1.17) version of DBD::Oracle or I get the very same results, a successful query with no rows returned (and I know there are rows there, if I build DBD:Oracle 1.17 against Oracle 8.1.7 libs it works...); sqlplus on my Oracle 11 system does return rows. I assume that the RDB OCI server emulates an Oracle 8i server (or something even older). Oracle 11 clients aren't compatible with Oracle 8i databases any more. The last client to work with 8i databases was 10g. The 10g client also talks to Oracle 11 servers, so that's currently the most compatible client. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd pgpFuKrD4RhUJ.pgp Description: PGP signature
Re: SQL Keyword is used as Field Name. SELECT Query Problem using DBI.
On 2009-03-02 20:04:02 -0800, Moderator wrote: ^ first off, I don't think choosing the nick name Moderator is an especially bright move. Some might think you are claiming to be the moderator of the list. One of our tables awb_msg has the field name 'text' and when I try to use the DBI to do a select it prompts an error. How can I do this. I can't change the field name as it is in a production environment and The error below doesn't look like is has anything to do with the field name. DBD::ODBC::st fetchrow_arrayref failed: [Microsoft][ODBC SQL Server Driver]Strin g data, right truncation (SQL-01004)(DBD: st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) err=-1) at ./dbi.pl line 34. It says that it must truncate a string (presumably the contents of text), but isn't allowed to (LongTruncOk not set). So the problem almost certainly not the name text but the size of the data. Read perldoc DBI for information about LongTruncOk and LongReadLen. Please guide me how I can do this. The first step is to actually read the error message. hp -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern. __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd pgp3oxfCAm8Eh.pgp Description: PGP signature
Re: DBI and DB:oracle modules
On 2009-02-04 18:32:22 -0600, David Ehresmann wrote: I want to install the DBI module on my linux server. It is not connected to a network. I am using Oracle EE 11g 11.1.0.7.0 and Red Hat Linux ES 4. My perl home is /usr/lib/perl. Which version of DBI and DBD::oracle do I use? I can go to this website: http://search.cpan.org/~timb/ and view the modules ( or bundles ). Which DBI version do I need, i.e. DBI 1.?? The version that is included with RHEL 4 should be fine. Which DBD::oracle driver do I need 1.17 thru 1.21? Just get the newest one from CPAN. $perl -v This is perl, v5.8.5 built for i386-linux-thread-multi You may want to subscribe to the rhel-i386-as-4-webapp-1-beta channel, which includes perl-5.8.8. hp -- _ | Peter J. Holzer| Am Anfang war der Bug und der |_|_) | Sysadmin WSR | Verantwortliche sprach: | | | h...@wsr.ac.at | Es werde ein Testcase. __/ | http://www.hjp.at/ |-- Clemens Zauner in dcii pgpVzCdpFS1W4.pgp Description: PGP signature
Re: How to iterate through database tables
On 2009-02-03 10:06:48 -0800, Douglas Wilson wrote:
On Tue, Feb 3, 2009 at 5:47 AM, Deviloper devilo...@slived.net wrote:
But some bad guy could showed up and force the poor developer not to use
perl-vars in SQL-Statements for security reasons.
Is ist possible to use tablenames like normal bind-variables?
Is there a better way to solve this problem?
Using perl vars is ok as long as you untaint them (you are using
taint checking in programs that use untrusted user input, right?).
I think this could be understood differently than you probably meant it.
Perl variables are not generally unsafe, and untainting them doesn't
make them magically safe.
The tainting mechanism in perl assumes that any input from the user is
unsafe and taints it. By untainting the variable you just tell perl I
have checked that this value is safe. Whether the check was sufficient
to ensure safety perl has no way to tell - it has to trust the
programmer.
In the context of this thread you probably want to give the user access
to certain tables ('jan_sales', 'feb_sales', ..., 'dec_sales') but not
to others (e.g., 'employees').
Depending on the input, you can check the input:
unless ($month =~ /^(jan|feb|...|dec)$/) {
die;
}
$month = $1;
$sth = $dbh-prepare(select * from ${month}_sales ...);
or maybe you don't need to untaint at all because the input is only used
for lookup:
my %quarters = {
q1 = ['jan', 'feb, 'mar'],
...
q4 = ['oct', 'nov, 'dec'],
};
my @months = @{ $quarters{$quarter} };
unless (@months) {
die
}
for my $month (@months) {
$sth = $dbh-prepare(select * from ${month}_sales ...);
}
Here $month is never tainted because it can get only values from your
program, not from the user. So it is safe to use.
hp
--
_ | Peter J. Holzer| Am Anfang war der Bug und der
|_|_) | Sysadmin WSR | Verantwortliche sprach:
| | | h...@wsr.ac.at | Es werde ein Testcase.
__/ | http://www.hjp.at/ |-- Clemens Zauner in dcii
pgpkqcWcegSgV.pgp
Description: PGP signature
Re: DBI and DB:oracle modules
On 2009-02-05 07:21:36 -0600, David Ehresmann wrote: The version that is included with RHEL 4 should be fine. How do I test to see if the DBI bundle was installed? This is a Redhat question, not a Perl question. % rpm -qa | grep DBI perl-DBI-1.50-1.el4s1 The normal way to install packages in RHEL (= 4) is with up2date, but I don't know if that works without an internet connection. rpm always works, of course. hp -- _ | Peter J. Holzer| Am Anfang war der Bug und der |_|_) | Sysadmin WSR | Verantwortliche sprach: | | | h...@wsr.ac.at | Es werde ein Testcase. __/ | http://www.hjp.at/ |-- Clemens Zauner in dcii pgpJfhgl1WRyF.pgp Description: PGP signature
Re: Ways of ensuring integers in the database are seen as numbers in perl?
On 2009-01-27 15:21:55 +, Martin Evans wrote: I'm working with DBD::Oracle at the moment and getting a little frustrated with integer numbers. I'd like my database returned integers to look like integers to Perl but they currently don't until I add 0 to them. Here is the problem: I think this is on purpose. Oracle numbers have a huge precision (38 decimal digits), so in general you cannot convert them to a perl number without losing precision. DBD::Oracle could be smart enough to recognize the safe cases (integers with at most 9 (32 bit) resp. 18 (64 bit) digits), but AFAIK it isn't. Your best bet is probably explicitely binding with an approriate type (SQL_INTEGER). hp -- _ | Peter J. Holzer| Am Anfang war der Bug und der |_|_) | Sysadmin WSR | Verantwortliche sprach: | | | h...@wsr.ac.at | Es werde ein Testcase. __/ | http://www.hjp.at/ |-- Clemens Zauner in dcii pgpM9Rtp1M19j.pgp Description: PGP signature
Re: DBI connect and ports issue
On 2009-01-14 22:01:39 -0500, Martin Gainty wrote: Port 1526 sounds odd as the default port for oracle is 1521 Port 1526 used to be the default port for oracle (until version 7, IIRC). Some sites still use it for historical reasons. hp -- _ | Peter J. Holzer| Am Anfang war der Bug und der |_|_) | Sysadmin WSR | Verantwortliche sprach: | | | h...@wsr.ac.at | Es werde ein Testcase. __/ | http://www.hjp.at/ |-- Clemens Zauner in dcii pgpqYXBsd2jeW.pgp Description: PGP signature
Re: Beginners Question: Can I have more the one statement-handle from same db-handle at a time?
On 2008-11-21 06:33:27 -0500, John Scoles wrote:
Deviloper wrote:
like:
my $db-connect(blablabla);
my $sth1 = $db-prepare(select * from animals);
$sth1-execute();
my $sth2 = $db-prepare(select * from pets);
$sth2-execute();
my $sth3 = $db-prepare(select * from fish);
$sth...
while (my ($animal) = $sth2-fetch_array) {
...
}
I can´t test myself because my the mysql db on my iphone
is broken and I have no other here at the moment :-(
Yes of course and it is best practice to let a single DB handle generate all
the Statement handles in you code.
I think the OP may have meant something different: Is it possible to
start fetching from one statement handle before you are finished with
the other?
In general, yes, this is possible, but it depends on the database.
MySQL in particular doesn't support multiple open cursors (at least in
5.0, IIRC). However, DBD::mysql gets around this limitation by reading
the complete resultset into memory in execute, and fetch_* just reads
from the already cached data. So, as far as the DBI programmer is
concerned, multiple open cursors are allowed (until the resultset is too
large to fit into memory - then you need to turn mysql_use_result on and
either fetch all rows or call finish before using another statement
handle).
hp
--
_ | Peter J. Holzer| Am Anfang war der Bug und der
|_|_) | Sysadmin WSR | Verantwortliche sprach:
| | | [EMAIL PROTECTED] | Es werde ein Testcase.
__/ | http://www.hjp.at/ |-- Clemens Zauner in dcii
pgpYvbP8srm2d.pgp
Description: PGP signature
Re: what's SQL do?
On 2008-08-10 08:07:53 -0700, [EMAIL PROTECTED] wrote: Quick question, I have a chunk of code from a previous coder: $member = $dbh-selectrow_array( SQL ); SELECT whatever FROM DATA SQL I'm not familiar with the syntax? See perldoc perlop, Quote-Like Operators. hp -- _ | Peter J. Holzer| Am Anfang war der Bug und der |_|_) | Sysadmin WSR | Verantwortliche sprach: | | | [EMAIL PROTECTED] | Es werde ein Testcase. __/ | http://www.hjp.at/ |-- Clemens Zauner in dcii pgp5IjGjBJSLo.pgp Description: PGP signature
Re: Perl+DBI question [C1]
On 2008-07-28 20:36:23 +0530, Srinivas KATTI wrote:
I am working on perl assignment which is first perl code in our
environment, i have come across following problem, pls if you could
provide your expert consultansy it will be great help to me
I am trying to use DBI in my program (simple program), below is the piece
of code
#!/usr/bin/perl -w
#use lib '/tools/dev/perl_modules/DBI/1.48/DBI-1.48';
BEGIN {
push @INC,/tools/dev/perl_modules/DBI/1.48/DBI-1.48;
}
use DBI;
[...]
when i execute the above program, get the following error
Can't locate loadable object for module DBI in @INC
On some OSs (notably Solaris) it isn't possible to change the search
path for shared libraries after the program has been loaded.
(@INC contains:
/usr/perl5/5.00503/sun4-solaris
And I see you are running Solaris, so that might be an issue. You are
also running an extremely old version of perl. If at all possible,
upgrade to a current version: 5.10.0 or at least 5.8.8.
[...]
the comments specified in DBI.pm says
# If you get an error here like Can't find loadable object ...
# then you haven't installed the DBI correctly. Read the README
# then install it again.
So is this correct? do i need to install it again
How did you install it? Normally, if you install a module, perl knows
how to find it - no messing around with @INC necessary. If you need to
do that, you have already done something strange (not necessarily wrong,
but strange - so you may run into strange problems).
hp
--
_ | Peter J. Holzer| Am Anfang war der Bug und der
|_|_) | Sysadmin WSR | Verantwortliche sprach:
| | | [EMAIL PROTECTED] | Es werde ein Testcase.
__/ | http://www.hjp.at/ |-- Clemens Zauner in dcii
pgpIJ75f409tz.pgp
Description: PGP signature
Re: [rt.cpan.org #36395]
On 2008-07-01 01:37:01 -0400, Ramesh Thangamani via RT wrote: Queue: DBI Ticket URL: http://rt.cpan.org/Ticket/Display.html?id=36395 On Tue Jun 24 17:17:23 2008, TIMB wrote: The DBI is acting as defined. It's not a bug. You should avoid using an array to pass arguments to execute(). I could still see the following examples mentioned in the documentation in CPAN where we are passing an array to execute method(). Please comment on this. $rv = $sth-executeor die $sth-errstr; $rv = $sth-execute(@bind_values) or die $sth-errstr; Which means that exeute takes a list of bind values. It doesn't necessarily mean that it is good style to use an actual array. But I disagree with Tim here. There are many situations where using an array is the right thing to do - especially if you are constructing sql statements with a variable number of placeholders on the fly it is IMHO the only sane way. I think it is unfortunate that execute with zero parameters means something different from execute with non-zero parameters and that there is no way to distinguish zero parameters from an array with with zero elements, but that can't be helped: Changing the former would break lots of existing code, and the latter is a fundamental property of Perl5. In practice I've never run into this problem. While I have occassionally constructed queries where the number of placeholders and the size of the array were not equal, I don't remember ever ending up with an empty array. I did forget the parameter list a few times (i.e., $sth-execute instead of $sth-execute($x, $y)) ... hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgp21zqhqE25d.pgp Description: PGP signature
Re: Generic DBI question about backups
On 2008-06-20 10:08:11 -0500, Curtis Leach wrote: Thanks for all the responses. Looks like I may have to go with a CSV type solution. That's probably the safest route, as long as you make sure that you don't lose data when exporting to CSV (check for character sets, binary data, etc.) Too bad that keeping the hardware that Oracle is on isn't an option for me. Oracle dump files (created by exp or datapump) are portable between architectures. So you should be able to export the data on your current hardware and then import it on a PC. If you do this, set up your migration PC now, don't wait until the current hardware is gone. hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpL7VeKw6SRL.pgp Description: PGP signature
Re: OCIEnvNlsCreate error with DBD::Oracle 1.21 and Oracle 10g client in Cygwin
On 2008-04-22 12:59:26 -0700, Dunston Rocks wrote: I had posted a few days ago about facing problems installing DBD::Oracle with Oracle 10g client under Cygwin. Regular client or instant client? Though those were circumvented successfully, when I try to run a simple script that calls DBI-Connect, I get DBI connect() failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME env var, NLS settings, permissions, etc. at tmp.pl line 12 Error ERROR OCIEnvNlsCreate. Check ORACLE_HOME env var, NLS settings, permissions, etc. at tmp.pl line 12. ORACLE_HOME C:\oracle\product\10.2.0\client_2 ORACLE_SID DEVDB TWO_TASK DEVDB NLS_LANG WE8ISO8859P15 This is not a valid NLS_LANG setting. It needs to be .WE8ISO8859P15 (or include a language and country, like american_america.WE8ISO8859P15). I just ran into a similar problem with instant client 10.2.0.4 on Linux (Debian Etch, instant client rpms converted to .deb with alien). Wierdly enough it happened only during make test. Running the tests individually with prove -b -v worked, and at least my simple test scripts work, too (Haven't tried anything complicated yet). hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpzdB2re9eCs.pgp Description: PGP signature
Re: Perl 5.10.0
On 2008-06-05 01:44:20 -0700, Jonathan Leffler wrote: On Thu, Jun 5, 2008 at 12:54 AM, Scott Ryan [EMAIL PROTECTED] wrote: Hi I am struggling to build the DBD-Oracle module on mandriva 2008.1. [...] make: *** No rule to make target `/usr/lib/perl5/5.10.0/i386-linux-thread-multi/CORE/EXTERN.h', needed by `Oracle.o'. Stop. Any help would be appreciated as google throws up nothing. Assuming that the file doesn't exist - rather than no permissions - then look to see whether there is any other file in the CORE directory. On my Solaris machine, the equivalent file exists. If your CORE directory is non-existent, or mostly empty (should be over 50 files in it), then your best bet is probably build your own Perl. If just the one file is missing, you could try a reinstall. It is also possible that the header files are in a different package. Most Linux distributions split a basic perl installation into multiple packages. Although CORE/*.h are in the base package in the perl package in Redhat and Debian, Mandriva might have put them into a different package - look for something like perl-devel, perl-headers, or similar. hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgp4TgF2ds08E.pgp Description: PGP signature
Re: install_driver(Oracle) failed: wrong ELF class: DynaLoader.pm
On 2008-05-29 09:26:29 +0200, Christian Merz wrote: as as workaround i wrote this shell skript: LD_LIBRARY_PATH=/export/home/oracle/product/10.2.0/lib:/export/home/oracle/product/10.2.0/lib32 export LD_LIBRARY_PATH /oracle/dba/backup/ora19/DbOnline.pl ora19 and now it works. Thank you ! ... But this is a workaround i do not like very much. Reexecuting the skript might be a way (probably i will do this). Is there another possibility? You could write LD_LIBRARY_PATH=/export/home/oracle/product/10.2.0/lib:/export/home/oracle/product/10.2.0/lib32 /oracle/dba/backup/ora19/DbOnline.pl ora19 directly into the crontab. But personally I prefer invoking small shell scripts to very long commands in crontabs. hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpb1lbLOl4EL.pgp Description: PGP signature
Re: Perl in oracle 10.2.0.3 on 64 BIT OS
On 2008-05-19 17:17:22 -0700, Jonathan Leffler wrote: On Mon, May 19, 2008 at 11:49 AM, Shanmugam, Dhandapani [EMAIL PROTECTED] wrote: Does PERL support 64-bit operating systems? Yes. Kindly help me on how should perl work on Oracle 64-bit Solaris Operating system . Am getting the below error [bash]perl create_ddl.pl Can't locate DBI.pm in @INC So, you haven't installed DBI yet. You will have to do that before you can use it. For example, you must have DBI installed before installing DBD::Oracle. Given that the install is currently using Perl 5.8.3, you should build your own Perl, either 5.8.8 or 5.10.0, and then install it in a location of your own choosing (rather than messing with the system version) The install path looks strange: /ade/stvobadm_perl_583_build/perl/bin/Solaris/Opt I don't know where Solaris puts their files (last version of Solaris I used was 2.7 or maybe 8, I think), but I doubt that this is the system version. I have seen similarly crazy paths in the perl distributed with Oracle Application Server. OAS sets up the environment in such a way that it does work (on Fridays at full moon), but it is still a pain. I suspect that this version of perl is also distributed as part of some application and not intended to be standalone. I agree that the OP should just get a current version of perl (I guess there are binary packages available for solaris, or he can compile it himself) instead of trying to beat a messed-up version into submission. (If he needs to use perl *as part of* an application he should pester their tech support) and then get on with installing DBI and its pre-requisites and then install DBD::Oracle. I can't answer for whether DBD::Oracle supports 64-bit systems, but it would be astonishing (to me) if it did not do so. It works at least on 64-bit Linux. I believe 64-bit HP-UX has been mentioned on this list, too. hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpfM5U52k43E.pgp Description: PGP signature
Re: OCIEnvNlsCreate error with DBD::Oracle 1.21 and Oracle 10g client in Cygwin
On 2008-05-05 10:46:05 -0700, pgodfrin wrote: My install of Oracle, for reasons unknown to me, had this file set to rwxr-x--- which doesn't work for 'others'. In recent versions of Oracle, by default the programs and libraries installed with a server installation are only usable by the DBA group. Oracle calls this a hardened installation and provides a script to soften it, if you need to. I assume there are security reasons for this, but I haven't been able to find an explanation (admittedly I haven't looked very hard). Or maybe they just want to nudge people into separating client and server installations ... hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpa43exBzjjt.pgp Description: PGP signature
Re: DBD::Oracle and Support for Oracle 8 and 9 clients
On 2008-04-17 21:43:27 -0400, [EMAIL PROTECTED] wrote: I just want to get a general feeling of the community on weather the next version of DBD::Oracle (1.22) should drop support for the Oracle 8 and Oracle 9 clients I was just reviewing the code today and there are a large number of little fixes here and there to make these two clients work with later versions of DBD::Oracle. We still have some 8i databases, but since they can be accessed with an Oracle 10 client[0], that's not a problem. If I have a reason to upgrade to a newer DBD::Oracle on a machine I can just install a version 10 client there, too. So I was thinking of cleaning up the code so that the next version 1.22 only supports 9.6 and later clients? Go ahead. hp [0] Not just theoretically. I do have a number of perl scripts using a version 10 client which access one of the old 8i databases. Sometimes things are a bit weird (especially with UTF-8), but it works. -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgp7mFmtsIokY.pgp Description: PGP signature
Re: Recursive SQL query?
On 2008-04-15 18:55:25 -0400, Colin Wetherbee wrote: Scott Webster Wood wrote: You shouldn't really need to go recursing through a data tree to come up with all the parts of a single fact. Well that is unless you are wanting to inherit data from linked elements further up a link list without re-posting redundant information. That's true enough in your situation, but then you trade speed against data size. I suspect traversing a data hierarchy inside a database, with SQL alone, won't be too pleasant when it comes to performance. Don't forget data consistency. In the OP's design it is clear that NULL means inherit from parent. So consider these two cases: Table categories: Id Parent-Id Property 1 NULL foo 2 1 NULL Table categories: Id Parent-Id Property 1 NULL foo 2 1 foo In the first case the child (id 2) inherits property foo from the parent. If you change it in the parent, it will change in the child, too. In the second case both have the property foo, but they are set on both explicitely. If you change it in the parent, it will remain the same in the child. Now change this to a design with an explicit property table which linked from all levels: Table categories: Id Parent-Id Property 1 NULL prop_1 2 1 prop_1 Table properties: Id text prop_1 foo Now we have no distinction between inherit from parent and explicitely set to some value which just happens to be the same as that of the parent. We can use distinctive entries in the properties table: Table categories: Id Parent-Id Property 1 NULL prop_1 2 1 prop_2 Table properties: Id text prop_1 foo prop_2 foo but that's not quite the same: prop_1 and prop2 can be referenced from anywhere in the categories tree and the user needs to be able to distinguish them somehow. So let's drop that for the moment and assume that properties.text is unique. Then, when you want to change property on the parent row, you have three choices: 1) Just change the text in the properties table. This will change it everywhere in the database, not just in the row and its children. 2) Create a new property entry with the new text, and just change the foreign key in the parent row to point to this new entry. 3) Create a new property entry with the new text, and recursively change it in the parent and all descendants. All of these are different from the orignal design. It's possible that one of them matches the intentions of OP better than the solution he had in mind, but you need to be aware of the differences. There is of course a fourth method: 4) In addition to the foeign key to the properties table, add a flag which tells whether the value was inherited or set explicitely. On update, recurse as in 3), but change only values with the flag set. This restores the semantics of the OP's design. One other point to consider: In the OP's design, every query for data may need to go up to the root of the tree to find the value. So that may be up to depth(tree) additional rows to read *for every query*. In the cases of 3) and 4) you need to recurse down through the tree for every update. This may mean up to nodes(tree) additional rows to update for every update. So you are trading a small improvement in query time against a potentially huge degradation of update time. So to determine which is better you need to consider the frequency of queries and updates, the size and shape of the tree, how many nodes have the properties explicitely set vs. inherit them, etc. And finally, this is the perl DBI mailinglist, not an SQL mailinglist. There is nothing wrong with solving the problem in Perl, you don't have to do it in SQL ;-). So, you can for example do individual queries and cache the results (in the object, if you are creating objects, or in a hash). The upper levels in the catalog will almost always be in the cache, so you rarely need to send a query for the parent. hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgp6ILrihqgzD.pgp Description: PGP signature
Re: question for DBD-Oracle driver
On 2008-04-09 13:07:55 -0500, James H. McCullars wrote:
At 10:47 AM 4/9/2008, pgodfrin wrote:
This will obviate the need to use LD_LIBRARY_PATH, which has some
controversy surrounding it's use. I also found a strangeness for the
install of DBD. If I remember correctly, the install document states
that LD_LIBRARY_PATH should be set during the module install, which
will link the value of the LD_LIBRARY_PATH . Indeed the value ends up
in the make file (in the EXTRA_LIBS, LD_RUN_PATH and OTHERLDFLAGS
entries), and the library seems to be available as per the ldd
command, yet, without either an LD_LIBRARY_PATH environment variable
or the ld.so.conf.d method, DBD does not run.
I've just been through an install of DBD::Oracle on a Solaris 10 machine
and
this was my experience as well. My needs were complicated by the fact that I
was running it as a CGI script and thus did not have a way to easily set an
environment variable.
$ENV{LD_LIBRARY_PATH} = ...;
or somewhat more generic:
if (open(E, $offl_root/dbi/env)) {
while (E) {
chomp;
my ($k, $v) = split(/=/);
$ENV{$k} = $v;
}
close(E);
}
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgpMmnG10yfux.pgp
Description: PGP signature
Re: question for DBD-Oracle driver
On 2008-04-10 08:27:06 -0500, James H. McCullars wrote:
At 02:17 AM 4/10/2008, Peter J. Holzer wrote:
I've just been through an install of DBD::Oracle on a Solaris 10
machine and this was my experience as well. My needs were
complicated by the fact that I was running it as a CGI script and
thus did not have a way to easily set an environment variable.
$ENV{LD_LIBRARY_PATH} = ...;
That doesn't work from a CGI script.
Yes, it does. I have quite a few CGI scripts which use this technique.
Why shouldn't it work with a CGI script? I can think of reasons why it
wouldn't work with mod_perl, but none for a CGI script.
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgp0etRPl9f3Z.pgp
Description: PGP signature
Re: question for DBD-Oracle driver
On 2008-04-10 10:26:50 -0500, James H. McCullars wrote: At 09:08 AM 4/10/2008, Peter J. Holzer wrote: Why shouldn't it work with a CGI script? I can think of reasons why it wouldn't work with mod_perl, but none for a CGI script. Setting the environment variable worked for me from the command line but never from a CGI script. By worked for me from the command line do mean: 1) Set the environment variable within the script as in the example I gave and invoked the script from the command line, or 2) set the environment variable in the shell and then invoke the script? These are completely different. The best explanation I could find for this was here: http://www.perlmonks.org/?node_id=126575 This talks about the second case. Yes, that's entirely possible (doesn't seem to be the case on linux, though), but it doesn't have anything to do with CGI scripts. I don't believe that that the first case is possible on any unixoid OS. hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpkNp51MjdpU.pgp Description: PGP signature
Re: mysql inserting decimal value with perl loses precision
On 2008-03-14 09:50:15 +, [EMAIL PROTECTED] wrote:
This is more for the archives than anything as there is a workaround.
When inserting 1234567890.123456 into a decimal(16,6) column, the value
stored
is 1234567890.123460
The value 1234567890.123456 is not exactly representable in a perl
numeric scalar (assuming 64 bit double, which is a reasonable
assumption). The nearest representable approximation is
1234567890.12345600128173828125.
However, rounding that to 6 digits after the comma is 1234567890.123456,
so I don't see a reason for the wrong rounding. It looks more like if
it's rounded to 5 digits instead of 6.
Having a brainwave from the Time::HiRes docs,
sprintf('%.6f', 1234567890.123456) stores the expected value.
In this case the scalar is a string instead of a number. I don't know
the internals of DBD::mysql, but I would expect that it is also passed
as a string to the server, which then stores it as is (converting to
the internal decimal type of course, but that's just some
bit-twiddling).
Is this expected behaviour or should DBD::mysql automagically do the right
thing?
I would expect rounding errors when passing numeric scalars to decimal
types in general, but not in this case - that should be handled
correctly (because it can).
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgpBX1xr6w7ZF.pgp
Description: PGP signature
Re: mysql inserting decimal value with perl loses precision
On 2008-03-14 12:44:29 +, [EMAIL PROTECTED] wrote: Peter J. Holzer - [EMAIL PROTECTED] wrote: On 2008-03-14 09:50:15 +, [EMAIL PROTECTED] wrote: This is more for the archives than anything as there is a workaround. When inserting 1234567890.123456 into a decimal(16,6) column, the value stored is 1234567890.123460 The value 1234567890.123456 is not exactly representable in a perl numeric scalar (assuming 64 bit double, which is a reasonable perl -V gives doublesize=8 assumption). The nearest representable approximation is 1234567890.12345600128173828125. However, rounding that to 6 digits after the comma is 1234567890.123456, so I don't see a reason for the wrong rounding. It looks more like if it's rounded to 5 digits instead of 6. Time::HiRes docs say What is going on is that the default floating point format of Perl only outputs 15 digits. In this case that means ten digits before the decimal separator and five after Ah, yes. I should have checked that as I've run into this issue before (although not in a DBI context). My fault. That's a bug in perl itself, not in DBD::mysql. Under some conditions the number to string conversion stops one or even two digits too soon. I looked at that code a few months ago and I know where it happens and why it happens but I didn't get around to prepare a patch and send it to p5p. (floating point arithmetic is tricky - I'd feel more comfortable reusing a well-known implementation of a known well-behaved algorithm than implementing one of my own). Is this expected behaviour or should DBD::mysql automagically do the right thing? I would expect rounding errors when passing numeric scalars to decimal types in general, but not in this case - that should be handled correctly (because it can). I wasn't sure it could be or not. Unless the driver knows the precision of course. Since perl itself gets the conversion wrong, that would be a necessary condition. If DBD::mysql knows that the type is decimal(X,Y), it can invoke an sprintf(%.Ys,...) itself. But I don't know how easily DBD::mysql can get that info - if it means a round-trip to the database you might want to avoid that. It's probably a good idea to put something in the docs, a la Time::HiRes. ACK. hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpuyNiyxSMDg.pgp Description: PGP signature
Re: make error- ld: Unrecognized argument: -Wl,+b...
On 2008-02-19 10:22:21 -0800, Jonathan Leffler wrote: On Feb 19, 2008 10:04 AM, Capacio, Paula J [EMAIL PROTECTED] wrote: Hello, I am getting the following error from make (excerpt only full list follows) [...] ld: Unrecognized argument: -Wl,+b/usr/oracle/client/10.2/lib:/usr/oracle/client/10.2/rdbms/lib Fatal error. *** Error exit code 1 Nasty - especially since you're not a C programmer. Systems that build Perl using 'ld' directly as the way to build shared objects give me the heebie-jeebies each time - and your Perl appears to have been built that way. The '-Wl,+b...' notation is an instruction to the C compiler to pass the '+b...' to the loader; the loader itself doesn't know how to deal with -Wl options. We can debate whether this is a bug in DBD::Oracle or not - or a bug in the way Perl was built on your machine. Since Perl mainly works OK and DBD::Oracle doesn't, I'd be more inclined to say DBD::Oracle is at fault, but DBD::Informix (which I know a lot better than DBD::Oracle) faces roughly similar issues and my recommendation is to build Perl so it uses the C compiler to build shared objects rather than using 'ld' (or /usr/bin/ld) directly. I second that recommendation. Paula seems to use an Activestate perl: nfig_heavy=Config_dynamic.pl -Dcf_by=ActiveState [EMAIL PROTECTED] com' At least on PA-RISC, perl needs to be built in a special way (described on README.hpux) to be able to use DBD::Oracle. The perl interpreter shipped with HP-UX isn't built that way and while I don't know about the ActiveState perl interpreter, I suspect that it isn't either. So I would recommend to always build your own perl on HP-UX (preferrably with the HP C/ANSI-C compiler). hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpwcA0WXacHO.pgp Description: PGP signature
Re: Find current database name from db handle
On 2008-02-04 15:23:26 +0100, Kostas Chatzikokolakis wrote: I'm using a dbi handle that is shared between many packages in my code. Some package might do a USE db_name to change the current database of the connection. Can I retrieve the current database name from the handle, either from DBI or from the DBD::mysql driver (without querying the server)? What's wrong with 'perldoc DBI'? What's the name attribute of a database handle documented as doing? Does it not work for you? Hello Jonathan, thanks for your reply. From perldoc: Name [...] Usually (and recommended to be) the same as the dbi:DriverName:... string used to connect to the database, but with the leading dbi:DriverName: removed. So Name returns the dsn used to connect, it is not meant to be the current database (it's not updated when changing database by doing USE db-name). I want something that is dynamic, similar to doing a SELECT DATABASE() query in mysql, but without doing an actual query. I don't think this is possible in general. It's certainly not possible in the DBI, as the SQL commands to change the database (or schema, or whatever your RDBMS calls it[1]) is different between databases (in Oracle it's alter session set current_schema = $scheme_name). It may be possible in the driver which could check for these commands and remember the current database. But even that's not guaranteed to work: What happens if this command is hidden inside a stored procedure? And why do want to avoid the query? How often do you plan to make this call? hp [1] And these aren't really the same, anyway. -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpap6Z0pOepq.pgp Description: PGP signature
Re: DBI-DBD::ORACLE EROR
On 2008-01-29 19:14:51 -0800, Jonathan Leffler wrote: On Jan 29, 2008 5:31 AM, Mohammed, Shafi [EMAIL PROTECTED] wrote: I need following files. As has already been explained to you -- you need to get those files from a legitimate source, namely Oracle, or your authorized Oracle supplier. Nothing else will work; no-one who respects the law will help you other than by telling you to get them from Oracle. And from a technical point of view: Sending him these few files won't help him. He does need the complete package, and the complete package is available for free from the Oracle web-site, so that's simply the easiest way for him to get it. hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpeM4wATHiTc.pgp Description: PGP signature
Re: All tests fail for any other than user 'oracle'
On 2008-01-15 16:29:39 -0600, Michael Muratet wrote: I installed the Oracle Instant Client packages by rpm back in December. Because of the install or more likely coincidentally I had problems with corruption of the system files of the database. The database was empty at the time and I simply rebuilt it. I am trying now to get DBD::Oracle working. The DBI install came off without a hitch. The DBD perl build and the make worked OK (logs are at the end). The first attempt at testing failed. I recalled something I had read about file permissions and the user 'oracle'. I logged in as oracle and all the tests were successful. sqlplus works but only for user oracle. Are you sure you are using the instant client and not an oracle server installation on the same machine? I am asking for two reasons: 1) For some time now an Oracle database *server* installation includes all the client libraries and tools (sqlplus, etc.), but the permissions are set so that only the oracle user (or maybe the dba group) can use them. This sounds exactly like your problem. (I am not sure why Oracle did this - maybe they want to encourage people to separate server and client installations even if they are on the same machine - anyway, there is a script to soften the permissions). 2) You said you corrupted the system files of the database and rebuilt it. Since it is extremely unlikely that installing a software on one host will damage software on a different host, I conclude that your database and the Instant Client packages are installed on the same machine - so you need to be careful to set the environment to point to the right installation. hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpJqLxyXLyrJ.pgp Description: PGP signature
Re: Using q() to define a query
On 2008-01-12 11:54:03 +0100, Dr.Ruud wrote:
Carville, Stephen schreef:
and c.certcrtdate = to_date('%s','MM-DD-')
Aaargh, you should always use '-MM-DD'.
While I prefer -MM-DD, too (and where is MM-DD- used, anyway?
Isn't that usually written as MM/DD/ or MM.DD.?), it doesn't
make a difference here: The string is converted to a date type, and
then two date values are compared. That one of them was computed from a
string is irrelevant.
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgphbxA2pRKIv.pgp
Description: PGP signature
Re: Using q() to define a query
On 2008-01-12 11:51:42 +0100, Dr.Ruud wrote:
Colin Wetherbee schreef:
my $sql = q(SELECT departure_date, eq.name AS equipment,
dp.full_city AS departure_city, ap.full_city AS arrival_city,
ca.name AS carrier_name, number
FROM jsjourneys
FULL OUTER JOIN jscarriers AS ca ON jsjourneys.carrier = ca.id
FULL OUTER JOIN jsequipment AS eq ON jsjourneys.equipment = eq.id
JOIN jsports AS dp ON jsjourneys.departure_port = dp.id
JOIN jsports AS ap ON jsjourneys.arrival_port = ap.id
ORDER BY departure_date);
(As an aside, how do you guys quote your queries? I find that for
anything longer than about 60 characters, q() and '' and everything
else start to look horribly inelegant.)
my $sql = 'SQL';
SELECT
jo.departure_date AS departure
, eq.nameAS equipment
, dp.full_city AS departure_city
, ap.full_city AS arrival_city
, ca.nameAS carrier_name
, jo.number
FROM
jsjourneys AS jo
[...]
SQL
I almost never use here documents because they cannot be properly
indented:
sub foo {
some;
code;
here;
if (bla) {
more;
code;
here;
my $sql = 'SQL';
SELECT
jo.departure_date AS departure
, eq.nameAS equipment
, dp.full_city AS departure_city
, ap.full_city AS arrival_city
, ca.nameAS carrier_name
, jo.number
[...]
SQL
even;
more;
}
code;
here;
}
just looks terrible. Of course with SQL leading whitespace doesn't
matter so you can just indent the whole statement and just have the
dangling terminator at the left edge[1] but that doesn't work for
multiline strings in general. Putting here documents in a function of
their own as proposed by Stephen helps, but the indentation is still
inconsistent.
Since initial whitespace doesn't matter in SQL, I'd just write that as:
sub foo {
some;
code;
here;
if (bla) {
more;
code;
here;
my $sql = q{
SELECT
jo.departure_date AS departure
, eq.nameAS equipment
, dp.full_city AS departure_city
, ap.full_city AS arrival_city
, ca.nameAS carrier_name
, jo.number
[...]
};
even;
more;
}
code;
here;
}
(actually, I'd put the commas at the end of the lines)
As an aside, the SPL programming language[2] allows the terminator of a
here document to be indented and to strip off everything up to and
including some character from each line, so that could be written like
this:
function foo() {
some;
code;
here;
if (bla) {
more;
code;
here;
var sql = SQL|
|SELECT
|jo.departure_date AS departure
|, eq.nameAS equipment
|, dp.full_city AS departure_city
|, ap.full_city AS arrival_city
|, ca.nameAS carrier_name
|, jo.number
|[...]
SQL;
even;
more;
}
code;
here;
}
BTW, some editors recognize certain heredoc-delimiters (like SQL) and
switch language for color coding, autocompletion, etc.
That's a neat feature of course and an argument for using
here-documents.
hp
[1] No, I don't think »my $sql = 'SQL';« is a good idea.
[2] http://www.clifford.at/spl
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgpnuYyeCkdLs.pgp
Description: PGP signature
Re: Using q() to define a query
On 2008-01-14 13:07:17 +0100, Jenda Krynicky wrote:
From: Peter J. Holzer [EMAIL PROTECTED]
As an aside, the SPL programming language[2] allows the terminator of a
here document to be indented and to strip off everything up to and
including some character from each line, so that could be written like
this:
function foo() {
some;
code;
here;
if (bla) {
more;
code;
here;
var sql = SQL|
|SELECT
|jo.departure_date AS departure
|, eq.nameAS equipment
|, dp.full_city AS departure_city
|, ap.full_city AS arrival_city
|, ca.nameAS carrier_name
|, jo.number
|[...]
SQL;
even;
more;
}
code;
here;
}
That kinda defeats the purpose of heredocs, namely allowing me to
copypaste some data into/out of the script without having to escape
or modify anything.
In most cases this works with q{} just the same (the only exception is
the sequence \\, which unfortunately represents only a single backslash
inside q{}.
If you do this and then need to test and tweak the SQL in some UI
provided by your database you have to go and strip and later
reintroduce the |s.
Yes, that works for SQL, as I wrote, but not for multi-line strings in
general, where leading whitespace may matter (think of formatted text
output).
Before Clifford introduced that feature into SPL, we discussed several
formats. I think one of them included a column 0 marker in the first
line - something like that:
var sql = ^SQL|
SELECT
jo.departure_date AS departure
, eq.nameAS equipment
, dp.full_city AS departure_city
, ap.full_city AS arrival_city
, ca.nameAS carrier_name
, jo.number
[...]
SQL;
where everything to the left of the marker (^ here) would be stripped
off. That would simplify cut'n'paste a bit. I don't remember why he
didn't do it that way, but one obvious problem with this approach is
that to the left of is ambiguous if tabs and spaces are mixed.
Not for me, I'd rather have a few lines that are not indented.
As I wrote I prefer just using q{}. As you can see in my example, it has
all lines indented and you can simply cut and paste.
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgpo6sTwTG4Iw.pgp
Description: PGP signature
Re: Segmentation Fault(Core dumped)
On 2008-01-07 15:02:41 -, Kasi, Vijay (London) wrote: Robert, Kasi, Vijay (London) schrieb: I am receiving 'Segmentation Fault (core dumped)' error while executing perl script on unix host. I am using oracle 10.2.0 with perl 5.8.6 . Can you pls advise what could be the reason. The code was running fine when I have used Oracle 9.2.0 version, we have changed oracle client path to 10.2.0 (32 bit libraries) this morning and it started failing since. Well, you have just answered your own question. You cannot just change the client path and expect it to work, you will have to rebuild DBD::Oracle against the new client. hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgp5hOykyTYS1.pgp Description: PGP signature
heap corruption in DBD::Oracle
I have a perl script which updates some (large) tables from text-files,
inserting, updating and deleting records. For performance reasons the
inserts, updates and deletes are queued together and then done with an
array operation when there are enough. Now I've added another table
and the script dies with:
*** glibc detected *** double free or corruption (fasttop): 0x01450ad0
***
in ora_execute_array (called from line 854 in Oracle.pm)
The relevant part of the script looks like this:
sub insert {
my ($self, $inserts) = @_;
my $dal = $self-{_dal};
my $sth_insert_data = $dal-{_dbh}-prepare_cached(insert into
data(id, real) values(?, ?));
my $sth_insert_datacoord =
$dal-{_dbh}-prepare_cached(
insert into datacoords(period_start, period_end,
data_id, set_id)
values(?,?, ?,
? )
);
my $sth_insert_fact_comext =
$dal-{_dbh}-prepare_cached(
insert into fact_comext(period_start, reporter,
partner, product, data_id, real)
values(?, ?,?,
?, ?? )
);
my @values = map { $_-{real} } @$inserts;
my $data_ids = $self-seq_numbers(scalar(@$inserts));
$sth_insert_data-execute_array({}, $data_ids, [EMAIL PROTECTED]); # --
WORKS
unless ($time_period_id) {
my @tp = $dal-datasets({ path = [ 'time_period']});
die time_period not found if @tp 1;
die time_period not unique if @tp 1;
$time_period_id = $tp[0]-id;
}
my @ps = map { $_-{period}[0] } @$inserts;
my @pe = map { $_-{period}[1] } @$inserts;
$sth_insert_datacoord-execute_array({}, [EMAIL PROTECTED], [EMAIL
PROTECTED], $data_ids, $time_period_id); # -- WORKS
my @prod_ds = map { $_-{ds}-id } @$inserts;
$sth_insert_datacoord-execute_array({}, undef, undef, $data_ids,
[EMAIL PROTECTED]); # -- WORKS
my @rep_ds = map { $_-{reporter}-id } @$inserts;
$sth_insert_datacoord-execute_array({}, undef, undef, $data_ids,
[EMAIL PROTECTED]); # -- WORKS
my @part_ds = map { $_-{partner}-id } @$inserts;
$sth_insert_datacoord-execute_array({}, undef, undef, $data_ids,
[EMAIL PROTECTED]); # -- WORKS
{
my @ps1 = @ps;
my @rep_ds1 = @rep_ds;
my @part_ds1 = @part_ds;
my @prod_ds1 = @prod_ds;
my @values1 = @values;
$sth_insert_fact_comext-execute_array({}, [EMAIL PROTECTED], [EMAIL
PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], $data_ids, [EMAIL
PROTECTED]); # --- CRASH
}
$dal-{_dbh}-commit();
$inserted += @$inserts;
}
There are six calls to execute_array in the routine. The first five
(marked WORKS) work fine, the sixth crashes. The copying of the arrays
before the sixth execute_array was a feeble (and unsuccessful) attempt
at a workaround.
Has anybody run into this problem before or do I need to dig deeper?
Version information:
DBD::Oracle 1.19
DBI 1.58
perl v5.8.8 built for x86_64-linux-thread-multi
Red Hat Enterprise Linux AS release 4 (Nahant Update 5)
Oracle 10.2.0.1.0
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgp6ieyKOn6pr.pgp
Description: PGP signature
Re: heap corruption in DBD::Oracle
On 2007-12-16 23:07:36 +0100, Peter J. Holzer wrote:
I have a perl script which updates some (large) tables from text-files,
inserting, updating and deleting records. For performance reasons the
inserts, updates and deletes are queued together and then done with an
array operation when there are enough. Now I've added another table
and the script dies with:
*** glibc detected *** double free or corruption (fasttop):
0x01450ad0 ***
in ora_execute_array (called from line 854 in Oracle.pm)
The relevant part of the script looks like this:
sub insert {
my ($self, $inserts) = @_;
my $dal = $self-{_dal};
my $sth_insert_data = $dal-{_dbh}-prepare_cached(insert into
data(id, real) values(?, ?));
my $sth_insert_datacoord =
$dal-{_dbh}-prepare_cached(
insert into datacoords(period_start, period_end,
data_id, set_id)
values(?,?, ?,
? )
);
my $sth_insert_fact_comext =
$dal-{_dbh}-prepare_cached(
insert into fact_comext(period_start, reporter,
partner, product, data_id, real)
values(?, ?,?,
?, ?? )
^^
Oops. I just noticed the missing comma here. I don't know if this was
the cause (I changed the execute_array to a plain execute in a loop,
when I noticed it and the script is still running), but that's certainly
a possibility.
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgpZVFRiNlkc3.pgp
Description: PGP signature
Re: Fun with UTF-8 and Oracle [PATCH]
On 2007-09-04 14:20:20 +0100, Tim Bunce wrote:
On Mon, Sep 03, 2007 at 07:20:42PM +0200, Peter J. Holzer wrote:
On 2007-09-03 13:03:37 +0100, Tim Bunce wrote:
Oh the joys of Oracle character set support.
See the dbd_rebind_ph() code in dbdimp.c. Especially from the line
csform = phs-csform; onwards.
You can explicitly set the csform and csid using bind_param(..., { ... })
Have fun!
I had :-). You gave me a push into the right direction, and I think I
found the problem:
[code deleted]
While it claimed to set csform=SQLCS_IMPLICIT, it was actually
setting it to SQLCS_NCHAR, and I guess that was what prevented the
use of the index on the varchar2 column. Sure enough, if I
explicitely set ora_csform to 1, it's fast. It is also fast, if I
set NLS_NCHAR to US7ASCII, so that it isn't used.
Maybe the tests in line 1379 and 1381 should be reversed so that
SQLCS_IMPLICIT is preferred over SQLCS_NCHAR?
Sounds very plausible.
[...]
That's great, but why stop there? Now you seem to have identified the
problem, how about patching the code instead ;-)
Sorry for the long delay. I wanted to test the patch properly first and
then forgot to post it. Now I stumbled upon the same problem on a
different server which reminded me that I never posted the patch. So
here it is.
(I couldn't think of a proper test case: Building a table with 4 billion
rows and then checking whether the query takes a few seconds or an hour
doesn't seem a good idea :-). Anyway, I've used it in production for
almost 3 months.)
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
--- dbdimp.c.orig 2006-11-03 15:05:46.0 +0100
+++ dbdimp.c2007-09-24 10:36:25.0 +0200
@@ -1376,15 +1376,15 @@
if (!csform SvUTF8(phs-sv)) {
/* try to default csform to avoid translation through non-unicode */
- if (CSFORM_IMPLIES_UTF8(SQLCS_NCHAR)) /* prefer NCHAR */
- csform = SQLCS_NCHAR;
- else if (CSFORM_IMPLIES_UTF8(SQLCS_IMPLICIT))
+ if (CSFORM_IMPLIES_UTF8(SQLCS_IMPLICIT))/* prefer
IMPLICIT */
csform = SQLCS_IMPLICIT;
+ else if (CSFORM_IMPLIES_UTF8(SQLCS_NCHAR))
+ csform = SQLCS_NCHAR;
/* else leave csform == 0 */
if (trace_level)
PerlIO_printf(DBILOGFP,rebinding %s with UTF8 value %s,
phs-name,
- (csform == SQLCS_NCHAR)? so setting csform=SQLCS_IMPLICIT
:
- (csform == SQLCS_IMPLICIT) ? so setting csform=SQLCS_NCHAR :
+ (csform == SQLCS_IMPLICIT) ? so setting csform=SQLCS_IMPLICIT
:
+ (csform == SQLCS_NCHAR)? so setting csform=SQLCS_NCHAR :
but neither CHAR nor NCHAR are unicode\n);
}
pgpb2cPcnVfAY.pgp
Description: PGP signature
Re: DBD::Sybase 1.08 fails to run tests on HP-UX 11.23 (64bit) with Sybase 12.5.3 (64bit)
On 2007-11-26 05:21:09 -0800, Andon Tschauschev wrote: Hello everybody, I tried to install DBD::Sybase 1.08 on HP-UX 11.23 (64bit) and it failed in the test phase. Versions of HP-UX and Sybase: $uname -a HP-UX servername B.11.23 U ia64 0563309065 unlimited-user license select @@version Adaptive Server Enterprise/12.5.3/EBF 13337 ESD#7/P/ia64/HP-UX B.11.23/ase1253/1951/64-bit/OPT/Fri Mar 24 00:47:27 2006 Running perl Makefile.PL says u.a: BLK api available - found: blk_r64 blk64 but at end of the run of perl Makefile.PL: Note (probably harmless): No library found for -lblk . Compiling with make runs successfully, although producing following warnings: (Bundled) cc: warning 922: -Ae is unsupported in the bundled compiler, ignored. ^ I don't think you'll have much luck with the bundled C compiler. At least on PA-RISC (don't know about IA64) that's a very primitive Pre-ANSI-C-compiler, usable only for compiling /stand/build/conf.c (and similar specialized tasks). Either buy the HP ANSI-C compiler or get gcc. hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgp24zDHsATm8.pgp Description: PGP signature
Re: Segmentation fault with module DBD::Oracle 1.19 on SuSE Linux Enterprise Server 10 (IBM Power 64 bit)
On 2007-11-09 15:12:03 +0100, Roberto Zini wrote: Hello everybody. I'm trying to get the DBD::Oracle 1.19 Perl module working fine under SuSE Linux Enterprise Server 10 (IBM Power 64bit). I don't know about the Power architecture specifically, but on most platforms you cannot link 64 and 32 bit code together. So if your perl is a 64-bit executable, your Oracle libraries need to be 64 bit, too; and if your perl executable is 32 bits, you need the 32-bit Oracle libraries. file `which perl` should tell you for sure, but we can guess from the output of perl -V: The version of SLES is 10.0 (no SP1 applied yet) and the version of Perl I'm using is: === cut here == 8 == Summary of my perl5 (revision 5 version 8 subversion 8) configuration: Platform: osname=linux, osvers=2.6.16, archname=ppc-linux-thread-multi-64int 64int sounds like 64 bit, but ... intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=87654321 longsize=4 and ptrsize=4 are strong hints that it's really a 32-bit executable. When trying to compile with the 32bit module, I have a Segmentation Fault when using a Perl test script (during the DBI-connect phase) where when using the 64bit version the linker skips over the Oracle provided libclntsh.so.10.1 (it's incompatible but to me it seems fine). A 64-bit library is indeed incompatible with a 32-bit executable, so that's to be expected. I cannot explain the segfault, though. I did notice that you didn't include any output from make test. Did you run it? If so, did it also crash with a segfault? hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpfqnxzjmRLf.pgp Description: PGP signature
Re: Parsing Results from fetchall_hashref()
On 2007-10-26 19:39:38 -, Rob Wilkerson wrote:
I'm trying to debug a problem in a Perl script that retrieves records
from an Oracle database and writes those records to a flat file. I'm
retrieving the records using fetchall_hashref() and have found that at
least one record isn't being written correctly so I'm trying to figure
out why.
I can access the value of each member just fine using $mediaref-
{$media_id}-{'ADID'} and I can access the size of the entire record
set as keys ( %$mediaref ). What I need to know, though, is how many
elements exist in $mediaref-{$media_id}, but I can't figure out
whether that's possible or how to do it. I've tried all kinds of
variations of keys ( %$mediaref-{$mediaid} ) with no luck at all.
scalar keys %{ $mediaref-{$mediaid} }
But I'm quite sure that will return the same number for all rows in the
query. Maybe you are more interested in the number of fields which are
not null:
scalar grep {
defined $mediaref-{$mediaid}{$_}
} keys %{ $mediaref-{$mediaid} }
Is it possible?
Yes.
Am I correctly guessing at how the system stores fields (i.e. as a nested
hash)?
Yes. No need to guess, though. perldoc DBI states that quite clearly:
The fetchall_hashref method can be used to fetch all the data to be
returned from a prepared and executed statement handle. It returns a
reference to a hash containing a key for each distinct value of the
$key_field column that was fetched. For each key the corresponding
value is a reference to a hash containing all the selected columns and
their values, as returned by fetchrow_hashref().
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgpIX6LaU94eo.pgp
Description: PGP signature
Re: Parsing Results from fetchall_hashref()
On 2007-10-28 16:29:21 +0100, Peter J. Holzer wrote:
On 2007-10-26 19:39:38 -, Rob Wilkerson wrote:
I can access the value of each member just fine using $mediaref-
{$media_id}-{'ADID'} and I can access the size of the entire record
set as keys ( %$mediaref ). What I need to know, though, is how many
elements exist in $mediaref-{$media_id}, but I can't figure out
whether that's possible or how to do it. I've tried all kinds of
variations of keys ( %$mediaref-{$mediaid} ) with no luck at all.
scalar keys %{ $mediaref-{$mediaid} }
But I'm quite sure that will return the same number for all rows in the
query. Maybe you are more interested in the number of fields which are
not null:
scalar grep {
defined $mediaref-{$mediaid}{$_}
} keys %{ $mediaref-{$mediaid} }
Or, a bit simpler:
scalar grep { defined $_ } values %{ $mediaref-{$mediaid} }
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgppVLiRPbO8R.pgp
Description: PGP signature
Re: using $dbh-quote with UTF
On 2007-10-17 17:04:29 -0700, [EMAIL PROTECTED] wrote: I'm trying to use $dbh-quote with a UTF string, and I'm not having luck. How can I get it to return a UTF8 string? [script printing some HTML snipped] Please explain what you are trying to do. Using the output of quote in an HTML fragment doesn't seem very useful to me. I can only assume that you are doing it to demonstrate something but I don't understand what you are trying to demonstrate. hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpy65uukXhzQ.pgp Description: PGP signature
Re: Very slow executes with utf8 integer parameters in DBD::Oracle
On 2007-09-14 18:03:15 +0100, Martin Evans wrote:
I have NLS_LANG=AMERICAN_AMERICA.AL32UTF8 as recommended in DBD::Oracle when
using utf8 and I need to as my data is utf8 in Perl.
Grossly simplified my code does:
o select integer_primary_key_field from table
o prepare(select from another_table where field = ?)
o execute($inter_primary_key_value_retrieved_from_select)
This query is vastly more complex than this really
Even though the field retrieved from the first table is an integer when I
look
at it, Perl has utf8 flag set. When these utf8 encoded integers are then
passed
into the execute for a select on another table the execute takes 0.7s. Now
that
may not sound a lot to you but this query gets runs a lot. If I downgrade the
integer parameter with utf8::downgrade before passing it to execute the
execute
takes 0.01s.
When I look at the QEP the filter contains a TO_NUMBER(:p1) and I think
Oracle
has decided it cannot use an index on the column.
I tried binding the parameter as ora_number but that does not help. The only
thing which seems to work is to downgrade the parameter from utf8.
Any ideas?
This may be the same problem I ran into a few weeks ago. See
http://www.mail-archive.com/dbi-users@perl.org/msg30138.html
I have a patch for this but I still haven't gotten around to testing it,
so even though it's only a few lines I don't want to post it yet. Feel
free to contact me off-list if you want to try it.
The workaround which I actually use in production code is to set
NLS_NCHAR=US7ASCII. Of course this isn't a good idea if you have
nvarchar2 or nclob columns. Explicitely binding with the correct cs_form
also works:
$sth-bind_param(1, $name, { ora_csform = SQLCS_IMPLICIT });
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgpKmME1flZrp.pgp
Description: PGP signature
Re: dynamic lib ignored even after found in install_driver(Oracle) failed: Can't load... cgi problem
On 2007-09-03 19:42:10 +0200, Ralph Wecks wrote: It looks like permission issue - but I think it is not. For validation purposes I installed 2 SUN Enterprises (called C- and D-System), both with - Solaris 10 - Oracle 10.2.0 (Admin Client) - DBI 1.58 - DBD Oracle 1.19 - our application [C works, D doesn't. Probably irrelevant differences in installed software snipped] Trying to logon to our form management's database the message Cant't load Oracle.so ... libclntsh.so.10.1 not found ... appears. But all permissions seemed to be sufficient. So I enabled webserver daemon's profile and logged in as webservd. Walking through oracle's directories I made a cat libclntsh.so.10.1 at .../lib32. Yery interesting content but no permission problem. At this point, it seems that the error message would be correct: - After running a chmod -R 777 on the /oracle directory the logon procedure works fine - Revoking permission with a chmod -R 755 /oracle didn't had any effect, logon goes on - After chmod -R 644 on .../lib32 logon works fine furthermore - Logon failed again with libclntsh.so.10.1 not found after running a chmod -R 644 on /oracle Some functions, procedures or what ever in the oracle dbm tree obviously needs executable rights. You haven't written where your libraries are installed, but from /oracle and .../lib32 I conclude that the path to libclntsh.so.10.1 is something like /oracle/.../lib32/libclntsh.so.10.1 (probably /oracle/app/oracle/product/client/lib32/libclntsh.so.10.1, but it doesn't matter much what's between oracle and lib32). To access files in a directory, a process needs execute permissions on that directory. So by changing the permissions on /oracle to 644, you were preventing all root processes from accessing everything below that directory. If that works on your C system, then either your application is running as root or it is really using a different oracle client installation. Basically the same is true for .../lib32. If that directory has permissions 644, no non-root process can access the files in it, which means that libclntsh.so.10.1 cannot be loaded. However, if your application was already running at the time you did the chmod, it was already loaded and continued to work as long as it could access other files below /oracle which it needed to open at runtime (e.g., tnsnames.ora). hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpmlyu1HsOwS.pgp Description: PGP signature
Re: Fun with UTF-8 and Oracle
On 2007-09-03 13:03:37 +0100, Tim Bunce wrote:
Oh the joys of Oracle character set support.
See the dbd_rebind_ph() code in dbdimp.c. Especially from the line
csform = phs-csform; onwards.
You can explicitly set the csform and csid using bind_param(..., { ... })
Have fun!
I had :-). You gave me a push into the right direction, and I think I
found the problem:
1375 csform = phs-csform;
1376
1377 if (!csform SvUTF8(phs-sv)) {
1378 /* try to default csform to avoid translation through
non-unicode */
1379 if (CSFORM_IMPLIES_UTF8(SQLCS_NCHAR)) /* prefer NCHAR
*/
1380 csform = SQLCS_NCHAR;
1381 else if (CSFORM_IMPLIES_UTF8(SQLCS_IMPLICIT))
1382 csform = SQLCS_IMPLICIT;
1383 /* else leave csform == 0 */
1384 if (trace_level)
1385 PerlIO_printf(DBILOGFP,rebinding %s with UTF8
value %s, phs-name,
1386 (csform == SQLCS_NCHAR)? so setting
csform=SQLCS_IMPLICIT :
1387 (csform == SQLCS_IMPLICIT) ? so setting
csform=SQLCS_NCHAR :
1388 but neither CHAR nor NCHAR are unicode\n);
1389 }
The first thing to notice is that the trace message is backwards: It
prints setting csform=SQLCS_IMPLICIT if the csform is set to SQLCS_NCHAR
and vice versa. So that explains why I didn't notice anything strange in
the trace output. While it claimed to set csform=SQLCS_IMPLICIT, it was
actually setting it to SQLCS_NCHAR, and I guess that was what prevented
the use of the index on the varchar2 column. Sure enough, if I
explicitely set ora_csform to 1, it's fast. It is also fast, if I set
NLS_NCHAR to US7ASCII, so that it isn't used.
Maybe the tests in line 1379 and 1381 should be reversed so that
SQLCS_IMPLICIT is preferred over SQLCS_NCHAR? I guess there was some
good reason to prefer SQLCS_NCHAR, but it seems to me that SQLCS_IMPLICIT is
usually what you would want (but then I'm probably biased from my
absolute failure to use nvarchar or nclob columns with Oracle 8.0 from
Java or Perl many moons ago).
But do please write up what you find as a patch the the Oracle docs.
It's entirely possible that the code is doing the wrong thing.
Like this?
Index: Oracle.pm
===
--- Oracle.pm (revision 9909)
+++ Oracle.pm (working copy)
@@ -1462,7 +1462,14 @@
Specify the OCI_ATTR_CHARSET_FORM for the bind value. Valid values
are SQLCS_IMPLICIT (1) and SQLCS_NCHAR (2). Both those constants can
-be imported from the DBD::Oracle module. Rarely needed.
+be imported from the DBD::Oracle module.
+This should be rarely needed, but at least some versions of Oracle
+(e.g., 10.2) don't use an index if this is set wrong, which can happen
+if both the character set and the national character set are UTF-8 and
+the placeholder value has the utf8 flag set. In this case you can either
+choose ora_csform to match your column or (if you don't have any NCHAR,
+NVARCHAR2 or NCLOB columns) you can set NLS_NCHAR to US7ASCII to prevent
+its use.
=item ora_csid
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgpNSensae0Jy.pgp
Description: PGP signature
Re: Problems connecting to Oracle DB on SLES
On 2007-08-31 09:37:51 +0200, Thorsten Harms wrote: I have a quite annoying problem concerning the NLS_LANG parameter. If set to german_germany.we8iso8859p1 as necessary, connecting to database fails. If NLS_LANG is not set, all runs fine, but german umlauts are, of course, not correctly displayed. Does setting it to american_america.WE8ISO8859P1 or .WE8ISO8859P1 work? hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgp4i5tAjSKBX.pgp Description: PGP signature
Re: DBI-Bind parameters
On 2007-08-23 10:17:58 +0800, Ow Mun Heng wrote:
Current method.. pseudo code.
while (1)
{
$from_datetime = time;
$to_datetime = time + $time_interval;
$query = select count(*) from table where time = ? and time ?;
$sth = $dbh-prepare($query)
$sth-execute($from_datetime, $to_datetime)
$from_datetime = $to_datetime;
}
Please don't post pseudo code. It is hard to say what you are doing
wrong if you don't tell us what you are actually doing. Post a
complete, minimal script which demonstrates the problem.
Results:
[...]
2nd subsequent queries
- DESTROY(DBI::st=HASH(8720c4c))= undef at mssql_2_postgres_cvs.pl line
238
- DESTROY(DBI::db=HASH(8755fd0))= undef at mssql_2_postgres_cvs.pl line
238
!! ERROR: -1 'called with 2 bind variables when 4 are needed' (err#0)
- execute('2007-08-22 18:01:00' '2007-08-22 18:02:00')= undef at
mssql_2_postgres_cvs.pl line 238
[...]
so... how come it says 4 bind variables are needed?
I suspect that you aren't really preparing the same statement in the
second run through the loop. Maybe you conatenated two queries, or
something like that. But since we don't know your actual code, we can
just guess. Insert a 'print query=$query\n before the prepare to
check.
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgpHDmw8RQuzI.pgp
Description: PGP signature
Fun with UTF-8 and Oracle
While investigating a performance problem I found a curious behaviour.
I have an oracle database (10.2.0.3) and matching client, DBI 1.50 and
DBD::Oracle 1.19. Database and client are set to use the AL32UTF8
charset.
Now I do a simple query on one of the tables:
select id, name from datasets ds where name = ?
The table has about 300k rows and an index on the name column.
The name I'm searching for contains only ASCII characters, but depending
on the source, the string may have the utf8 flag set.
So my testcase currently looks like this:
1 #!/usr/bin/perl
2 use warnings;
3 use strict;
4 use utf8;
5 use DBI;
6 use Time::HiRes qw(time);
7 my $dbh = db_connect();
8 datasets_by_name($dbh, 'CN-03021200-import-1000 kg-CMX-rs4');
9 datasets_by_name($dbh, 'CN-03021200-import-1000 ECU-CMX-rs4');
10 my $s ='CN-03021200-import-1000 ECU-CMX-rs5';
11 utf8::upgrade $s;
12 datasets_by_name($dbh, $s);
13 datasets_by_name($dbh, 'CN-03021200-import-1000 ECU-CMX-rs6');
14 datasets_by_name($dbh, 'CN-03021200-import-1000 kg-CMX-rs6');
15 sub read_cred {
16 my ($fn) = @_;
17 open(FN, $fn) or die cannot open $fn: $!;
18 my $line = FN;
19 close(FN);
20 my @cred = split(/[\s\n]+/, $line);
21 return @cred;
22 }
23 sub db_connect {
24 my $dbi_credential_file = $ENV{WZRP_CONN};
25 my ($data_source, $username, $auth) =
read_cred($dbi_credential_file);
26 $dbh = DBI-connect($data_source, $username, $auth,
27 { AutoCommit = 0,
28 PrintError = 0,
29 RaiseError = 1
30 }
31 );
32 $dbh-{FetchHashKeyName} = 'NAME_lc';
33 return $dbh;
34 }
35 my $sth;
36 sub datasets_by_name {
37 my ($dbh, $name) = @_;
38 my $t0 = time;
39 unless ($ENV{PREPARE_ONCE} $sth) {
40 $sth = $dbh-prepare(select id, name from datasets ds where
name = ?);
41 }
42 my $r = $dbh-selectall_hashref($sth, 'id', {}, $name);
43 my $t1 = time;
44 print $t1-$t0, \n;
45 }
The function datasets_by_name invokes the query above. If it prepares
the query on each request, the timings look like this:
0.00154304504394531
0.00111699104309082
0.27887487411499
0.00116086006164551
0.00110697746276855
I.e., searching for a byte string takes about 1 millisecond and
searching for a utf8 string takes about 280 milliseconds! Oracle
enterprise manager tells me that in the slow case the index is ignored
and a full table scan is performed.
But wait, it gets even better. If the statement handle for the query is
cached and reused, the timings look like this:
0.00197005271911621
0.000653982162475586
0.280965089797974
0.2483971118927
0.250221967697144
I.e., the information that the index can't be used (which I don't
understand in the first place) seems to be sticky.
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgpZ7srUVyTaL.pgp
Description: PGP signature
Re: DBI doc curiosity
On 2007-08-20 16:21:47 +1000, Ron Savage wrote:
Under the write-up for fetchall_hashref is some sample code:
$dbh-{FetchHashKeyName} = 'NAME_lc';
$sth = $dbh-prepare(SELECT FOO, BAR, ID, NAME, BAZ FROM TABLE);
$sth-execute;
$hash_ref = $sth-fetchall_hashref('id');
print Name for id 42 is $hash_ref-{42}-{name}\n;
However, after a call to $dbh - primary_key_info(...), it seems the only
acceptable values for the parameter in the call to fetchall_hashref() are
TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and KEY_SEQ. Is that right?
The example assumes that there is a table called TABLE with at least
the fields FOO, BAR, ID, NAME, and BAZ. If you don't have such a table
(and at least Oracle thinks TABLE is an invalid table name), it won't
work of course. You are supposed to replace the table name and field
names with the name and fields of a table which actually exists.
I don't understand how primary_key_info() gets into the picture. Do you
mean column_info?
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgpSFuVrD5XfU.pgp
Description: PGP signature
