RE: Perl DBI truncates Trailing Spaces from Placeholder Bind Variables.
I made the change you described below, and this has solved the problem. I now get all 700 rows or so. Thanks! Now if we could only get Stefan's UTF89 patch for DBD-1.12 Trevor van Dyk -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 9:09 PM To: T. van Dyk Cc: 'Tim Bunce'; [EMAIL PROTECTED] Subject: Re: Perl DBI truncates Trailing Spaces from Placeholder Bind Variables. On Wed, Apr 03, 2002 at 06:11:33PM +0200, T. van Dyk wrote: - prepare for DBD::Oracle::db (DBI::db=HASH(0x401e4e6c)~0x401c34f8 'select area_node.core_description,lab_user.log, lab_logon_session, lab_user_role, lab_role, area_node, lab_resource where lab_user_role.role = lab_role.role_id and lab_user_role.for_user = lab_user.user_id and lab_logon_session.for_user = lab_user.user_id and lab_resource.dispatch_area = area_node.node_id and lab_resource.for_user = lab_user.user_id and not lab_logon_session.for_resource is null order by area_node.core_description') OCIStmtPrepare(40216a68,401fce98,'select area_node.core_description,lab_user.logon_id from lab_user, lab_logon_session, lab_user_role, lab_role, area_node, lab_resource where lab_user_role.role = lab_role.role_id and lab_user_role.for_user = lab_user.user_id and lab_logon_session.for_user = lab_user.user_id and lab_resource.dispatch_area = area_node.node_id and lab_resource.for_user = lab_user.user_id and not lab_logon_session.for_resource is null order by area_node.core_description',843,1,0)=SUCCESS Umm, any idea why the lab_user.logon_id from lab_user, is corrupted in the trace of the args to prepare? Most odd. OCIAttrGet(40216a68,OCI_HTYPE_STMT,401e8ab4,,24,401fce98)=SUCCESS dbd_st_prepare'd sql SELECT dbd_describe SELECT (EXPLICIT, lb 131072)... fbh 1: 'CORE_DESCRIPTION' NO null , otype 1- 5, dbsize 32/33, p32.s0 fbh 2: 'LOGON_ID' NO null , otype 1- 5, dbsize 8/9, p8.s0 So CORE_DESCRIPTION is expected to be no more that 32 characters (or maybe bytes) long. - fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x401c3510)~0x401c169c) OCIStmtFetch(40216a68,401fce98,1,2,0)=SUCCESS_WITH_INFO dbd_st_fetch 2 fields SUCCESS_WITH_INFO OCIErrorGet(401fce98,1,NULL,77ff1398,ORA-24345: A Truncation or null fetch error occurred ,1024,2)=SUCCESS OCIErrorGet after ORA-01406 error on field 1 of 2, ora_type 1 (er1:ok): -1, 24345: ORA-24345: A Truncation or null d OCIErrorGet(401fce98,2,NULL,77ff1398,ORA-24345: A Truncation or null fetch error occurred ,1024,2)=NO_DATA ERROR EVENT 24345 'ORA-24345: A Truncation or null fetch error occurred (DBD ERROR: ORA-01406 error on field 1 of 2) 0 (rc=1406): 'Nordfjord og Sogndal driftsområ' 1 (rc=0): 'T124837' !! ERROR: 24345 'ORA-24345: A Truncation or null fetch error occurred (DBD ERROR: ORA-01406 error on field 1 of 2, ' - fetchrow_array= ( ) [0 items] row51 at advx_stats_test.pl line 359 See the data value shown for field 0 above, the last two chars (after driftsomr) are \xc3\xa5. I see NLS_LANG env var is NORWEGIAN_NORWAY.UTF8 Looks like a chars vs bytes bug in DBD::Oracle. For now, try this... in oci8.c make this change if (fbh-ftype == 5) - fbh-disize += 1; /* allow for null terminator */ + fbh-disize = fbh-disize*4 + 1;/* allow for wide chars and null terminator */ Tim.
Re: Sybase stored procedures
On 2 Apr 2002 at 12:51, [EMAIL PROTECTED] wrote: Where I work, we use a lot (thousands) of Sybase stored procedures and have only recently adopted DBI and DBD::Sybase. Is there any DBIx-type module out there that handles fetching the return status and output params in an intuitive way? A quick search of CPAN has revealed nothing. Interesting ... I have a module that I wrote for myself that does something similar for MS SQL Server. I wrote it originally because we needed to call stored procs that return output parameters, and we were using DBD::ODBC on Windows machines, but DBD::Sybase (with FreeTDS) on Linux boxes. At that time, anyway, those two modules handled placeholders, parameter binding and output parameters differently, or not at all, now that I think about it. I have a proof-of-concept module that does this, tentatively called DBIx::Sybase::StoredProc, but I want to research any similar efforts before I work much more on it. I think such a module should: Anyway, I ended up naming the module DBIx::MSSQL::Proc::Output since it was really only designed to handle procs that returned output parameters and not recordsets. This is because all it did internally was create a SQL statement in the which the last line does a SELECT of the return value and all the output parameters, thus creating a one-row recordset that was then plugged back into the bound variables. Definitely not the optimal way to do it, but it works for its particular purpose. 1. Generate a sub that takes the SP arguments as Perl arguments, calls the SP, and returns the status. This seems to require looking up the argument types in syscolumns and systypes to find out which arguments must be quoted. 2. For output params, accept scalar refs and fill them in with the output values. This seems to require looking up the types in order to generate declarations of T-SQL output variables. 3. Fetch all result sets, checking each one's syb_result_type. Results that are not output params or the return status should be accumulated using fetchall_arrayref, or the caller should be allowed to provide a callback to fetch them. Accumulated results should be available as a list of return values ($status, $arrayref1, $arrayref2, ...) if the call is in list context. Issues with multiple recordsets are what kept me from ever going further with my module. I assume if I used something that was lower level than a generated SQL statement, I might be able to handle both output parameters and recordsets being returned by a single stored proc, but at the time I didn't have any procs that required that behavior. 4. Include some form of support for both named and positional parameters. 5. Possibly allow using AUTOLOAD to simplify usage and to cache generated subs. This can benefit from looking up all database names in sysdatabases and creating a package named after each one. My module does this when invoked with either of two syntaxes: use Module 'server' = $server, 'username' = $username, 'password' = $password; or: use Module 'dbh' = $dbh; If you can get something to work like this, I'd love to see it on CPAN. It's certainly a more robust-sounding module than mine. -Tim
Re: Sybase stored procedures
On 2 Apr 2002 at 19:43, Tim Bunce wrote: [EMAIL PROTECTED] wrote: I have a proof-of-concept module that does this, tentatively called DBIx::Sybase::StoredProc, I'd suggest just Sybase::StoredProc Why doesn't this fit into the DBIx namespace, in your opinion? Just curious. -Tim
as_string
I would like to suggest an as_string function that would work like:
$sth=$dbh-prepare('SELECT foo FROM bar WHERE baz=?');
$sth-as_string('blah');
Will print:
SELECT foo FROM bar WHERE baz='blah'
And maybe even:
$dbh-as_string('DELETE FROM foo WHERE bar=?',undef,can't);
Which should usually print:
DELETE FROM foo WHERE bar='can''t'
I think such a method can be very helpful for debugging. Right now I simply copy the
-do line and change it to sprintf with %s's..
Thanks.
Ido.
How to install DBI in Windows 2000
Hi I want use perl to connect SQL server ( or Access) via DBI from my PC ( windows 2000). But I can't use it. I think it necessary to install DBI . Where can I get the DBI module( for windows 2000) , How to install it ? your support would be highly appreciated. Best Regards, Vincent Liang Tel: +886-6-270-8989 ex:6292 Fax:+886-6-270-9666 Email : [EMAIL PROTECTED]
Re: Problem with SQL errorhandling using DBD:Oracle and DBD:Sybase
On Thu, Apr 04, 2002 at 08:21:27AM +0200, Sauer (ext_evosoft) Martin wrote:
sub err_trap
{
my $error_message = shift(_);
$DbS-rollback;
die $error_message\n ERROR: $DBI::err ($DBI::errstr)\n;
}
1) Why are $DBI::err and $DBI::errstr set to undef at that point of
execution? Is this the wrong way to check for errors? Do I need to test them
via the database handle ( $DbS-err ) ?
When you call rollback(), you reset the error from the previous statement.
You need to get the error message/code before you call rollback().
Ronald
RE: How to install DBI in Windows 2000
You should first have Perl installed (ActivePerl) preferably. Then use the ppm utility to install the modules. In command prompt... ppm install DBI and ppm install DBD::ODBC (You will also need DBD::ODBC to connect using ODBC) Ilya -Original Message- From: VincentLiang(???) To: [EMAIL PROTECTED] Sent: 4/4/02 2:29 AM Subject: How to install DBI in Windows 2000 Hi I want use perl to connect SQL server ( or Access) via DBI from my PC ( windows 2000). But I can't use it. I think it necessary to install DBI . Where can I get the DBI module( for windows 2000) , How to install it ? your support would be highly appreciated. Best Regards, Vincent Liang Tel: +886-6-270-8989 ex:6292 Fax:+886-6-270-9666 Email : [EMAIL PROTECTED]
RE: as_string
There is an attribute {Statement} already which does this. As far as
placeholders, you never know, since they are not bound till later in the
process. You can use trace() to figure out what is being bound.
Ilya
-Original Message-
From: Ido Trivizki
To: [EMAIL PROTECTED]
Sent: 4/3/02 6:52 PM
Subject: as_string
I would like to suggest an as_string function that would work like:
$sth=$dbh-prepare('SELECT foo FROM bar WHERE baz=?');
$sth-as_string('blah');
Will print:
SELECT foo FROM bar WHERE baz='blah'
And maybe even:
$dbh-as_string('DELETE FROM foo WHERE bar=?',undef,can't);
Which should usually print:
DELETE FROM foo WHERE bar='can''t'
I think such a method can be very helpful for debugging. Right now I
simply copy the -do line and change it to sprintf with %s's..
Thanks.
Ido.
RE: as_string
Do you mean the $sth-{Statement} attribute?
-Original Message-
From: Ido Trivizki [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 03, 2002 5:53 PM
To: [EMAIL PROTECTED]
Subject: as_string
I would like to suggest an as_string function that would work like:
$sth=$dbh-prepare('SELECT foo FROM bar WHERE baz=?');
$sth-as_string('blah');
Will print:
SELECT foo FROM bar WHERE baz='blah'
And maybe even:
$dbh-as_string('DELETE FROM foo WHERE bar=?',undef,can't);
Which should usually print:
DELETE FROM foo WHERE bar='can''t'
I think such a method can be very helpful for debugging.
Right now I simply copy the -do line and change it to
sprintf with %s's..
Thanks.
Ido.
RE: DBD Issue
Did you run the mytest\testfunc.pl? Thanks, Jeff -Original Message- From: Charles E. Robinson III [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 02, 2002 10:48 AM To: Jeff Urlwin Cc: [EMAIL PROTECTED] Subject: RE: DBD Issue Thanks, for the response Jeff, its very much appreciated. I posted it on the wrong list. Thanks, again. This is a solaris 8 server attempting to connect to a Solaris 8 progress server. here's my main env settings: UDBCINI=/usr/openlink/bin/udbc.ini ODBCHOME=/usr/openlink/odbcsdk $ODBCHOME=/usr/openlink/odbcsdk ODBCINI=/usr/openlink/bin/odbc.ini ODBCINSTINI=/usr/openlink/bin/odbcinst.ini CLASSPATH=/usr/openlink/jdk1.2/opljdbc2.jar LD_LIBRARY_PATH=/usr/openlink/lib LIBPATH=/usr/openlink/lib DBI_DSN=dbi:ODBC:alles9 DBI_USER= DBI_PASS= SHLIB_PATH=/usr/openlink/lib here's the output from the test verbose: livepes# make test TEST_VERBOSE=1 PERL_DL_NONLAZY=1 /usr/bin/perl -Iblib/arch -Iblib/lib -I/usr/local/lib/perl5/5.6.1/sun4-solar is -I/usr/local/lib/perl5/5.6.1 -e 'use Test::Harness qw(runtests $verbose); $verbose=1; runtests @ARGV;' t/*.t t/01base1..5 ok 1 ok 2 ok 3 ok 4 ok 5 ok t/02simple..1..17 ok 1 Test 2: connecting to the database ok 2 Test 3: create test table Unable to find a suitable test type for field COL_C at t/ODBCTEST.pm line 63. dubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 3-17 Failed 15/17 tests, 11.76% okay t/03dbatt...1..6 ok 1 ok 2 DBD::ODBC::db do failed: [OpenLink][ODBC][Progress Server]** Table PERL_DBD_TEST does not exist or cannot be accessed. (962) (SQL-42S02)(DBD: Execute immediate failed err=-1) at t/03dbatt.t line 60. Use of uninitialized value in numeric lt () at t/03dbatt.t line 19. Use of uninitialized value in numeric eq (==) at t/03dbatt.t line 20. not ok 3 ok 4 DBD::ODBC::db do failed: [OpenLink][ODBC][Progress Server]** Table PERL_DBD_TEST does not exist or cannot be accessed. (962) (SQL-42S02)(DBD: Execute immediate failed err=-1) at t/03dbatt.t line 60. Use of uninitialized value in numeric lt () at t/03dbatt.t line 28. Use of uninitialized value in numeric eq (==) at t/03dbatt.t line 29. ok 5 ok 6 FAILED test 3 Failed 1/6 tests, 83.33% okay t/05meth1..6 ok 1 ok 2 DBD::ODBC::db prepare failed: [OpenLink][ODBC][Progress Server]** Table PERL_DBD_TEST does not exist or cannot be accessed. (962) (SQL-42S02)(DBD: st_prepare/SQLPrepare err=-1) at t/05meth.t line 21. Can't call method execute on an undefined value at t/05meth.t line 22. Issuing rollback() for database handle being DESTROY'd without explicit disconnect(). dubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 3-6 Failed 4/6 tests, 33.33% okay t/07bind1..9 ok 1 Test 2: connecting to the database ok 2 Test 3: create test table Unable to find a suitable test type for field COL_C at t/ODBCTEST.pm line 63. dubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 3-9 Failed 7/9 tests, 22.22% okay t/08bind2...1..3 Test 1: insert various test data, without having this test tell the driver the type that is being bound to a column. This tests the use of SQLDescribeParam to obtain the column type on the insert. This is experimental and will most likely fail. [OpenLink][ODBC][Progress Server]** Table PERL_DBD_TEST does not exist or cannot be accessed. (962) (SQL-42S02)(DBD: st_prepare/SQLPrepare err=-1) at t/ODBCTEST.pm line 172. These are tests which rely upon the driver to tell what the parameter type is for the column. This means you need to ensure you tell your driver the type of the column in bind_col(). not ok 1 [OpenLink][ODBC][Progress Server]** Table PERL_DBD_TEST does not exist or cannot be accessed. (962) (SQL-42S02)(DBD: st_prepare/SQLPrepare err=-1) at t/ODBCTEST.pm line 172. not ok 2 [OpenLink][ODBC][Progress Server]** Table PERL_DBD_TEST does not exist or cannot be accessed. (962) (SQL-42S02)(DBD: st_prepare/SQLPrepare err=-1) at t/ODBCTEST.pm line 172. not ok 3 FAILED tests 1-3 Failed 3/3 tests, 0.00% okay t/09multi...1..0 DBD::ODBC::db prepare failed: [OpenLink][ODBC][Progress Server]** Unable to understand after -- PERL_DBD_TEST. (247) (SQL-S1000)(DBD: st_prepare/SQLPrepare err=-1) at t/09multi.t line 38. skipped test on this platform Failed Test Status Wstat Total Fail Failed List of Failed -- -- t/02simple.t 255 6528017 15 88.24% 3-17 t/03dbatt.t61 16.67% 3 t/05meth.t 255 65280 64 66.67% 3-6 t/07bind.t 255 65280 97 77.78% 3-9 t/08bind2.t33 100.00% 1-3 1 test skipped. Failed 5/7 test scripts, 28.57% okay. 30/46 subtests
Re: Connect to Oracle Database using Kerberos Tickets
Please let me know if you find out anything. Meanwhile, what parameters do you pass to SQL*Plus? Tim. On Thu, Apr 04, 2002 at 09:39:21AM +0200, Castillo, Felix wrote: Hi, I'm trying to connect to an Oracle database using ASO and Kerberos5. The connection through SQL*Plus works properly but I couldn't find any documentation/hints on how to connect through DBI/DBD. Is there anybody who know how to connect? Thanks for any hint Felix
Re: Sybase stored procedures
On Thu, Apr 04, 2002 at 12:22:34PM +0100, Tim Noll wrote: On 2 Apr 2002 at 19:43, Tim Bunce wrote: [EMAIL PROTECTED] wrote: I have a proof-of-concept module that does this, tentatively called DBIx::Sybase::StoredProc, I'd suggest just Sybase::StoredProc Why doesn't this fit into the DBIx namespace, in your opinion? Just curious. I'd rather modules that are very specific to one database live in the namespace of that database. The DBIx namespace is intended more for things that extend the DBI itself. Tim.
RE: Question
You must have Interbase client libs installed on your machine. It's trying to include a file from those libs and is not succeeding. Ilya -Original Message- From: Nguyen Tran Quoc Vinh To: Sterin, Ilya Cc: [EMAIL PROTECTED] Sent: 4/3/02 8:57 AM Subject: Question I have problem installing the DBI::Interbase in WIndows 2000. I have downloaded the DBD-InterBase-0.28.4 and compiled with nmake 1.5 but have not happenned. Please see the attachment file and give me a consultation. Thank you very much. A new DBI user. Nguyen Tran Quoc Vinh msg.txt
Re: as_string
- Original Message -
From: Ido Trivizki [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, April 04, 2002 3:52 AM
Subject: as_string
I would like to suggest an as_string function that would work like:
$sth=$dbh-prepare('SELECT foo FROM bar WHERE baz=?');
$sth-as_string('blah');
For binding params you could use $dbh-quote
Which will quote the param in the correct way for the used database.
Not all DBMSs use the same way of quoting.
Maarten.
occasional doing rollback error
I've been staring at this for a while and am getting nowhere. Hope the list can help...
One of the guys is getting intermittent errors out of the script below. The error is
Issuing rollback for database handle being DESTROY'd without explicit
disconnect.
The script:
#! /usr/local/bin/perl
use DBI;
use DBD::Oracle qw(:ora_types);
use utf8;
use FileHandle;
# Main
my ($dbh, $query, $sth, $rv, $song, $account_id, $win_dir, $account_name, $db_name,
$db_user, $db_password,
$db_file_path, $record_not_found, $conf_file_handle, $err_file_handle,
$out_file_handle, $file_name, $conf_file_name);
if($#ARGV != 4) {
print Usage: $0 WMA Directory Account Name Database DB User DB
Password\n;
exit (1);
}
$ENV{NLS_LANG} = AMERICAN_AMERICA.UTF8;
$win_dir = $ARGV[0];
$account_name = $ARGV[1];
$db_name = $ARGV[2];
$db_user = $ARGV[3];
$db_password = $ARGV[4];
$dbh = DBI-connect(DBI:Oracle:$db_name,$db_user,$db_password, { RaiseError = 1,
AutoCommit = 0 });
$dbh-{LongReadLen} = (1024 * 65);
$query = qq~ select cp.content_provider_id from im_content_provider cp where
cp.login_name = ? ~;
$sth = $dbh-prepare($query) or die Connection to DB failed;
$rv = $sth-execute($account_name) or die Execute Failed - $query - Account -
$ARGV[1];
$account_id = $sth-fetchrow or die Account Not Found - $account_name;
$sth-finish;
$query = qq~
select p.upc as sku, s.title as song_title, co.full_name as main_artist_name,
cy.name as copyright_text
from im_product p, im_album_song sa, im_song s, im_album a, im_collaborator
co, im_copyright cy
where p.file_path = ?
and sa.album_song_id = p.album_song_id
and s.song_id = sa.song_id
and a.album_id = sa.album_id
and a.content_provider_id = $account_id
and co.collaborator_id = s.main_artist_id
and cy.copyright_id = s.copyright_id
~;
$sth = $dbh-prepare($query) or die Prepare Failed - $query;
opendir dir_handle, $win_dir or die Failed to Open Directory $win_dir;
$out_file_handle = new FileHandle( $win_dir/GetMetaData_OUT.csv) or die File
Creation Failed - GetMetaData_OUT.csv - $!;
print $out_file_handle qq(SKU,Windows File Path,DB File Path,Song
Title,Main Artist Name,Copyright Text\n);
$err_file_handle = new FileHandle( $win_dir/GetMetaData_ERR.csv) or die File
Creation Failed - GetMetaData_ERR.csv - $!;
print $err_file_handle qq(Windows File Path,DB File Path,Error Message\n);
while($file_name = readdir dir_handle) {
if(!($file_name =~ /\.wma$/)) {
next;
}
$conf_file_name = $file_name;
$conf_file_name =~ s/\.wma$/\.conf/;
$db_file_path = qq(/opt/lmn/files/$account_name/wma/$file_name);
$record_not_found = 0;
$rv = $sth-execute($db_file_path) or die Execute Failed - $query - File Path -
$db_file_path;
$song = $sth-fetchrow_hashref or $record_not_found = 1;
if($record_not_found) {
print $err_file_handle qq($win_dir/$file_name,$db_file_path,Record Not
Found\n);
next;
}
$conf_file_handle = new FileHandle( $win_dir/$conf_file_name);
print $conf_file_handle qq(Title: $song-{SONG_TITLE}\n);
print $conf_file_handle qq(Author: $song-{MAIN_ARTIST_NAME}\n);
print $conf_file_handle qq(Copyright: $song-{COPYRIGHT_TEXT}\n);
print $conf_file_handle qq(Description: \n);
print $conf_file_handle qq(Rating: \n);
$conf_file_handle-close();
print $out_file_handle
qq($song-{SKU},$win_dir/$file_name,$db_file_path,$song-{SONG_TITLE},$song-{MAIN_ARTIST_NAME},$song-{COPYRIGHT_TEXT}\n);
}
closedir dir_handle;
$sth-finish;
$out_file_handle-close();
$err_file_handle-close();
$dbh-disconnect;
--
Re: Connect to Oracle Database using Kerberos Tickets
I thought it would be the same as using OS Authenticated logins,
i.e. set $user = '/' and don't pass a password.
For example:
my $dbh = DBI-connect(dbi:Oracle:$dbname, /, , {
RaiseError = 1, AutoCommit = 0
});
I believe I tried his when I had an ASO instance and it worked.
On Thu, 4 Apr 2002, Tim Bunce wrote:
Please let me know if you find out anything.
Meanwhile, what parameters do you pass to SQL*Plus?
Tim.
On Thu, Apr 04, 2002 at 09:39:21AM +0200, Castillo, Felix wrote:
Hi,
I'm trying to connect to an Oracle database using ASO and Kerberos5. The
connection through SQL*Plus works properly but I couldn't find any
documentation/hints on how to connect through DBI/DBD.
Is there anybody who know how to connect?
Thanks for any hint
Felix
Re: DBI vs. piping query to Mysql
If you are running apache with mod_perl and if the site has more than a
couple of visitors an hour and you use Apache::DBI for connection
pooling, you can bet that you will speed up by using DBI. A lot. A
whole lot.
On Wed, 2002-04-03 at 18:33, Peter Scott wrote:
At 04:28 PM 4/3/02 -0500, Kevin Old wrote:
Hello all,
I am a consultant brought in to manage and restructure some Perl scripts that
were written some time ago. The programmer at that time was using the
following code to do a query from within a CGI page.
${query} = SELECT ccyymmddhh FROM inventory ORDER BY ccyymmddhh ; ;
open( INPUT, echo \${query}\ |
/usr/local/mysql/bin/mysql -A -q -N gso| ) ;
{ccyymmddhh} = INPUT ;
chomp( {ccyymmddhh} ) ;
close( INPUT ) ;
*Shudder*
I think that I should clean this up and reprogram this to use DBD::mysql
rather than the way he does it here.
That's an understatement.
Anyone have any idea if it would improve performance?
The only way to be sure is to try both ways, but... I would bet long odds
that the performance will be greatly improved. The above has to fire off a
subprocess and build up and tear down a connection for each query. If the
DBI way turns out to be slower, look me up at the Perl Conference and I'll
buy you a drink. So, I suspect, will Tim Bunce :-)
I'd love to hear from people that have gone doing it this way to using DBI.
Obviously I can run benchmarks before and after and see which takes longer,
and I think that using DBI is not only much easier to read and manage, but
probably a little faster. Just seeking the advice of others.
Go with easier to read and manage first. The above code is NOT capable of
being reused in obvious ways (suppose $query contained quote marks or shell
metacharacters).
--
Peter Scott
Pacific Systems Design Technologies
http://www.perldebugged.com
RE: RE: DBD Issue
Ok -- that solves it. It seems that the problem stems from the fact that the ODBC driver reports that there is no long var char type. I have a hard time believing that, but that's what the driver seems to report. I think you'll be Ok using it, as the tests really don't account for that...they assume there is a long type. I probably could fix that for the future, but I hadn't run into that yet. Regards, Jeff -Original Message- From: Charles Robinson [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 10:25 PM To: Jeff Urlwin Cc: [EMAIL PROTECTED] Subject: RE: RE: DBD Issue livepes# make install Files found in blib/arch: installing files in blib/lib into architecture dependent library tree Writing /usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris/auto/DBD/ODBC/.packlist Appending installation info to /usr/local/lib/perl5/5.6.1/sun4-solaris/perllocal.pod then I ran testfunc.pl: livepes# ./testfunc.pl Information for DBI_DSN=dbi:ODBC:alles9 SQL_ACCESSIBLE_PROCEDURES (20): N SQL_ALTER_TABLE (86): 3 SQL_CATALOG_LOCATION (114): 1 SQL_CATALOG_NAME_SEPARATOR (41):. SQL_CURSOR_COMMIT_BEHAVIOR (23):1 SQL_DATABASE_NAME (16): pro1 SQL_DBMS_NAME (17): PROGRESS SQL_DBMS_VER (18): 09.01. SQL_DRIVER_NAME (6):oplodbc.so SQL_DRIVER_VER (7): 04.01.0903 SQL_IDENTIFIER_QUOTE_CHAR (29): SQL_MULT_RESULT_SETS (36): Y SQL_PROCEDURES (21):N SQL_PROCEDURE_TERM (40):Stored Procedure Getfunctions: ,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,11, 1,1,1,1,1,1,1,1,1,1,1,1,1,1,,1,,1,1,1,1,1,1,1,1,1,1,1,,1,,1,,, Listing all SQL_ALL_TYPES types Use of uninitialized value in join or string at ./testfunc.pl line 76. , TYPE_NAME, DATA_TYPE, PRECISION, LITERAL_PREFIX, LITERAL_SUFFIX, CREATE_PARAMS, NULLABLE, CASE_SENSITIVE, SEARCHABLE, UNSIGNED_ATTRIBUTE, MONEY, AUTO_INCREMENT, LOCAL_TYPE_NAME, MINIMUM_SCALE, MAXIMUM_SCALE, SQL_DATA_TYPE, SQL_DATETIME_SUB, SQL_NUM_PREC_RADIX, INTERVAL_PRECISION Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. CHARACTER, 1, 255, ', ', maxlength, 1, 1, 2, , 0, , CHAR, , , 1, , , Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. CHARACTER, 12, 255, ', ', maxlength, 1, 1, 2, , 0, , CHARACTER, , , 12, , , Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. LOGICAL, -7, 1, , , , 1, 0, 2, 1, 0, 0, LOGICAL, 0, 0, -7, , , Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. SMALLINT, -6, 3, , , , 1, 0, 2, 0, 0, 0, SMALLINT, 0, 0, -6, , 10, Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. SMALLINT, 5, 5, , , , 1, 0, 2, 0, 0, 0, SMALLINT, 0, 0, 5, , 10, Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. Use of uninitialized value in join or string at ./testfunc.pl line 84. INTEGER, 4, 10, , , , 1, 0, 2, 0, 0, 0, INTEGER, 0, 0, 4, , 10, Use
AW: Connect to Oracle Database using Kerberos Tickets
Hi Tim, the configuration is just like using the 'identified externally' feature. In fact you have to use this for connecting through Kerberos. 'sqlplus /' for IPC connections or 'sqlplus /@sid' for remote connections. This runs only after initializing Kerberos with a ticket. In fact I use following script use DBI; $db = DBI-connect( 'dbi:Oracle:', '/', '' ) or die; $th = $db-prepare( select sysdate from dual ); $th-execute(); print \n, $th-fetchrow_array, \n; and I get the error DBI-connect() failed: ORA-12649: Unknown encryption or data integrity algorithm (DBD ERROR: OCIServerAttach) at ./t line 2 Thanks a lot for your support Felix -Ursprungliche Nachricht- Von: Tim Bunce [mailto:[EMAIL PROTECTED]] Gesendet: Donnerstag, 4. April 2002 19:56 An: Castillo, Felix Cc: [EMAIL PROTECTED] Betreff: Re: Connect to Oracle Database using Kerberos Tickets Please let me know if you find out anything. Meanwhile, what parameters do you pass to SQL*Plus? Tim. On Thu, Apr 04, 2002 at 09:39:21AM +0200, Castillo, Felix wrote: Hi, I'm trying to connect to an Oracle database using ASO and Kerberos5. The connection through SQL*Plus works properly but I couldn't find any documentation/hints on how to connect through DBI/DBD. Is there anybody who know how to connect? Thanks for any hint Felix
Installation of DBI on Solaris 7 -
Hi, I am installing DBI on our Solaris m/c. I retrieved perl 5.6.2 from Sunfreeware site and installed perl by pkgadd and installed gcc and binutils. I have no problem with 'Perl Makefile.PL'. But 'make' gave some warnings and when I 'make test' it passes successfully only one test namely prepares. All other tests fails. I included the complete log file for information . Please help me. Thanks Prabahar $perl Makefile.PL *** Note: The optional PlRPC-modules (RPC::PlServer etc) are not installed. If you want to use the DBD::Proxy driver and DBI::ProxyServer modules, then you'll need to install the RPC::PlServer, RPC::PlClient, Storable and Net::Daemon modules. The CPAN Bundle::DBI may help you. You can install them any time after installing the DBI. You do *not* need these modules for typical DBI usage. Optional modules are available from any CPAN mirror, in particular http://www.perl.com/CPAN/modules/by-module http://www.perl.org/CPAN/modules/by-module ftp://ftp.funet.fi/pub/languages/perl/CPAN/modules/by-module Checking if your kit is complete... Looks good Writing Makefile for DBI cp Changes blib/lib/DBI/Changes.pm cp lib/DBD/Proxy.pm blib/lib/DBD/Proxy.pm cp lib/DBI/ProxyServer.pm blib/lib/DBI/ProxyServer.pm cp DBIXS.h blib/arch/auto/DBI/DBIXS.h cp dbi_sql.h blib/arch/auto/DBI/dbi_sql.h cp lib/DBD/NullP.pm blib/lib/DBD/NullP.pm cp dbipport.h blib/arch/auto/DBI/dbipport.h cp lib/DBI/Format.pm blib/lib/DBI/Format.pm cp dbd_xsh.h blib/arch/auto/DBI/dbd_xsh.h cp lib/DBI/Shell.pm blib/lib/DBI/Shell.pm cp DBI.pm blib/lib/DBI.pm cp lib/DBI/FAQ.pm blib/lib/DBI/FAQ.pm cp lib/DBD/ExampleP.pm blib/lib/DBD/ExampleP.pm cp lib/Bundle/DBI.pm blib/lib/Bundle/DBI.pm cp Driver.xst blib/arch/auto/DBI/Driver.xst cp lib/Win32/DBIODBC.pm blib/lib/Win32/DBIODBC.pm cp lib/DBD/Sponge.pm blib/lib/DBD/Sponge.pm cp lib/DBI/W32ODBC.pm blib/lib/DBI/W32ODBC.pm cp lib/DBI/DBD.pm blib/lib/DBI/DBD.pm $make /usr/local/bin/perl -p -e s/~DRIVER~/Perl/g blib/arch/auto/DBI/Driver.xst Perl.xsi /usr/local/bin/perl -I/usr/local/lib/perl5/5.6.1/sun4-solaris -I/usr/local/lib/perl5/5.6.1 /usr/local/lib/perl5/5.6.1/ExtUtils/xsubpp -typemap /usr/local/lib/perl5/5.6.1/ExtUtils/typemap Perl.xs Perl.xsc mv Perl.xsc Perl.c gcc -c -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O-DVERSION=\1.21\ -DXS_VERSION=\1.21\ -fPIC -I/usr/local/lib/perl5/5.6.1/sun4-solaris/CORE -Wall -Wno-comment -DDBI_NO_THREADS Perl.c Perl.xsi: In function `XS_DBD__Perl__st__prepare': Perl.xsi:197: warning: operation on `PL_na' may be undefined Perl.xsi: In function `XS_DBD__Perl__st_bind_param': Perl.xsi:233: warning: operation on `PL_na' may be undefined Perl.xsi: In function `XS_DBD__Perl__st_bind_param_inout': Perl.xsi:269: warning: operation on `PL_na' may be undefined /usr/local/bin/perl -I/usr/local/lib/perl5/5.6.1/sun4-solaris -I/usr/local/lib/perl5/5.6.1 /usr/local/lib/perl5/5.6.1/ExtUtils/xsubpp -typemap /usr/local/lib/perl5/5.6.1/ExtUtils/typemap DBI.xs DBI.xsc mv DBI.xsc DBI.c gcc -c -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O-DVERSION=\1.21\ -DXS_VERSION=\1.21\ -fPIC -I/usr/local/lib/perl5/5.6.1/sun4-solaris/CORE -Wall -Wno-comment -DDBI_NO_THREADS DBI.c Running Mkbootstrap for DBI () chmod 644 DBI.bs rm -f blib/arch/auto/DBI/DBI.so LD_RUN_PATH= gcc -G -L/usr/local/lib DBI.o -o blib/arch/auto/DBI/DBI.so chmod 755 blib/arch/auto/DBI/DBI.so cp DBI.bs blib/arch/auto/DBI/DBI.bs chmod 644 blib/arch/auto/DBI/DBI.bs /usr/local/bin/perl -Iblib/arch -Iblib/lib -I/usr/local/lib/perl5/5.6.1/sun4-solaris -I/usr/local/lib/perl5/5.6.1 dbiproxy.PL dbiproxy Extracted dbiproxy from dbiproxy.PL with variable substitutions. cp dbiproxy blib/script/dbiproxy /usr/local/bin/perl -I/usr/local/lib/perl5/5.6.1/sun4-solaris -I/usr/local/lib/perl5/5.6.1 -MExtUtils::MakeMaker -e MY-fixin(shift) blib/script/dbiproxy /usr/local/bin/perl -Iblib/arch -Iblib/lib -I/usr/local/lib/perl5/5.6.1/sun4-solaris -I/usr/local/lib/perl5/5.6.1 dbish.PL dbish Extracted dbish from dbish.PL with variable substitutions. cp dbish blib/script/dbish /usr/local/bin/perl -I/usr/local/lib/perl5/5.6.1/sun4-solaris -I/usr/local/lib/perl5/5.6.1 -MExtUtils::MakeMaker -e MY-fixin(shift) blib/script/dbish DBI.xs: In function `dbih_clearcom': DBI.xs:971: warning: unused variable `Perl___notused' DBI.xs: In function `dbih_get_fbav': DBI.xs:1109: warning: unused variable `Perl___notused' DBI.xs: In function `dbih_set_attr_k': DBI.xs:1201: warning: unused variable `Perl___notused' DBI.xs: In function `log_where': DBI.xs:1743: warning: unused variable `Perl___notused' DBI.xs: In function `XS_DBI_dispatch': DBI.xs:2275: warning: unused variable `Perl___notused' DBI.xs: In function `XS_DBD_st_bind_col': DBI.xs:3054: warning: operation on `PL_na' may be
DBI:DBD
Hi All,
How to execute a stored procedure in ORACLE using DBD::Oracle?
pls help me, now i can excute some simple query but i could not execute
my procedure by syntax
$dbh-do(EXEC myprotest('aaa',1));
i get the error in log:
DBD::Oracle::db do failed: ORA-00900: invalid SQL statement (DBD ERROR:
OCIStmtExecute) at ... line 137
RE: DBD
Please read perldoc DBD::Oracle before starting any development with
this module.
Ilya
-Original Message-
From: Vu Van Toan [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 05, 2002 12:52 AM
To: [EMAIL PROTECTED]
Subject: DBI:DBD
Hi All,
How to execute a stored procedure in ORACLE using DBD::Oracle?
pls help me, now i can excute some simple query but i could
not execute my procedure by syntax
$dbh-do(EXEC myprotest('aaa',1));
i get the error in log:
DBD::Oracle::db do failed: ORA-00900: invalid SQL statement
(DBD ERROR:
OCIStmtExecute) at ... line 137
AW: Problem with SQL errorhandling using DBD:Oracle and DBD:Sybase
I'm sorry, I also found this rollback handling error and now its working for
Oracle. But still not for DBD::Sybase!!
I tried V 0.91 and V 0.94 - both the same. All servermessages get printed to
stderr and I get an undef in the program.
That's also for the servermessages at connect time. (The first two lines in
the appended level 2 trace)
Need desperate help.
I've tried to understand the code and that's how far I came:
The problem seems to be in the result handling
st_next_result() - ct_results(13) == 1 - CS_CMD_FAIL
st_next_result() - ct_results(12) == 1 - CS_CMD_SUCCED
ct_results() final retcode = 15 - CS_END_RESULT
st_next_result() - lasterr = 0, lastsev = 0-with no error
Is there anybody, who can tell me how this handling is supposed to be
working???
Martin
Here's the trace output:
Changed database context to 'wsdbs'.
Changed language setting to us_english.
DBI 1.21-nothread dispatch trace level set to 2
- do for DBD::Sybase::db (DBI::db=HASH(0x1e7214)~0x1f3af8 'INSERT INTO
wsdb_fehlerbeschreibung ( FEHLER_ID, CREATION_TS) VALUES ( 1491, '27.02.2002
10:41:00')')
2 - prepare for DBD::Sybase::db (DBI::db=HASH(0x1f3af8)~INNER 'INSERT
INTO wsdb_fehlerbeschreibung ( FEHLER_ID, CREATION_TS) VALUES ( 1491,
'27.02.2002 10:41:00')' undef)
2 - prepare= DBI::st=HASH(0x1e7244) at Sybase.pm line 131
- execute for DBD::Sybase::st (DBI::st=HASH(0x1e7244)~0x174fb0)
syb_db_opentran() - ct_command(
BEGIN TRAN DBI105808
) = 1
syb_db_opentran() - ct_send() = 1
syb_db_opentran() - ct_results(12) == 1
syb_st_execute() - ct_command() OK
syb_st_execute() - ct_send() OK
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated.
st_next_result() - ct_results(13) == 1
st_next_result() - ct_results(12) == 1
ct_results() final retcode = 15
st_next_result() - lasterr = 0, lastsev = 0
syb_st_execute() - got CS_CMD_DONE: resetting ACTIVE, moreResults,
dyn_execed
- execute= undef at Sybase.pm line 132
- do= undef at tst7.pl line 27
- $DBI::err (*) FETCH from lasth=DBI::st=HASH(0x174fb0)
- err= undef
Use of uninitialized value in concatenation (.) or string at tst7.pl line
42.
- $DBI::errstr () FETCH from lasth=DBI::st=HASH(0x174fb0)
DBD::Sybase::st::errstr
Use of uninitialized value in concatenation (.) or string at tst7.pl line
42.
-Ursprüngliche Nachricht-
Von: David Kirol [SMTP:[EMAIL PROTECTED]]
Gesendet am: Donnerstag, 4. April 2002 15:00
An: Sauer (ext_evosoft) Martin
Betreff: RE: Problem with SQL errorhandling using DBD:Oracle and
DBD:Sybase
Martin,
The oracle error string is undefined in the sub because you are
reading the
error string from the roll back. Error trapping in perl is usually done
with
eval.
HTH
-Original Message-
From: Sauer (ext_evosoft) Martin
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 04, 2002 1:21 AM
To: '[EMAIL PROTECTED]'
Subject: Problem with SQL errorhandling using DBD:Oracle and DBD:Sybase
I have installed the following SW on Solaris 2.6:
perl 5.6.1
DBI-1.21
DBD:Oracle-1.12
DBD:Sybase-0.94
freetds-0.53 (connecting to MS-SQL 7.0 with TDS_VER 7.0)
Because I'm new to DBI, I tried to write some programs
(copies/modifications
of examples found in the documentation) to learn the handling.
Now I stuck at the error handling of SQL-Statements. Here is my test:
use DBI;
#my $server = dbi:Oracle:$obase;
my $server = dbi:Sybase:server=$obase;
my $DbS = DBI-connect($server, $ouser, $opass,
{ AutoCommit = 0, RaiseError = 0, PrintError = 0 } )
or die $DBI::errstr;
my $stmt = INSERT INTO tbl1( ID, CREATION_TS) VALUES (1491, '27.02.2002
10:41:00');
#Should return 'uniq constraint' on Oracle and 'out-of-range datetime' on
MS-SQL
$DbS-do($stmt) or err_trap(Error during do);
$DbS-disconnect;
#---
# Subroutines
#---
sub err_trap
{
my $error_message = shift(@_);
$DbS-rollback;
die $error_message\n ERROR: $DBI::err ($DBI::errstr)\n;
}
I thought after reading the documentation, I should be able to test in
err_trap via $DBI::err for specific SQL error codes and handle them.
But...
Connecting to MS-Sql, I get the following output:
Changed database context to 'wsdbs'.
Changed language setting to us_english.
The conversion of a char data type to a datetime data type resulted in
an
out-of-range datetime value.
The statement has been terminated.
Use of uninitialized value in concatenation (.) or string at tst4.pl
line
33.
Use of uninitialized value in concatenation (.) or string at tst4.pl
line
33.
Error during do
ERROR: ()
Issuing rollback() for database handle being DESTROY'd
Execution ERROR: Can't find shared columns!
I've recently started using DBD::CSV and am having some really good
results with it. I chose it because I wanted to keep my data in
editable
files, I wanted to be able to do simple change control management (ie,
using RCS/CVS) and yet I needed to do simple relational queries over
them ... DBD::CSV is simply the best thing for all of these things,
especially whilst I'm tinkering with my data model ... so thanks for
the
module!
I'm having a few niggling things happen though. One of these which is
annoying, but not critical is an warning I often get with a query which
has a table join or two: 'Execution ERROR: Can't find shared columns!'
It doesn't seem to affect the results of the query, but I don't know
exactly why I'm seeing it, since there are shared columns as far as I
can see - they're what I'm basing the join on, afterall ...
Here's the relevant bits of the data model:
people: PersonID LastName FirstName MiddleName
positions: PositionID PersonID DeptID RoleID
tenants:PersonID MachineID RoomID
phones: PhoneNo RoomID Description
rooms: RoomID BuildingID RoomNo Description
buildings: BuildingID CampusCode BuildingNo Description
and the query that's giving me trouble at the moment (it's stage
one
on a query to generate a departmental phone book):
SELECT people.PersonID, positions.RoleID, positions.DeptID,
phones.PhoneNo, rooms.RoomNo, rooms.BuildingID,
buildings.BuildingNo, buildings.CampusCode
FROMpeople, positions, tenants, phones, rooms, buildings
WHERE positions.PersonID = people.PersonID
AND tenants.PersonID = people.PersonID
AND phones.RoomID = tenants.RoomID
AND rooms.RoomID = tenants.RoomID
AND buildings.BuildingID = rooms.BuildingID
When I execute the query I get 4 'Can't find shared columns!' warnings,
but the correct results are returned ...
I don't seem to be able to turn it off either. From the manual page and
from looking at source code for SQL::Statement I would have thought
that
$sth-{'PrintError'} = undef;
or
$dbh-{'PrintError'} = undef;
would turn off the error message and stop the warning, but it doesn't.
(yes, the sample query in the manual page was the one I based my code
on,
it just got more tables and a more complex query ... :)
The relevant portion of SQL::Statement that is causing this appears to
be
within join_2_tables(), line 514, approx:
%is_shared = map {$_=1} @shared_cols;
$self-do_err(Can't find shared columns!) unless @shared_cols;
for my $c(@shared_cols) {
if ( !$iscolA{$c} and !$iscolB{$c} ) {
$self-do_err(Can't find shared columns!);
}
}
do_err() has the following lines (which is what I thought would control
the output):
$self-{errstr} = $err;
warn $err if $self-{PrintError};
# print $err if $self-{PrintError};
die \n if $self-{RaiseError};
can anyone help me out here?
thanks
--
Malcolm HerbertThis brain intentionally
[EMAIL PROTECTED]left blank
Re: occasional doing rollback error
- Original Message - From: Laurie Gennari [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 05, 2002 1:10 AM Subject: occasional doing rollback error I've been staring at this for a while and am getting nowhere. Hope the list can help... One of the guys is getting intermittent errors out of the script below. The error is Issuing rollback for database handle being DESTROY'd without explicit disconnect. Quess the error message sais it all. When your script dies before it reaches the $dbh-disconnect() statement, your $dbh is cleaned up, but you didnt call disconnect() on it. Therefore it issues a rollback when your script terminates. You could put the disconnect in an END block, or keep your script from dying at unexpected places :-) Maarten.
