SV: Survey: Do you use positioned updates from DBI ?
I'm hoping to get some feedback on how often updatable
cursors are being used in DBI apps ?
We use it from timne to time. I have implemented them in DBD::Ingres (see
ing_readonly in the DBD::Ingres docs).
My understanding is that standard embedded SQL uses a syntax like
DECLARE CURSOR cursor-name AS select-stmt FOR UPDATE;
OPEN cursor-name;
FETCH cursor_name INTO ...;
UPDATE table SET ... WHERE CURRENT OF cursor-name;
(or DELETE FROM table WHERE CURRENT OF cursor-name)
With DBD::Ingres the syntax is:
$sth = $dbh-prepare(SELECT a,b,c FROM t FOR UPDATE OFb);
$sth-execute;
$row = $sth-fetchrow_arrayref;
$dbh-do(UPDATE t SET b='1' WHERE CURRENT OF $sth-{CursorName});
where I assume that a select without a 'FOR UPDATE OF column' clause is
read-only.
Henrik Tougaard
DBD::Ingres maintainer.
DBD::Oracle
Hi: Send me the PPM package for DBD::Oracle.Activestate 5.8 Best Regards, Satish Waghmare
Re: Patch for DBD::SQLite - spurious undef warning
The DBI provides a neatsvpv(sv, maxlen) function specifically for
use in trace messages etc (it's the same as DBI::neat).
It handles undefs and much else besides. I'd recommend it.
Tim.
p.s. I'd also recommend turning of wrapping in your mail tool,
or setting the wrap marging higher.
On Mon, Jan 17, 2005 at 09:50:17AM +1300, Sam Vilain wrote:
These debug messages cause Use of uninitialised variable... warnings
when you pass in undef parameters to $sth-execute();
Also logged as https://rt.cpan.org/Ticket/Display.html?id=9121
--- dbdimp.c~ 2004-10-05 09:02:21.0 +1300
+++ dbdimp.c2004-12-24 14:45:13.0 +1300
@@ -328,7 +328,7 @@
int sql_type = SvIV(sql_type_sv);
sqlite_trace(4, params left in 0x%p: %d, imp_sth-params,
1+av_len(im
p_sth-params));
-sqlite_trace(4, bind %d type %d as %s, i, sql_type,
SvPV_nolen(value)
);
+sqlite_trace(4, bind %d type %d as %s, i, sql_type,
(SvPOK(value) ? S
vPV_nolen(value) : NULL));
if (!SvOK(value)) {
sqlite_trace(5, binding null);
@@ -431,7 +431,7 @@
}
pos = 2 * (SvIV(param) - 1);
sqlite_trace(3, bind into 0x%p: %d = %s (%d) pos %d\n,
- imp_sth-params, SvIV(param), SvPV_nolen(value), sql_type, pos);
+ imp_sth-params, SvIV(param), (SvPOK(value) ? SvPV_nolen(value) :
NULL)
, sql_type, pos);
av_store(imp_sth-params, pos, SvREFCNT_inc(value));
av_store(imp_sth-params, pos+1, newSViv(sql_type));
--
Sam Vilain, sam /\T vilain |T net, PGP key ID: 0x05B52F13
(include my PGP key ID in personal replies to avoid spam filtering)
Re: DBD::Oracle
On 01/17/2005 05:02 AM, Waghmare, Satish (IE03x) said: Send me the PPM package for DBD::Oracle.Activestate 5.8 http://ftp.esoftmatic.com/DBI/ -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
DBD::Pg - send arrays to a stored procedure
Hello,
After I've upgraded to newest versions of DBI(1.46) and DBD::Pg (1.32), my
scripts started to fail while feeding arrays into stored procedures in
postgresql base (v7.4.6). The (old working) code was something like
$dbh- selectrow_array(SELECT dummy(?), {}, [ 1, 2, 3]);
where 'dummy' is a function accepting array of integers and returning a single
integer:
CREATE OR REPLACE FUNCTION dummy(INTEGER[])
RETURNS integer AS '
DECLARE
BEGIN RETURN 0; END;
' LANGUAGE 'plpgsql' CALLED ON NULL INPUT;
Now, the scripts fail with the following error:
DBD::Pg::st execute_array failed: ERROR: array value must start with { or
dimension information
Then I've modified the script so it looks like this:
my $sth = $dbh-prepare( SELECT dummy(?));
$sth- bind_param_array( 1, [1,2,3]);
$sth- execute_array({});
But the error message is still the same. Finally, I've grep'd the postgresql
source code and found the 'array value must start...' line in the array parsing
code there, and so my best guess is that there must be somethign broken in the
newer version of DBD::Pg, probably somewhere near the place the arrays are
encoded, but I'm not really sure. OTOH, the 'new' DBD::Pg is not that new,
dated 2004/02/25, so it looks that the problem is elsewhere...
Does anybody have a clue what's actually happening?
Thanks in advance,
Dmitry Karasik
DBD::Sybase $dbh-clone errors...
Hello all,
Now, I'm not sure this is an error, since it does succeed, but the attached
script will produce a warning about an unknown attribute. What I'm doing is
connecting to a Sybase server, cloning the connection, running a query, and
then running another query based off of the first one.
There are better ways to do the example included, but this is strictly for
demonstration of the warning.
The warning I get when running the script is:
Can't set DBI::db=HASH(0x19f8860)-{User}: unrecognised attribute or invalid
value at C:/Perl/site/lib/DBI.pm line 645.
I'm not sure if the message is from DBI or from the DBD, but it does show
up...
Environment:
Windows XP Professional SP2
Sybase 11.9.2 client (With 11.9.2 server on Win2K AS sp4)
ActivePerl 5.8.3
DBI 1.43
DBD::Sybase 1.04.6
Thanks for any recommendations...
amonotod
--
`\|||/ amonotod@| sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo
_|_|_|_|_|_|_|_|
syb_clone_conn.pl
Description: Binary data
Re: SV: Survey: Do you use positioned updates from DBI ?
On Mon, 17 Jan 2005 07:54:42 +0100, Henrik Tougaard [EMAIL PROTECTED] wrote:
I'm hoping to get some feedback on how often updatable
cursors are being used in DBI apps ?
We use it from timne to time. I have implemented them in DBD::Ingres (see
ing_readonly in the DBD::Ingres docs).
My understanding is that standard embedded SQL uses a syntax like
DECLARE CURSOR cursor-name AS select-stmt FOR UPDATE;
OPEN cursor-name;
FETCH cursor_name INTO ...;
UPDATE table SET ... WHERE CURRENT OF cursor-name;
(or DELETE FROM table WHERE CURRENT OF cursor-name)
With DBD::Ingres the syntax is:
$sth = $dbh-prepare(SELECT a,b,c FROM t FOR UPDATE OFb);
$sth-execute;
$row = $sth-fetchrow_arrayref;
$dbh-do(UPDATE t SET b='1' WHERE CURRENT OF $sth-{CursorName});
where I assume that a select without a 'FOR UPDATE OF column' clause is
read-only.
That code should also work in DBD::Informix -- which also (obviously)
supports them.
IDS (Informix Dynamic Server) marginally complicates things because in
a MODE ANSI database, all cursors (on a single table, without ORDER
BY, ...) can be used for update - as required by the ISO (ANSI) SQL
standard. So, the FOR UPDATE clause is not always mandatory in
Informix - but operationally, the UPDATE statement would simply fail
if the cursor is not updatable, and would work if it is. In a
non-ANSI database, the FOR UPDATE clause is mandatory.
You may go back to sleep now - the first paragraph is all you really
needed to know.
--
Jonathan Leffler [EMAIL PROTECTED] #include disclaimer.h
Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org
I don't suffer from insanity - I enjoy every minute of it.
DBD Oracle DBI driver
All, Does anyone know of a repository where I can find DBD::Oracle? I have tried the ActiveState default repository but it reports it is not available. Regards, Javier Moreno
Re: DBD Oracle DBI driver
From: Moreno, Javier [EMAIL PROTECTED] Date: 2005/01/17 Mon AM 11:04:56 CST All, Does anyone know of a repository where I can find DBD::Oracle? I have tried the ActiveState default repository but it reports it is not available. You found the list, did you not find the archives? This was asked and answered just today... http://www.mail-archive.com/dbi-users@perl.org/msg23620.html Regards, Javier Moreno HTH, amonotod -- `\|||/ amonotod@| sun|perl|windows (@@) charter.net | sysadmin|dba ooO_(_)_Ooo _|_|_|_|_|_|_|_|
How to call Oracle DBs via DBI ODBC
All,
I have a web application which calls an Oracle database. I was unable to
connect to it but I found out it was because the HTTP port the TNS was
listening to was not the same one the webserver was running on. I have fixed
that on the webserver end. However, it seems the call to the TNS is getting
mixed up. The DBI ODBC driver appears to be sending the HOST_NAME as
SERVICE_NAME. My call is very standard, is there something else I should do in
order for this to work?
$::dbh = DBI-connect('dbi:ODBC:ILOG', 'system', '##', { AutoCommit =
0 }) or
Error(Unable to connect to database.br$DBI::errstr);
17-JAN-2005 11:18:19 *
(CONNECT_DATA=(SID=*)(SERVICE_NAME=slpmxwmorenojav)(CID=(PROGRAM=C:\Perl\bin\perl.exe)(HOST=SLPMXWMORENOJAV)(USER=SYSTEM)))
* (ADDRESS=(PROTOCOL=tcp)(HOST=151.110.117.153)(PORT=3772)) * establish *
slpmxwmorenojav * 12514
TNS-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor
Regards,
Javier Moreno
RE: How to call Oracle DBs via DBI ODBC
Javier,
Did you not like my response from Jan 16? Again:
This is not a Perl/ODBC issue. This is a problem with Oracle. From the oerr
utility:
12514, 0, TNS:listener could not resolve SERVICE_NAME given in connect
descriptor
// *Cause: The SERVICE_NAME in the CONNECT_DATA was not found in
// the listener's tables.
// *Action: Check to make sure that the SERVICE_NAME specified is correct.
// *Comment: This error will be returned if the (database) service has not been
// registered with the listener; a database instance that is part of this
// service may need to be started or configured properly.
Most likely your database is missing the 'local_listener' parameter.
You need to speak with your DBA and have him/her fix it. If you do not have a
DBA, go to
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch188.htm#REFRN10082
and read about it.
-
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Moreno, Javier [mailto:[EMAIL PROTECTED]
Sent: Monday, January 17, 2005 11:32 AM
To: dbi-users@perl.org
Subject: How to call Oracle DBs via DBI ODBC
All,
I have a web application which calls an Oracle database. I was unable to
connect to it but I found out it was because the HTTP port the TNS was
listening to was not the same one the webserver was running on. I have fixed
that on the webserver end. However, it seems the call to the TNS is getting
mixed up. The DBI ODBC driver appears to be sending the HOST_NAME as
SERVICE_NAME. My call is very standard, is there something else I should do in
order for this to work?
$::dbh = DBI-connect('dbi:ODBC:ILOG', 'system', '##', { AutoCommit =
0 }) or
Error(Unable to connect to database.br$DBI::errstr);
17-JAN-2005 11:18:19 *
(CONNECT_DATA=(SID=*)(SERVICE_NAME=slpmxwmorenojav)(CID=(PROGRAM=C:\Perl\bin\perl.exe)(HOST=SLPMXWMORENOJAV)(USER=SYSTEM)))
* (ADDRESS=(PROTOCOL=tcp)(HOST=151.110.117.153)(PORT=3772)) * establish *
slpmxwmorenojav * 12514
TNS-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor
Regards,
Javier Moreno
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
Re: DBD Oracle DBI driver
On 01/17/2005 12:04 PM, Moreno, Javier said: Does anyone know of a repository where I can find DBD::Oracle? I have tried the ActiveState default repository but it reports it is not available. It would be worthwhile for you to read the responses sent to you before you ask the same question again. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age. ---BeginMessage--- From: Moreno, Javier [EMAIL PROTECTED] Date: 2005/01/17 Mon AM 11:04:56 CST All, Does anyone know of a repository where I can find DBD::Oracle? I have tried the ActiveState default repository but it reports it is not available. You found the list, did you not find the archives? This was asked and answered just today... http://www.mail-archive.com/dbi-users@perl.org/msg23620.html Regards, Javier Moreno HTH, amonotod -- `\|||/ amonotod@| sun|perl|windows (@@) charter.net | sysadmin|dba ooO_(_)_Ooo _|_|_|_|_|_|_|_| ---End Message---
ANN: Gtk2::Ex::DBI-0.6
I'm pleased to annouce the 6th release of Gtk2::Ex::DBI ... http://entropy.homelinux.org/Gtk2-Ex-DBI/ Gtk2::Ex::DBI is an open-source helper object that makes your Gtk2-Perl apps data aware. It handles querying, 'painting' records on your Glade-generated form, passing updates back to the database server, inserting, deleting, and much more. This release is a MAJOR update that dramatically improves performance while lowering the system requirements, by selecting small 'slices' of records instead of the entire resultset at once. Currently MySQL is the only database server that gets full functionality, but I've made some progress with SQL Server by switching to DBD::ODBC - full SQL Server ( and Sybase too, I assume ) support should arrive in the next version. Adding support for Oracle and Postgres should be trivial, but I don't use either, so it's up to others ( hint, hint ) For anyone who has bookmarked my webpage previously, note that the address has moved ( see last item in changelog ). From the changelog: Version 0.6, released 18-Jan-2005 MAJOR CHANGE: Implemented selecting of partial recordsets ( slices ) instead of simply selecting the whole recordset at once. Dramatically improves performance on low-memory machines, and in cases of large recordsets INCOMPATIBLE CHANGE: The min / max values that are passed to the record spinner are now the recordset position PLUS ONE ( ie starts at 1 now instead of 0 ) Update the range of the record spinner when inserting / deleting Added workaround for http://bugzilla.gnome.org/show_bug.cgi?id=156017 Added support for default values. TODO: Support for fetching default values from DB Server Added support for Gtk2::CheckButton Broke out last_insert_id functionality into it's own method Fixed logic problem in 'move' method if there are no records ( don't try to move to position -1 ) Added ( dodgy ) workaround for http://bugzilla.gnome.org/show_bug.cgi?id=52372 - we bail out :) Documentation additions / cleanups Got website back online at http://entropy.homelinux.org after a large outage ( I was in Cambodia for a month, and someone stole my hostname after it expired )
Re: DBD::Pg - send arrays to a stored procedure
On Mon, 17 Jan 2005, Dmitry Karasik wrote:
Hello,
Hello,
After I've upgraded to newest versions of DBI(1.46) and DBD::Pg (1.32), my
scripts started to fail while feeding arrays into stored procedures in
postgresql base (v7.4.6). The (old working) code was something like
$dbh- selectrow_array(SELECT dummy(?), {}, [ 1, 2, 3]);
Um I don't know why that would work with an earlier version of DBD::Pg unless
you were using a version that was hacked to searalize perl arrays. I remember
that there was a patch floating around to do this, but it was never part of an
official release.
Then I've modified the script so it looks like this:
my $sth = $dbh-prepare( SELECT dummy(?));
$sth- bind_param_array( 1, [1,2,3]);
You caould hack it by passing in a searlized copy of the array and let
bind_param do the final quote pass.
Also, If you want, you can check out the current cvs version on the
array_support branch which has support for array quoting and dequoting;
although, you will have to use bind_param to get the above behaviour, so
$sth-bind_param(:array_column, [EMAIL PROTECTED],
{pg_type=INT4ARRAYOID});
$sth-execute();
I need to make a final pass at the array code (probably on either tue, or wed or
friday) before it in production ready, but I think that you will be fine with
the integer support as it is now.
Rudy
