RE: problem compiling dbd-oracle 1.12 on
Hi, Just to confirm, after downloading the bumper Oracle 9iAS release 2, that this software *is* bundled with Perl 5.6.1 (Oracle compiled not Activestate), DBI version 1.20 and DBD-Oracle version 1.12. Regards, Rob -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Thursday, October 17, 2002 11:22 AM To: Rob Leadbeater; [EMAIL PROTECTED] Subject: RE: problem compiling dbd-oracle 1.12 on Thanks, Rob, good information on the links. The software requirements are not useful for me, because I do not want to backport my system to historic software like Studio 5 and NT SP3. My current workaround is using an seperate version of ActivePerl, installing DBI and DBD with PPM and starting every perl script with shebang (#!c:\activePerl/perl/bin ). The pitty is, that this will not use mod_perl, which means that the perl scripts are not run in memory as with mod_perl. Additionaly I will download 9iAS to see wether this is shipped with DBI and DBD. I read in a comment from Oracle support, that it's NOT. Anyway, will send an update after 1.3 GB download. Thanks again Knut -Original Message- From: [EMAIL PROTECTED] [mailto:rob.leadbeater;lynx.co.uk] Sent: Thursday, October 17, 2002 10:02 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: problem compiling dbd-oracle 1.12 on Hi Knut, Did a bit more digging around yesterday after installing Oracle 9.2 (DB not AS) on a new Win32 system. The bundled Perl is version 5.00503, which was compiled with cl.exe. I'm not familiar with that, but digging around with Google, I managed to find some release notes for 9iAS release one, which I assume uses the same versions of Perl. These state that you need to have Visual Studio version 5 to compile DBI/DBD-Oracle. Details can be found at: http://157.26.164.60/ias_doc/install_doc/doc/relnotes/relnotes.htm On a better note, the FAQ for Oracle 9iAS release 2, states that DBI/DBD is bundled. http://otn.oracle.com/products/ias/ohs/collateral/r2/ohs-faq-v2-part3.htm (apologies if my mailer splits the URLs up). This could cause fun with Oracle's support, however as Oracle HTTP Server is essentially Apache, it should be relatively simple to compile your own Perl, or use Activestate's version, and then modify the relevant Apache config files to point to that version rather than the bundled Oracle version. Note that I haven't actually done this yet, but it seems a fairly logical thing to do. Regards, Rob -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, October 16, 2002 11:14 AM To: Rob Leadbeater Cc: [EMAIL PROTECTED] Subject: RE: problem compiling dbd-oracle 1.12 on Rob, I am using 9.2.0.0.0 database that brings HTTP webserver with it. But DBD and DBI are not bundled with it. You have to install it yourself. In the Oracle FAQ I read that it's the same with 9iAS. Don't know , why. Knut -Original Message- From: [EMAIL PROTECTED] [mailto:rob.leadbeater;lynx.co.uk] Sent: Wednesday, October 16, 2002 11:57 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: problem compiling dbd-oracle 1.12 on Hi Knut, Are you using Oracle 9iAS ? Looking at the URL below: http://otn.oracle.com/products/ias/daily/oct04.html appears to suggest that DBI-Oracle is already bundled, or will be soon, so hopefully it won't be required to actually compile it ... Regards, Rob -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, October 16, 2002 10:18 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: problem compiling dbd-oracle 1.12 on win32 Ilya, the perl I use is delivered with Oracle HTTP webserver. I am pretty sure I will never find out what compiler Oracle used. Is there a chance to find out where the missing symbols come from, means wich library is missing ? Regards Knut -Original Message- From: Sterin, Ilya [mailto:Isterin;ciber.com] Sent: Tuesday, October 15, 2002 8:45 PM To: 'Knut Herzog '; ''Crown David T. (DNREC)' '; 'dbi-users perl org ' Subject: RE: problem compiling dbd-oracle 1.12 on win32 In your case, you have to first find out what compiler was used to compile your Perl. Then you must use the same compile and it's make/nmake equivalent to compile DBI, DBD::Oracle, and any other perl module. Ilya -Original Message- From: Knut Herzog To: 'Crown David T. (DNREC)'; dbi-users perl org Sent: 10/15/02 10:29 AM Subject: RE: problem compiling dbd-oracle 1.12 on win32 Thanks David, you're right, ActivePerl and ppm are the easiest installation for perl on windows. I installed it with ppm on activeperl and had no problems. But in this case I have to use the perl that is delivered with Oracle Web server - non active Perl. It comes without the ppm and I didn't found a downloadable ppm - only bundles with activeperl. I read in the ppm 2.6 docs, that ppm should only be used with activePerl. Any other hints ? Regards
RE: :ODBC bug?
Dave Please *always* post to [EMAIL PROTECTED] Not sure about this one, but it appears that my version ($DBD::ODBC::VERSION = '0.40';) doesn't deal with nulls properly. Please upgrade to .45_18, if possible. Also, post code, if possible. With select * you usually can't assume the column ordering. Try using fetchrow_hashref and use Data::Dumper to view the contents. Finally, post a trace file (set the level = 9) along with the code ($dbh-trace(9,'filename.log'). Regards, Jeff I've been selected some data, and when one of the fields is a null, it's being populated with the value from that field in the previous returned row. Some data: select * from strand_content where sc_group_code = 'MUSIC_CONTENT' and str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E' and sc_index = 5 and ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER')) Gives: (field order: str_uniq_id, sc_group_code, sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user) (str_uniq_id isn't showing as i need to convert(varchar(255), str_uniq_id) if i want it to show. not relevant here. $VAR1 = [ [ '', 'MUSIC_CONTENT', 'DURATION', '5', '', 'Oct 17 2002 05:02PM', 'RAP Administrator' ], [ '', 'MUSIC_CONTENT', 'PERFORMER', '5', 'RICHARD G. MITCHELL', 'Oct 17 2002 05:02PM', 'RAP Administrator' ] ]; Note the value of the fifth field. null for DURATION (when checked in Query analyser), although IME with DBI this normally is given as undef rather than ''. And RICHARD G. MITCHELL for PERFORMER. If i swap the order around: select * from strand_content where sc_group_code = 'MUSIC_CONTENT' and str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E' and sc_index = 5 and ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER')) order by sc_item_code desc then i get this: $VAR1 = [ [ '', 'MUSIC_CONTENT', 'PERFORMER', '5', 'RICHARD G. MITCHELL', 'Oct 17 2002 05:02PM', 'RAP Administrator' ], [ '', 'MUSIC_CONTENT', 'DURATION', '5', 'RICHARD G. MITCHELL', 'Oct 17 2002 05:02PM', 'RAP Administrator' ] ]; Both fields are now RICHARD G. MITCHELL. This seems a bit odd (and broken) to me. I'd be happy to help in further debugging if i can (or happy to have it explained what i'm misunderstanding :) Cheers, -- dave thorn | [EMAIL PROTECTED]
Access to Clipper *.DBV files
Is their an interface to access Clipper Db *.DBV files? (*.DBV files are a variation of the *.DBT files that hold memo fields). Thanks, Phil - The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
Re: :ODBC bug?
Hope this helps
On Tue, Oct 22, 2002 at 08:54:42AM -0400, Jeff Urlwin wrote:
Not sure about this one, but it appears that my version
($DBD::ODBC::VERSION = '0.40';) doesn't deal with nulls properly.
Please upgrade to .45_18, if possible.
Not possible at the moment.
Also, post code, if possible. With select * you usually can't assume the
column ordering. Try using fetchrow_hashref and use Data::Dumper to view
the contents.
$ /usr/bin/perl --version
This is perl, v5.6.1 built for i386-linux
#!/usr/bin/perl -w
use strict;
use DBI;
use Data::Dumper;
my $dbh = make_dbh();
$dbh-trace(9, '/tmp/dbi_trace.log');
print \n\nFIRST\n\n;
my $sql = q[SELECT convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
];
my $sth = $dbh-prepare($sql) or die $dbh-errstr;
$sth-execute() or die $dbh-errstr;
while(my $data = $sth-fetchrow_hashref()) {
print Dumper $data;
}
print \n\nSWAP ORDER\n\n;
$sql .= ' ORDER BY sc_item_code DESC';
$sth = $dbh-prepare($sql) or die $dbh-errstr;
$sth-execute() or die $dbh-errstr;
while(my $data = $sth-fetchrow_hashref()) {
print Dumper $data;
}
sub make_dbh {
my $u = 'xx';
my $p = 'yy';
my $dbh = DBI-connect(dbi:ODBC:DSN=gaffa2;UID=$u;PWD=$p,
{ RaiseError = 1,
AutoCommit = 0 }) or die $DBI::errstr\n;
return $dbh;
}
__END__
../sql_file.pl 2 ~/2 ~/1
$ cat ~/1
FIRST
$VAR1 = {
'' = '94F6C407-D567-11D6-A75A-00508BFCB09E',
'SC_ITEM_CODE' = 'DURATION',
'SC_LAST_MOD' = 'Oct 17 2002 05:02PM',
'SC_GROUP_CODE' = 'MUSIC_CONTENT',
'SC_LAST_MOD_USER' = 'RAP Administrator',
'SC_INDEX' = '5',
'SC_VALUE' = ''
};
$VAR1 = {
'' = '94F6C407-D567-11D6-A75A-00508BFCB09E',
'SC_ITEM_CODE' = 'PERFORMER',
'SC_LAST_MOD' = 'Oct 17 2002 05:02PM',
'SC_GROUP_CODE' = 'MUSIC_CONTENT',
'SC_LAST_MOD_USER' = 'RAP Administrator',
'SC_INDEX' = '5',
'SC_VALUE' = 'RICHARD G. MITCHELL'
};
SWAP ORDER
$VAR1 = {
'' = '94F6C407-D567-11D6-A75A-00508BFCB09E',
'SC_ITEM_CODE' = 'PERFORMER',
'SC_LAST_MOD' = 'Oct 17 2002 05:02PM',
'SC_GROUP_CODE' = 'MUSIC_CONTENT',
'SC_LAST_MOD_USER' = 'RAP Administrator',
'SC_INDEX' = '5',
'SC_VALUE' = 'RICHARD G. MITCHELL'
};
$VAR1 = {
'' = '94F6C407-D567-11D6-A75A-00508BFCB09E',
'SC_ITEM_CODE' = 'DURATION',
'SC_LAST_MOD' = 'Oct 17 2002 05:02PM',
'SC_GROUP_CODE' = 'MUSIC_CONTENT',
'SC_LAST_MOD_USER' = 'RAP Administrator',
'SC_INDEX' = '5',
'SC_VALUE' = 'RICHARD G. MITCHELL'
};
$ cat ~/2
query = SELECT convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
query = SELECT convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
ORDER BY sc_item_code DESC
Finally, post a trace file (set the level = 9) along with the code
($dbh-trace(9,'filename.log').
DBI::db=HASH(0x824ad44) trace level set to 9 in DBI 1.21-nothread
- prepare for DBD::ODBC::db (DBI::db=HASH(0x824adf8)~0x824ad44 'SELECT
convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
')
dbih_setup_handle(DBI::st=HASH(0x824ae70)=DBI::st=HASH(0x824aef4), DBD::ODBC::st,
824aec4, Null!)
dbih_make_com(DBI::db=HASH(0x824ad44), DBD::ODBC::st, 200)
dbih_setup_attrib(DBI::st=HASH(0x824aef4), Err, DBI::db=HASH(0x824ad44))
SCALAR(0x82362b4) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x824aef4), State, DBI::db=HASH(0x824ad44))
SCALAR(0x82362fc) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x824aef4), Errstr, DBI::db=HASH(0x824ad44))
SCALAR(0x82362d8) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x824aef4), Handlers, DBI::db=HASH(0x824ad44))
ARRAY(0x824adbc) (already defined)
RE: :ODBC bug?
Ok -- keeping the long trace here...what I see here is that SQLFetch()
(which is supposed to update the field information) doesn't seem to be doing
that. The first time through, we see (snipped from trace below)
fetch col#4 SC_VALUE datalen=0 displ=256
fetch col#4 SC_VALUE datalen=19 displ=256
The second time through, I see:
fetch col#4 SC_VALUE datalen=19 displ=256
fetch col#4 SC_VALUE datalen=19 displ=256
I *think* that's a driver problem. You indicated it works in another
environment (query analyzer). Is that using the ODBC driver too? How about
What driver/version are you using? What database? I'd like to see if I can
reproduce here.
Regards,
Jeff
Hope this helps
On Tue, Oct 22, 2002 at 08:54:42AM -0400, Jeff Urlwin wrote:
Not sure about this one, but it appears that my version
($DBD::ODBC::VERSION = '0.40';) doesn't deal with nulls properly.
Please upgrade to .45_18, if possible.
Not possible at the moment.
Also, post code, if possible. With select * you usually can't
assume the
column ordering. Try using fetchrow_hashref and use
Data::Dumper to view
the contents.
$ /usr/bin/perl --version
This is perl, v5.6.1 built for i386-linux
#!/usr/bin/perl -w
use strict;
use DBI;
use Data::Dumper;
my $dbh = make_dbh();
$dbh-trace(9, '/tmp/dbi_trace.log');
print \n\nFIRST\n\n;
my $sql = q[SELECT convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
];
my $sth = $dbh-prepare($sql) or die $dbh-errstr;
$sth-execute() or die $dbh-errstr;
while(my $data = $sth-fetchrow_hashref()) {
print Dumper $data;
}
print \n\nSWAP ORDER\n\n;
$sql .= ' ORDER BY sc_item_code DESC';
$sth = $dbh-prepare($sql) or die $dbh-errstr;
$sth-execute() or die $dbh-errstr;
while(my $data = $sth-fetchrow_hashref()) {
print Dumper $data;
}
sub make_dbh {
my $u = 'xx';
my $p = 'yy';
my $dbh = DBI-connect(dbi:ODBC:DSN=gaffa2;UID=$u;PWD=$p,
{ RaiseError = 1,
AutoCommit = 0 }) or die $DBI::errstr\n;
return $dbh;
}
__END__
./sql_file.pl 2 ~/2 ~/1
$ cat ~/1
FIRST
$VAR1 = {
'' = '94F6C407-D567-11D6-A75A-00508BFCB09E',
'SC_ITEM_CODE' = 'DURATION',
'SC_LAST_MOD' = 'Oct 17 2002 05:02PM',
'SC_GROUP_CODE' = 'MUSIC_CONTENT',
'SC_LAST_MOD_USER' = 'RAP Administrator',
'SC_INDEX' = '5',
'SC_VALUE' = ''
};
$VAR1 = {
'' = '94F6C407-D567-11D6-A75A-00508BFCB09E',
'SC_ITEM_CODE' = 'PERFORMER',
'SC_LAST_MOD' = 'Oct 17 2002 05:02PM',
'SC_GROUP_CODE' = 'MUSIC_CONTENT',
'SC_LAST_MOD_USER' = 'RAP Administrator',
'SC_INDEX' = '5',
'SC_VALUE' = 'RICHARD G. MITCHELL'
};
SWAP ORDER
$VAR1 = {
'' = '94F6C407-D567-11D6-A75A-00508BFCB09E',
'SC_ITEM_CODE' = 'PERFORMER',
'SC_LAST_MOD' = 'Oct 17 2002 05:02PM',
'SC_GROUP_CODE' = 'MUSIC_CONTENT',
'SC_LAST_MOD_USER' = 'RAP Administrator',
'SC_INDEX' = '5',
'SC_VALUE' = 'RICHARD G. MITCHELL'
};
$VAR1 = {
'' = '94F6C407-D567-11D6-A75A-00508BFCB09E',
'SC_ITEM_CODE' = 'DURATION',
'SC_LAST_MOD' = 'Oct 17 2002 05:02PM',
'SC_GROUP_CODE' = 'MUSIC_CONTENT',
'SC_LAST_MOD_USER' = 'RAP Administrator',
'SC_INDEX' = '5',
'SC_VALUE' = 'RICHARD G. MITCHELL'
};
$ cat ~/2
query = SELECT convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
query = SELECT convert(varchar(255), str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E'
AND sc_index = 5
AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') )
ORDER BY sc_item_code DESC
Finally, post a trace file (set the level = 9) along with the code
($dbh-trace(9,'filename.log').
DBI::db=HASH(0x824ad44) trace level set to 9 in DBI 1.21-nothread
- prepare for DBD::ODBC::db
(DBI::db=HASH(0x824adf8)~0x824ad44 'SELECT convert(varchar(255),
str_uniq_id), sc_group_code,
sc_item_code, sc_index, sc_value, sc_last_mod, sc_last_mod_user
FROM strand_content
WHERE sc_group_code = 'MUSIC_CONTENT'
AND str_uniq_id =
Re: :ODBC bug?
On Tue, Oct 22, 2002 at 09:54:03AM -0400, Jeff Urlwin wrote: [SQLFetch()] The first time through, we see (snipped from trace below) fetch col#4 SC_VALUE datalen=0 displ=256 fetch col#4 SC_VALUE datalen=19 displ=256 The second time through, I see: fetch col#4 SC_VALUE datalen=19 displ=256 fetch col#4 SC_VALUE datalen=19 displ=256 I *think* that's a driver problem. You indicated it works in another environment (query analyzer). Is that using the ODBC driver too? How about incomplete sentence? How about...? What driver/version are you using? What database? I'd like to see if I can reproduce here. This is where my knowledge goes a bit flakey. It works in MS Query Analyzer, which, according to someone here, isn't using ODBC. Query analyser version: 7.00.623 SQL Server version 7.00.961, SP3; on NT 4, SP6 Cheers, -- dave thorn | [EMAIL PROTECTED]
Re: :ODBC bug?
Dave Thorn wrote: This is where my knowledge goes a bit flakey. It works in MS Query Analyzer, which, according to someone here, isn't using ODBC. Are you certain of that, last time I looked, it did use ODBC. -- Nick Gorham Easysoft Limited http://www.easysoft.com
Re: :ODBC bug?
On Tue, Oct 22, 2002 at 03:18:08PM +0100, Nick Gorham wrote: Dave Thorn wrote: This is where my knowledge goes a bit flakey. It works in MS Query Analyzer, which, according to someone here, isn't using ODBC. Are you certain of that, no :) -- dave thorn | [EMAIL PROTECTED]
RE: :ODBC bug?
On Tue, Oct 22, 2002 at 09:54:03AM -0400, Jeff Urlwin wrote: [SQLFetch()] The first time through, we see (snipped from trace below) fetch col#4 SC_VALUE datalen=0 displ=256 fetch col#4 SC_VALUE datalen=19 displ=256 The second time through, I see: fetch col#4 SC_VALUE datalen=19 displ=256 fetch col#4 SC_VALUE datalen=19 displ=256 I *think* that's a driver problem. You indicated it works in another environment (query analyzer). Is that using the ODBC driver too? How about incomplete sentence? How about...? Sorry -- brain problem, this morning. I was going to say, how about MS Query (not analyzer, but the query that comes with MS office which just queries the db), then I looked further to see you were on Linuxcan you give us a little more about your configuration? I can guess that you may be using one of the following: - 1) using DBI, DBD::Proxy or and MS ODBC drivers on the SQL Server side. - 2) using DBD::ODBC and EasySoft OOB or some other bridge - 3) using DBD::ODBC and something like FreeTDS - 4) some other config... What version of the MDAC is on your SQLServer itself? Since nearly all of these are dependant upon the ODBC driver on the server (or wherever the bridge is installed). If you can update that to MDAC 2.7, that would be great. There were some issues with 2.6 and below. Jeff What driver/version are you using? What database? I'd like to see if I can reproduce here. This is where my knowledge goes a bit flakey. It works in MS Query Analyzer, which, according to someone here, isn't using ODBC. Query analyser version: 7.00.623 SQL Server version 7.00.961, SP3; on NT 4, SP6 Cheers, -- dave thorn | [EMAIL PROTECTED]
Re: :ODBC bug?
Dave Thorn wrote: On Tue, Oct 22, 2002 at 09:54:03AM -0400, Jeff Urlwin wrote: [SQLFetch()] The first time through, we see (snipped from trace below) fetch col#4 SC_VALUE datalen=0 displ=256 fetch col#4 SC_VALUE datalen=19 displ=256 The second time through, I see: fetch col#4 SC_VALUE datalen=19 displ=256 fetch col#4 SC_VALUE datalen=19 displ=256 I *think* that's a driver problem. You indicated it works in another environment (query analyzer). Is that using the ODBC driver too? How about incomplete sentence? How about...? What driver/version are you using? What database? I'd like to see if I can reproduce here. This is where my knowledge goes a bit flakey. It works in MS Query Analyzer, which, according to someone here, isn't using ODBC. According to me :-) newer versions (7+) of Query Analyzer use ODBC, older versions might use DB Library You might try osql (O for ODBC) from the command prompt. Query analyser version: 7.00.623 SQL Server version 7.00.961, SP3; on NT 4, SP6 try: ppm query DBD-ODBC This is what I get: E:\Programs\Perlppm query DBD-ODBC DBD-ODBC [0.45_18] ODBC driver for the DBI module. Can you try to run your code on a different machine where it's possible to try 0.45_18?
Re: Access to Clipper *.DBV files
On 22 Oct 2002 at 9:15, Phil DiFalco wrote: Is their an interface to access Clipper Db *.DBV files? (*.DBV files are a variation of the *.DBT files that hold memo fields). I'm not aware of one but then again I don't use Clipper. If DBV files are a close equivalent to DBT files, I'd suggest checking out Jan Pazdziora's DBD::XBase module which includes good support for memo fields. He may even have some knowledge of Clipper DBT files. Good luck, William -- Lead Developer Knowmad Services Inc. || Internet Applications Database Integration http://www.knowmad.com
Re: :ODBC bug?
On Tue, Oct 22, 2002 at 10:24:39AM -0400, Jeff Urlwin wrote: Sorry -- brain problem, this morning. I was going to say, how about MS Query (not analyzer, but the query that comes with MS office which just queries the db), then I looked further to see you were on Linuxcan you give us a little more about your configuration? I can guess that you may be using one of the following: - 1) using DBI, DBD::Proxy or and MS ODBC drivers on the SQL Server side. - 2) using DBD::ODBC and EasySoft OOB or some other bridge - 3) using DBD::ODBC and something like FreeTDS It appears to be DBD::ODBC and FreeTDS. At least, the DSN name shown in ODBCConfig which matches my code says FreeTDS alongside. (TBPH it's been a long time since i set it all up and am a bit hazy on what i did.) What version of the MDAC is on your SQLServer itself? Since nearly all of these are dependant upon the ODBC driver on the server (or wherever the bridge is installed). I don't know. The server isn't one we can get at properly from here. According to someone here it's probably either 2.5 or 2.7, but it's all a bit vague, sorry. If you can update that to MDAC 2.7, that would be great. There were some issues with 2.6 and below. I don't think we could get any changes made without going through a laborious process. It's used by many groups of people. -- dave thorn | [EMAIL PROTECTED]
Re: Strange problem with
In the while statement you're missing the $website between $email2
$available_hours1.
Hope this helps.
George Mayne
[EMAIL PROTECTED]
*** REPLY SEPARATOR ***
On 10/21/2002 at 8:59 PM Brad Smith wrote:
Using an access database with DBI and DBD-ODBC, I am having the
strangest problem, and I can't seem to solve it.
The table has three fields, named available_hours1, available_hours2
and available_hours3. When I retrieve the data, the contents of
available_hours1 shows up when I say to print available_hours2.
Below is my code, which I have combed over and over. I can't find the
problem. I was hoping better eyes and wiser minds could figure out
where my problem may be:
my $dbh = DBI-connect('dbi:ODBC:greene_resource_providers') || die
DBI::errstr;
my $sth = $dbh-prepare(SELECT name, nickname, address1,
address2, address3, city, state,
zipcode, telephone1, telephone2, telephone3, private,
tollfree1, tollfree2, hotline1, hotline2, fax,
contact_name, contact_title, director_name, director_title, email1,
email2,
website, available_hours1, available_hours2, available_hours3,
eligibility1, eligibility2, eligibility3, eligibility4, eligibility5,
eligibility6, service1, service2, service3, service4,
service5, service6, service7, service8, service9,
service10, service11, service12, service13, service14,
service15, service16, service17, service18, service19,
service20, service21, service22, service23, service24,
service25, fee_structure1, fee_structure2, appointment,
required_documentation,
funding_source, service_code, legal_code, speaker, volunteer_use,
volunteer_need, volunteer_description, handicap_code, last_update,
tax1,
tax2,
tax3, tax4, tax5, tax6, tax7, tax8, tax9, tax10, tax11,
tax12,
tax13, tax14, tax15, tax16, tax17, tax8, tax19, tax20,
tax21,
tax22, tax23, tax24, tax25
FROM resource_providers
WHERE sid=?
);
$sth-execute($in{'sid'});
while (($name, $nickname, $address1, $address2, $address3, $city,
$state,
$zipcode, $telephone1, $telephone2, $telephone3,
$private,
$tollfree1, $tollfree2, $hotline1, $hotline2, $fax,
$contact_name, $contact_title, $director_name,
$director_title, $email1,
$email2, $available_hours1, $available_hours2,
$available_hours3,
$eligibility1, $eligibility2, $eligibility3, $eligibility4,
$eligibility5,
$eligibility6, $service1, $service2, $service3, $service4,
$service5, $service6, $service7, $service8, $service9,
$service10, $service11, $service12, $service13,
$service14,
$service15, $service16, $service17, $service18,
$service19,
$service20, $service21, $service22, $service23,
$service24,
$service25, $fee_structure1, $fee_structure2,
$appointment, $required_documentation,
$funding_source, $service_code, $legal_code,
$speaker, $volunteer_use,
$volunteer_need, $volunteer_description,
$handicap_code, $last_update, $tax1,
$tax2, $tax3, $tax4, $tax5, $tax6, $tax7, $tax8, $tax9,
$tax10, $tax11,
$tax12, $tax13, $tax14, $tax15, $tax16, $tax17, $tax18,
$tax19, $tax20,
$tax21, $tax22, $tax23, $tax24, $tax25)=$sth-
fetchrow_array) { code truncated here
Brad Smith
RE: :ODBC bug?
On Tue, Oct 22, 2002 at 10:24:39AM -0400, Jeff Urlwin wrote: Sorry -- brain problem, this morning. I was going to say, how about MS Query (not analyzer, but the query that comes with MS office which just queries the db), then I looked further to see you were on Linuxcan you give us a little more about your configuration? I can guess that you may be using one of the following: - 1) using DBI, DBD::Proxy or and MS ODBC drivers on the SQL Server side. - 2) using DBD::ODBC and EasySoft OOB or some other bridge - 3) using DBD::ODBC and something like FreeTDS It appears to be DBD::ODBC and FreeTDS. At least, the DSN name shown in ODBCConfig which matches my code says FreeTDS alongside. (TBPH it's been a long time since i set it all up and am a bit hazy on what i did.) If you are using FreeTDS, then it's irrelavent what version of the ODBC driver is running on the server side. It's probably just a client side problem. What I suggest is: - determining which (if any) ODBC driver manager (i.e. unixODBC) you are using. Try using isql or the driver manager's sql interface to see if you have the same problem. It's likely you do. In which case it's FreeTDS or the driver manager causing it. If you built DBD::ODBC directly against FreeTDS, then, check which version. I ran 0.53 and had issues. I haven't tested with 0.60 yet, but there have evidently been major improvements to the ODBC portion. That said, you are in a grey area for me as I don't have a lot of time to muck with that configuration. You might also try DBD::Sybase using FreeTDS...as I suspect that DBD::Sybase using FreeTDS directly may be more stable than DBD::ODBC using FreeTDS's ODBC library, then FreeTDS...just from my experimenting with FreeTDS 0.53... Regards, Jeff
Best way to break out SQL statements
Hello All,
I've spent quite a bit of time looking through all the messages at
http://archive.develooper.com/dbi-users;perl.org/
And have found some information, but nothing exactly what I was looking for,
so I thought I would try the list.
My question is 2 fold..
What is the best method of getting all SQL statements out of a script and
into a central location/file/module etc.
What is the best method of dealing with a situation where you want to have
different SQL based on a specific incoming param (such as a CGI param).
I came across a module on cpan called SQL::Catalog that can do what I'm
looking for but this results in putting the SQL statements in the DB which
results in 2 calls to the DB for every statement and I don't want to do
that.
The only way I have gotten this to work is storing my SQL within 2 hash of
hashes within a package and then based on a incoming CGI param I make
whichever statement hash I want the default..
For example, here is a snippet of the package that has the SQL statements:
%sql_statements_standard = (
display_search = {
select_countyid =
Select CountyId, County from County where StateId
= ?,
},
display_user_reg= {
select_all_from_user= Select * from
User where username = ?,
},
%sql_statements_rental = (
display_search = {
select_countyid =
rentalsql,
},
display_user_reg= {
select_all_from_user= rentalsql,
},
I'm breaking it up as $hash_type{sub in script}{sql statement}
Then at the start of my script I get all the SQL I want, for example:
if ($form_data{'sec_type'} eq 'rental') {
%sql_statements = %sql_statements_rental;
} else {
%sql_statements = %sql_statements_standard;
}
Then I just call $sql_statements{sub routine}{sql} and I don't have to have
a bunch of IF statements nor do I have to have a 2nd script just to take
care of the rental condition.
Although this does work, it doesn't seem to be the best solution, for
example I can't have something like:
order by Priority desc, $sort limit $recno, $entries_per_page; at the end
of the statement within the hash, it doesn't work (I have to add it within
my script, like my $sql_st =
$sql_statements{'simple_search_database'}{select_long_lat_listing} . order
by Priority desc, $sort limit $recno, $entries_per_page;)
Is there a better way of doing this? I've been reading that mailing list and
checking the groups on google but coming up empty.
Thanks In Advance!!
-Chris
dbd-oracle and oracle library - dynamic loading question
Here's a hopefully simple question, but it's driving me crazy... setup is HPUX11.11, perl 5.8.0, dbi-1.28, dbd-oracle-1.12. perl compiles and works fine. so does dbi. dbd-oracle compiles ok, too. but here is the problem... in order to get the dbd-oracle to pass its tests, I must set the following shell variables: LD_RUN_PATH=/opt/oracle/product/8.1.7/lib SHLIB_PATH=/opt/oracle/product/8.1.7/lib (actually, I set them *before* I make dbd-oracle) after I make install the dbd, everything works fine *if* I set the SHLIB_PATH variable before invoking the perl script. However, if I *don't* set SHLIB_PATH, I get the error: install_driver(Oracle) failed: Can't load '/opt/perl/lib/site_perl/5.8.0/PA-RISC2.0/auto/DBD/Oracle/Oracle.sl' for module DBD::Oracle: No such file or directory at /opt/perl/lib/5.8.0/PA-RISC2.0/DynaLoader.pm line 229. So my question really is, should I *have* to set SHLIB_PATH in the shell before running perl scripts that use dbd-oracle, or is there a way to force it to know to use the library at /opt/oracle/product/8.1.7/lib ? (Obviously, this is a shared-library kind of issue, but I'm not a real programmer, so I don't know the details of how this works - please be nice !) BTW, if this were just a bunch of perl scripts, I wouldn't have a problem telling the users to set the SHLIB_PATH variable before using them - however, the scripts are actually cgi scripts, intended to be invoked from a web server, so before I go and kludge up the Apache config to include this variable, I want to make sure that I really *should* be doing that... Thanks ! John -- # # John Foley - LAN Manager #Maildrop: IL02 - RM 0206 # # Schaumburg BTS CoE# SMTP E-MAIL: [EMAIL PROTECTED] # # Motorola # X400 E-MAIL: [EMAIL PROTECTED] # # 1301 East Algonquin Rd. # Phone: (847) 576-8719 # # Schaumburg, Illinois 60196 (USA) # Fax: (847) 538-3765 # # (this email sent using Netscape on NT)
FW: Best way to break out SQL statements
Sorry, meant to add that I'm using Perl5, DBI, Linux and mySql 3.23
-Original Message-
Sent: Tuesday, October 22, 2002 10:51 AM
To: [EMAIL PROTECTED]
Subject: Best way to break out SQL statements
Hello All,
I've spent quite a bit of time looking through all the messages at
http://archive.develooper.com/dbi-users;perl.org/
And have found some information, but nothing exactly what I was looking for,
so I thought I would try the list.
My question is 2 fold..
What is the best method of getting all SQL statements out of a script and
into a central location/file/module etc.
What is the best method of dealing with a situation where you want to have
different SQL based on a specific incoming param (such as a CGI param).
I came across a module on cpan called SQL::Catalog that can do what I'm
looking for but this results in putting the SQL statements in the DB which
results in 2 calls to the DB for every statement and I don't want to do
that.
The only way I have gotten this to work is storing my SQL within 2 hash of
hashes within a package and then based on a incoming CGI param I make
whichever statement hash I want the default..
For example, here is a snippet of the package that has the SQL statements:
%sql_statements_standard = (
display_search = {
select_countyid =
Select CountyId, County from County where StateId
= ?,
},
display_user_reg= {
select_all_from_user= Select * from
User where username = ?,
},
%sql_statements_rental = (
display_search = {
select_countyid =
rentalsql,
},
display_user_reg= {
select_all_from_user= rentalsql,
},
I'm breaking it up as $hash_type{sub in script}{sql statement}
Then at the start of my script I get all the SQL I want, for example:
if ($form_data{'sec_type'} eq 'rental') {
%sql_statements = %sql_statements_rental;
} else {
%sql_statements = %sql_statements_standard;
}
Then I just call $sql_statements{sub routine}{sql} and I don't have to have
a bunch of IF statements nor do I have to have a 2nd script just to take
care of the rental condition.
Although this does work, it doesn't seem to be the best solution, for
example I can't have something like:
order by Priority desc, $sort limit $recno, $entries_per_page; at the end
of the statement within the hash, it doesn't work (I have to add it within
my script, like my $sql_st =
$sql_statements{'simple_search_database'}{select_long_lat_listing} . order
by Priority desc, $sort limit $recno, $entries_per_page;)
Is there a better way of doing this? I've been reading that mailing list and
checking the groups on google but coming up empty.
Thanks In Advance!!
-Chris
Re: Best way to break out SQL statements
On Tue, Oct 22, 2002 at 10:51:20AM -0400, Chris Faust wrote:
What is the best method of getting all SQL statements out of a
script and into a central location/file/module etc.
There is SQL::Snippet, available on CPAN. I have never used it
myself.
I have written extensions to XML::Generator::DBI which can parse SQL
stored as XML. This allows you to, among other things, define
parameters in the SQL which can then be used to generate HTML forms
for the user. See http://xsql.sourceforge.net/. It is under active
development, but usable in its present state.
What is the best method of dealing with a situation where you want
to have different SQL based on a specific incoming param (such as a
CGI param).
The only way I have gotten this to work is storing my SQL within 2 hash of
hashes within a package and then based on a incoming CGI param I make
whichever statement hash I want the default..
Then I just call $sql_statements{sub routine}{sql} and I don't have to have
a bunch of IF statements nor do I have to have a 2nd script just to take
care of the rental condition.
This seems like a nice solution.
Although this does work, it doesn't seem to be the best solution, for
example I can't have something like:
order by Priority desc, $sort limit $recno, $entries_per_page; at the end
of the statement within the hash, it doesn't work (I have to add it within
my script, like my $sql_st =
$sql_statements{'simple_search_database'}{select_long_lat_listing} . order
by Priority desc, $sort limit $recno, $entries_per_page;)
You could do this with your method by putting the variables you do not
want interpolated when you set the hash values in single quotes. Then
eval the sql before you use it to interpolate the values. Of course,
for variable locations that allow placeholders, you should use them.
dd
--
David Dooling
Re: :ODBC bug?
On Tue, Oct 22, 2002 at 10:54:18AM -0400, Jeff Urlwin wrote: [FreeTDS] If you are using FreeTDS, then it's irrelavent what version of the ODBC driver is running on the server side. It's probably just a client side problem. What I suggest is: - determining which (if any) ODBC driver manager (i.e. unixODBC) you are using. Try using isql or the driver manager's sql interface to see if you have the same problem. It's likely you do. I don't, I'm afraid. Horribly messy output from isql follows: query = SELECT sc_item_code, sc_value FROM strand_content WHERE sc_group_code = 'MUSIC_CONTENT' AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E' AND sc_index = 5 AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') ) +-++ | | | +-++ | DURATION| | | PERFORMER | RICHARD G. MITCHELL | +-++ 2 rows affected SQL and then: query = SELECT sc_item_code, sc_value FROM strand_content WHERE sc_group_code = 'MUSIC_CONTENT' AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E' AND sc_index = 5 AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') ) ORDER BY sc_item_code DESC +-++ | | | +-++ | PERFORMER | RICHARD G. MITCHELL | | DURATION| | +-++ 2 rows affected SQL Cheers, -- dave thorn | [EMAIL PROTECTED]
Re: Strange problem with
On Tue, 22 Oct 2002 07:50:06 -0500 George Mayne [EMAIL PROTECTED] wrote:
In the while statement you're missing the $website between $email2
$available_hours1.
With as many columns as you have, it would be easier and _much_ more
efficient to retreive the values with $sth-bind_columns(). It would have
also complained if you gave the wrong number of columns.
Short example (untested):
$dbh - {RaiseError} = 1
my $sth = $dbh - prepare( ... );
$sth - bind_columns( \( $v1, $v2, $v3, ... ) );
$sth - execute;
while ( $sth - fetch ) {
# do something with $v1, $v2, $v3, ...
}
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.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.
RE: :ODBC bug?
Unfortunately, that's not readable. I only see the one row. Jeff On Tue, Oct 22, 2002 at 10:54:18AM -0400, Jeff Urlwin wrote: [FreeTDS] If you are using FreeTDS, then it's irrelavent what version of the ODBC driver is running on the server side. It's probably just a client side problem. What I suggest is: - determining which (if any) ODBC driver manager (i.e. unixODBC) you are using. Try using isql or the driver manager's sql interface to see if you have the same problem. It's likely you do. I don't, I'm afraid. Horribly messy output from isql follows: query = SELECT sc_item_code, sc_value FROM strand_content WHERE sc_group_code = 'MUSIC_CONTENT' AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E' AND sc_index = 5 AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') ) +-+--- -- ---+ | | | +-+--- -- ---+ | DURATION| | | PERFORMER | RICHARD G. MITCHELL | +-+--- -- ---+ 2 rows affected SQL and then: query = SELECT sc_item_code, sc_value FROM strand_content WHERE sc_group_code = 'MUSIC_CONTENT' AND str_uniq_id = '94F6C407-D567-11D6-A75A-00508BFCB09E' AND sc_index = 5 AND ( (SC_ITEM_CODE = 'DURATION') OR (SC_ITEM_CODE = 'PERFORMER') ) ORDER BY sc_item_code DESC +-+--- -- ---+ | | | +-+--- -- ---+ | PERFORMER | RICHARD G. MITCHELL | | DURATION| | +-+--- -- ---+ 2 rows affected SQL Cheers, -- dave thorn | [EMAIL PROTECTED]
Re: :ODBC bug?
On Tue, Oct 22, 2002 at 11:48:01AM -0400, Jeff Urlwin wrote: Unfortunately, that's not readable. I only see the one row. Well, it shows RICHARD G. MITCHELL for PERFORMER, and nothing for DURATION, using the same queries as posted in code earlier. -- dave thorn | [EMAIL PROTECTED]
Code review
Ok, I didn't know what list to send this to, since it incorporates a lots of different things, DBI, basic perl stuff, etc., so please excuse this intrusion on your list, if it isnt supposed to be there. If you know a better list for me to submit this to, please let me know. Basically, I am asking for your comments about this code I wrote. I am relatively new to perl, and this is one of my first major projects I worked on using perl, and would like feedback to things that I did wrong with relation to perl, ie error checking, better ways to do something using better perl syntax etc. Functionally, this program works, and is in use today, but I would to make perl a standard here, and getting more knowledge is a good thing, IMHO. If you are interested, read on, otherwise delete message here: This program will call a sqr program inside it, change the output file to html, and send this via email to a recipient. The code should be pretty well commented in respects to what it is doing. If there are any questions. feel free to ask. Chris Elead.pl Description: Perl program
RE: :ODBC bug?
Right, but I only see one row in the message, per query. Shouldn't there be two? Jeff -Original Message- From: Dave Thorn [mailto:davet;fysh.org] Sent: Tuesday, October 22, 2002 11:56 AM To: Jeff Urlwin Cc: [EMAIL PROTECTED] Subject: Re: :ODBC bug? On Tue, Oct 22, 2002 at 11:48:01AM -0400, Jeff Urlwin wrote: Unfortunately, that's not readable. I only see the one row. Well, it shows RICHARD G. MITCHELL for PERFORMER, and nothing for DURATION, using the same queries as posted in code earlier. -- dave thorn | [EMAIL PROTECTED]
Re: :ODBC bug?
Dave Thorn wrote: On Tue, Oct 22, 2002 at 11:48:01AM -0400, Jeff Urlwin wrote: Unfortunately, that's not readable. I only see the one row. Well, it shows RICHARD G. MITCHELL for PERFORMER, and nothing for DURATION, using the same queries as posted in code earlier. Just to warn you, isql doesn't check if the field is null, it just calls SQLGetData as a SQL_CHAR, and prints the char that comes back. -- Nick Gorham Easysoft Limited http://www.easysoft.com
Re: :ODBC bug?
On Tue, Oct 22, 2002 at 12:11:43PM -0400, Jeff Urlwin wrote: Right, but I only see one row in the message, per query. Shouldn't there be two? I think you're mis-parsing it. The DURATION and PERFORMER bits are in different rows to each other. | DURATION| | | PERFORMER | RICHARD G. MITCHELL two rows. I took the liberty of selecting less fields than with the previous queries to try and make it slightly more readable, sorry, should have mentioned that, hoped it'd show in the output. | PERFORMER | RICHARD G. MITCHELL | | DURATION| two rows (other query) -- dave thorn | [EMAIL PROTECTED]
DBD::Oracle and FreeBSD
I'm trying to get DBD::Oracle working on a FreeBSD machine. After
jumping through some hoops to install it, I get this error:
/usr/libexec/ld-elf.so.1:
/usr/local/perl-5.8.0/lib/site_perl/5.8.0/i386-freebsd/auto/DBD/Oracle
/Oracle.so: Undefined symbol OCIInitialize when trying to use a
script that uses DBD::Oracle.
---
#!/usr/bin/perl -w
use strict;
$ENV{ORACLE_HOME}='/oracle/OraHome1';
use DBI;
my($dbh) = DBI-connect(dbi:Oracle:host=$hostname;sid=$sid, $user,
$pass)
|| die COULD NOT CONNECT\n;
---
OCIInitialize *seems* to be in the Oracle.so file, but I'm not sure in
what capacity...
---
% strings
/usr/local/perl-5.8.0/lib/site_perl/5.8.0/i386-freebsd/auto/DBD/Oracle
/Oracle.so | grep OCIInitialize
OCIInitialize
OCIInitialize. Check ORACLE_HOME and NLS settings etc.
---
I've attached the %perl Makefile.PL output and the %gmake output
when I installed DBD::Oracle. Additionally, in order to get this
working (or maybe the reason it's not), I installed Oracle 9i Client
on a SUSE Linux machine, copied the /oracle directory (to get the
library and header files that I need) to my FreeBSD machine. All I
want to be able to do is connect the FreeBSD webserver to a remote
Oracle 9i server. If there is another way, please let me know. If I
need to patch the Makefile in anyway I could use some help. I am
concerned about this section in the perl_Makefile.PL output, even
though I get the Checking if your kit is complete...Looks good
message.
---
Warning: Oracle build rule discovery failed (256)
Oracle oci build prolog:
/oracle/OraHome1/rdbms/lib/env_rdbms.mk, line 2176: Missing
dependency operator
/oracle/OraHome1/rdbms/lib/env_rdbms.mk, line 2178: Need an
operator
/oracle/OraHome1/rdbms/lib/env_rdbms.mk, line 2180: Need an
operator
make: fatal errors encountered -- cannot continue
---
Or if you know how to get past the ld-elf.so.1 error, help would be
wonderful.
Thanks,
Matthew
root@dev /usr/local/src/DBD-Oracle-1.12 # gmake
cp Oracle.h blib/arch/auto/DBD/Oracle/Oracle.h
cp Oracle.pm blib/lib/DBD/Oracle.pm
cp dbdimp.h blib/arch/auto/DBD/Oracle/dbdimp.h
cp oraperl.ph blib/lib/oraperl.ph
cp ocitrace.h blib/arch/auto/DBD/Oracle/ocitrace.h
cp Oraperl.pm blib/lib/Oraperl.pm
cp mk.pm blib/arch/auto/DBD/Oracle/mk.pm
/usr/local/perl-5.8.0/bin/perl5.8.0 -p -e s/~DRIVER~/Oracle/g
/usr/local/perl-5.8.0/lib/site_perl/5.8.0/i386-freebsd/auto/DBI/Driver.xst Oracle.xsi
/usr/local/perl-5.8.0/bin/perl5.8.0 /usr/local/perl-5.8.0/lib/5.8.0/ExtUtils/xsubpp
-typemap /usr/local/perl-5.8.0/lib/5.8.0/ExtUtils/typemap Oracle.xs Oracle.xsc
mv Oracle.xsc Oracle.c
cc -c -I/oracle/OraHome1/rdbms/demo -I/oracle/OraHome1/rdbms/public
-I/oracle/OraHome1/plsql/public -I/oracle/OraHome1/network/public
-I/oracle/OraHome1/rdbms/demo -I/oracle/OraHome1/rdbms/demo
-I/usr/local/perl-5.8.0/lib/site_perl/5.8.0/i386-freebsd/auto/DBI -DHAS_FPSETMASK
-DHAS_FLOATINGPOINT_H -fno-strict-aliasing -O -DVERSION=\1.12\
-DXS_VERSION=\1.12\ -DPIC -fpic
-I/usr/local/perl-5.8.0/lib/5.8.0/i386-freebsd/CORE Oracle.c
Oracle.xs: In function `XS_DBD__Oracle__st_ora_fetch':
Oracle.xs:69: warning: passing arg 1 of `fprintf' from incompatible pointer type
Oracle.xs:79: warning: passing arg 1 of `fprintf' from incompatible pointer type
Oracle.xs:83: warning: passing arg 1 of `fprintf' from incompatible pointer type
Oracle.xs:87: warning: passing arg 1 of `fprintf' from incompatible pointer type
cc -c -I/oracle/OraHome1/rdbms/demo -I/oracle/OraHome1/rdbms/public
-I/oracle/OraHome1/plsql/public -I/oracle/OraHome1/network/public
-I/oracle/OraHome1/rdbms/demo -I/oracle/OraHome1/rdbms/demo
-I/usr/local/perl-5.8.0/lib/site_perl/5.8.0/i386-freebsd/auto/DBI -DHAS_FPSETMASK
-DHAS_FLOATINGPOINT_H -fno-strict-aliasing -O -DVERSION=\1.12\
-DXS_VERSION=\1.12\ -DPIC -fpic
-I/usr/local/perl-5.8.0/lib/5.8.0/i386-freebsd/CORE dbdimp.c
dbdimp.c: In function `dbd_fbh_dump':
dbdimp.c:85: warning: initialization from incompatible pointer type
dbdimp.c: In function `ora_db_login6':
dbdimp.c:206: warning: passing arg 1 of `fprintf' from incompatible pointer type
dbdimp.c:212: warning: passing arg 1 of `fprintf' from incompatible pointer type
dbdimp.c:220: warning: passing arg 1 of `fprintf' from incompatible pointer type
dbdimp.c:221: warning: passing arg 1 of `fprintf' from incompatible pointer type
dbdimp.c:222: warning: passing arg 1 of `fprintf' from incompatible pointer type
dbdimp.c:224: warning: passing arg 1 of `fprintf' from incompatible pointer type
dbdimp.c:227: warning: passing arg 1 of `fprintf' from incompatible pointer type
dbdimp.c:228: warning: passing arg 1 of `fprintf' from incompatible pointer type
dbdimp.c:229: warning: passing arg 1 of `fprintf' from incompatible pointer type
dbdimp.c:234: warning: passing arg 1 of `fprintf' from incompatible pointer type
dbdimp.c:236: warning: passing arg 1 of `fprintf' from incompatible pointer type
dbdimp.c:244: warning: passing arg 1 of `fprintf' from
Hiding the db password
I write/maintain web applications at my job, and several of them are in perl and use the perl dbi to connect to an oracle db. however, the passwords are displayed in the files and are accessible by many people outside of our group (since all the cgi files are on servers accessible by many people.) Is there a way to hide the passwords in a file so that perl can still connect to the db but now allow anyone but me and maybe my supervisor to see the password? Thanks. John Gedeon Proverbs 3:5 Trust in the Lord with all your heart and lean not on your own understanding;
RE: DBI, MSSQL 2000, and Unicode
Thamir, Try SQL_WLONGVARCHAR... I haven't tested much with Unicode, so there may be issues there. Cross-posted to [EMAIL PROTECTED] for more help. Regards, Jeff Greetings, I am parsing an XML file encoded in UTF-8 and want to insert parsed text into NVARCHAR field in a MSSQL 2000 database. The problem seems that DBD::ODBC driver inserts the text into the database in the wrong encoding. In the bind_param method, I've bound the placeholder to a DBI::SQL_LONGVARCHAR but it didn't work. Any clues? Regards, Thamir ___ Perl-Win32-Database mailing list [EMAIL PROTECTED] To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
Re: Hiding the db password
Hi ( 02.10.22 11:02 -0700 ) John Gedeon: Is there a way to hide the passwords in a file so that perl can still connect to the db but now allow anyone but me and maybe my supervisor to see the password? Not really cleanly. If you're on a UN*X system, you can use file permissions to minimize exposure. The problem is that the webserver user needs to read the password, so anyone who can run as that user can read the password. You could have in in an environment variable that gets populated from an encrypted string when the webserver starts. This means that it's in plaintext in memory and the key for the decryption either needs to be entered by an operator or somewhere on the filesystem [which puts you in the same dilemma you are in now (more or less)]. Trust in the Lord with all your heart and lean not on your own understanding; Well, I prefer to RTFM. -- ..--- ...
RE: Hiding the db password
One thing that's possible here is to put passwords into an encrypted Perl module. Perl supports compile time filtering and I've seen people use this to hide passwords or even to hide their entire set of source. But I've never done it myself (yet). The place to look if you're interested in that is in the 'perlfilter' docs that come with your Perl distribution. The idea I had was to create a Perl module just for passwords that need encrypting and filter just that one module. I was also considering whether I could somehow identify encrypted values anywhere and just filter those ... it seems possible by writing a filter that filters only when it sees certain source code constructs and passes the rest through untouched, but I'm not sure. If you do this, I'd be interested in the results. I tend to stress the 'Lazy' part of Perl's three programmer virtues: Laziness, Hubris, and Impatience. 8-) Steve Sapovits GSI Commerce Work Email: [EMAIL PROTECTED] Home Email: [EMAIL PROTECTED] Work Phone: 610-491-7087 Cell: 610-574-7706 Pager: 877-239-4003 -Original Message- From: John Saylor [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, October 22, 2002 2:09 PM To: John Gedeon Cc: [EMAIL PROTECTED] Subject: Re: Hiding the db password Hi ( 02.10.22 11:02 -0700 ) John Gedeon: Is there a way to hide the passwords in a file so that perl can still connect to the db but now allow anyone but me and maybe my supervisor to see the password? Not really cleanly. If you're on a UN*X system, you can use file permissions to minimize exposure. The problem is that the webserver user needs to read the password, so anyone who can run as that user can read the password. You could have in in an environment variable that gets populated from an encrypted string when the webserver starts. This means that it's in plaintext in memory and the key for the decryption either needs to be entered by an operator or somewhere on the filesystem [which puts you in the same dilemma you are in now (more or less)]. Trust in the Lord with all your heart and lean not on your own understanding; Well, I prefer to RTFM. -- ..--- ...
Re: Hiding the db password
( 02.10.22 11:02 -0700 ) John Gedeon: Is there a way to hide the passwords in a file so that perl can still connect to the db but now allow anyone but me and maybe my supervisor to see the password? Not really cleanly. If you're on a UN*X system, you can use file permissions to minimize exposure. The problem is that the webserver user needs to read the password, so anyone who can run as that user can read the password. You could have in in an environment variable that gets populated from an encrypted string when the webserver starts. This means that it's in plaintext in memory and the key for the decryption either needs to be entered by an operator or somewhere on the filesystem [which puts you in the same dilemma you are in now (more or less)]. I agree. In a mod_perl environment, your best bet is to write some small piece of perl code in your httpd.conf: Perl print STDOUT Enter database password: ; $DATABASE::PASSWORD = STDIN; $DATABASE::PASSWORD =~ s/\n//og; /Perl and in your perl script, just use $DATABASE::PASSWORD as the password. The only thing is that on restart of the webserver you always have to pass in the database password. H
Re: Hiding the db password
Assuming you create a Perl module outside of the webserver's
document root, the tricky thing is that for cgi scripts, the
user that the web server is running as needs at least read
access to that file that contains the DB passwords.
What platform are you on?
Assuming a *nix platform, I suppose you could create a group
(just_us) that would consist of you, your boss, and the user
the webserver runs under, and then
/var/our_perl_modules
-
DB.pm - contains the DB password $my_db_pw
have DB.pm file owner john, group just_us, and have
permissions 750 (read, write, execute for owner, and
read, execute for group, and nothing
for world)
/var/www/cgi-bin/my_script.cgi
--
use lib '/var/our_perl_modules';
use DB;
...
my $dbh = DBI-connect($dsn,
$DB::my_db_user,
$DB::my_db_pw,
{ RaiseError = 1, PrintError = 0, AutoCommit = 1 }
);
Note: This is all off the top of my head - it is untested, and
it wouldn't surprise me if it's not syntactically correct, but
hopefully it shows you how it might be done. Read up on Perl
modules by doing
perldoc perlmod
at a command prompt. Doing 'perldoc perl' I found these perldocs
related to modules:
perlmod Perl modules: how they work
perlmodlib Perl modules: how to write and use
perlmodstylePerl modules: how to write modules with style
perlmodinstall Perl modules: how to install from CPAN
perlnewmod Perl modules: preparing a new module for distribution
But if there are other administrators(with root access)
on the machine, they'll be able to get at your DB.pm by
just su'ing to one of the user acccounts in the group.
I'm no security expert, and hopefully I haven't mis-stated
anything(please correct me if I'm wrong), but maybe that
gives you an idea or two.
One other thing - if your webserver is Apache, you might
want to check out suexec. That allows you to locate cgi
scripts underneath a *regular* users home directory, and run
those scripts as that regular user(they won't run as the
webserver user, they will actually run as the user whose
home directory it is).
HTH.
--
Hardy Merrill
Senior Software Engineer
Red Hat, Inc.
John Gedeon [[EMAIL PROTECTED]] wrote:
I write/maintain web applications at my job, and several of them are in
perl and use the perl dbi to connect to an oracle db. however, the
passwords are displayed in the files and are accessible by many people
outside of our group (since all the cgi files are on servers accessible by
many people.) Is there a way to hide the passwords in a file so that perl
can still connect to the db but now allow anyone but me and maybe my
supervisor to see the password?
Thanks.
John Gedeon
Proverbs 3:5 Trust in the Lord with all your heart and lean not on
your own understanding;
Re: Hiding the db password
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 22 October 2002 21:41, Hardy Merrill wrote: Assuming you create a Perl module outside of the webserver's document root, the tricky thing is that for cgi scripts, the user that the web server is running as needs at least read access to that file that contains the DB passwords. This solution works as long as all of the programmers are trusted users and only non-programmers are untrusted. Creating a script which prints out the DB password would be quite easy. But if the programmers are not a concern, then there is no problem with the filesystem rights management. Another solution (as Henri Asseily pointed out) would be asking the password each time the Apache configuration changes, someone unplugs the power, frustrated cracker forkbombs the system... Not a bad solution on a stable environment, though. SSL uses the same method. The simplest method of all would be to eliminate unauthorized access using the database management systems authorization schemas. For example, you could allow access to the database for only selected Unix users. This is done using the access control methods offered by the DBMS. If no-one can su(1) to www-data (or some other user Apache runs as), only root, www-data and other selected users can connect to the database. No passwords needed. -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE9taH3nksV4Ys/z5gRAvk4AJ9CrmnZpsBIw4A1RadD16WhnKQ7AgCgqIMF 7Y/JQrOfc6XYq3Hry3T32A8= =mwmA -END PGP SIGNATURE-
Sybase nsql bug
Hello Michael,
I believe this is a bug with the Sybase 0.94 nsql call through func. I
messed around with Sybase.pm and I believe the eval and catch between lines
290 and 340 is causing the problem. When you trap the die to check if it is
a deadlock error, you don't have an 'else' of what to do if it is not a
deadlock problem. I worked on a patch for a little while, but I got
confused on the interactions between eval, RaiseError and syb_err_handler.
If this turns out to really be a bug, I think I could patch it with a little
guidance. But for now I think the best solution for me would be to remove
the deadlocking stuff from my local copy of DBD::Sybase.
Regards,
Mitch
#!/usr/local/bin/perl5
use strict;
use warnings;
use DBI;
my $dbh = DBI-connect(
'dbi:Sybase:server=XXX',
'XXX',
'XXX',
{
RaiseError = 1 ,
syb_err_handler = sub {
print STDERR Sybase message: , pop;
return 1
}
}
) or die 'cannot connect';
print STDERR Sybase version: $dbh-{syb_oc_version}\n;
my $sql = 'select id, type, badcolumn from master..sysobjects';
my $h = $dbh-func($sql, 'HASH', 'nsql');
print STDERR It didn't die!\n;
===
opshintweb1$ ./test.pl
Sybase version: Sybase Client-Library/11.1.1/P-EBF7662/sun_svr4/SPARC
Solaris 2.5.1/1/OPT/Thu Nov 20 19:19:42 1997
Sybase message: Invalid column name 'badcolumn'.
no statement executing at
/usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris/DBD/Sybase.pm line 293.
It didn't die!
===
Mitch Helle-Morrissey, Software Engineer
Reuters Information Technology, Inc.
Re: Hiding the db password
Juha-Mikko Ahonen [[EMAIL PROTECTED]] wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tuesday 22 October 2002 21:41, Hardy Merrill wrote:
Assuming you create a Perl module outside of the webserver's
document root, the tricky thing is that for cgi scripts, the
user that the web server is running as needs at least read
access to that file that contains the DB passwords.
This solution works as long as all of the programmers are trusted
users and only non-programmers are untrusted. Creating a script which
prints out the DB password would be quite easy. But if the programmers
are not a concern, then there is no problem with the filesystem rights
management.
Better still would be to, instead of just placing the passwords
in a separate module, to place the entire connect into its own
subroutine in that separate module. That way, the only access
to that password info would be through the also encapsulated
connect subroutine - so the only exposed call is to the connect
subroutine:
/var/my_perl_modules
DB.pm - contains subroutine my_connect, which either
returns a good $dbh, or undef(if unsuccessful)
/var/www/cgi-bin/my_script.cgi
--
use lib '/var/my_perl_modules';
use DB;
my $dbh = DB::my_connect();
if (!$dbh) {
...bad error...do something drastic
}
No password info is exposed to scripts that use the connect. Can
you see a way in this scenario that a non-root programmer could
easily print the password info?
Another solution (as Henri Asseily pointed out) would be asking the
password each time the Apache configuration changes, someone unplugs
the power, frustrated cracker forkbombs the system... Not a bad
solution on a stable environment, though. SSL uses the same method.
The simplest method of all would be to eliminate unauthorized access
using the database management systems authorization schemas. For
example, you could allow access to the database for only selected Unix
users. This is done using the access control methods offered by the
DBMS.
If no-one can su(1) to www-data (or some other user Apache runs as),
only root, www-data and other selected users can connect to the
database. No passwords needed.
I've done this before with the Postgres DB - using Postgres ident
authentication, a user only gets access to a database if that
user is both a system user(has a regular system user account),
and a postgres user, and that user successfully logs in to his
regular system account - then he is granted access(this is a very
simplistic view). Couple that with suexec and you have pretty
good privacy(no pun intended) for your passwords.
--
Hardy Merrill
Senior Software Engineer
Red Hat, Inc.
RE: Hiding the db password
In this case, local users still have access to the DB.pm and it has the
user/password info, isn't it?
David Kuo
-Original Message-
From: Hardy Merrill [mailto:hmerrill;redhat.com]
Sent: Tuesday, October 22, 2002 3:35 PM
To: Juha-Mikko Ahonen
Cc: Hardy Merrill; John Gedeon; [EMAIL PROTECTED]
Subject: Re: Hiding the db password
Juha-Mikko Ahonen [[EMAIL PROTECTED]] wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tuesday 22 October 2002 21:41, Hardy Merrill wrote:
Assuming you create a Perl module outside of the webserver's
document root, the tricky thing is that for cgi scripts, the
user that the web server is running as needs at least read
access to that file that contains the DB passwords.
This solution works as long as all of the programmers are trusted
users and only non-programmers are untrusted. Creating a script which
prints out the DB password would be quite easy. But if the programmers
are not a concern, then there is no problem with the filesystem rights
management.
Better still would be to, instead of just placing the passwords
in a separate module, to place the entire connect into its own
subroutine in that separate module. That way, the only access
to that password info would be through the also encapsulated
connect subroutine - so the only exposed call is to the connect
subroutine:
/var/my_perl_modules
DB.pm - contains subroutine my_connect, which either
returns a good $dbh, or undef(if unsuccessful)
/var/www/cgi-bin/my_script.cgi
--
use lib '/var/my_perl_modules';
use DB;
my $dbh = DB::my_connect();
if (!$dbh) {
...bad error...do something drastic
}
No password info is exposed to scripts that use the connect. Can
you see a way in this scenario that a non-root programmer could
easily print the password info?
Another solution (as Henri Asseily pointed out) would be asking the
password each time the Apache configuration changes, someone unplugs
the power, frustrated cracker forkbombs the system... Not a bad
solution on a stable environment, though. SSL uses the same method.
The simplest method of all would be to eliminate unauthorized access
using the database management systems authorization schemas. For
example, you could allow access to the database for only selected Unix
users. This is done using the access control methods offered by the
DBMS.
If no-one can su(1) to www-data (or some other user Apache runs as),
only root, www-data and other selected users can connect to the
database. No passwords needed.
I've done this before with the Postgres DB - using Postgres ident
authentication, a user only gets access to a database if that
user is both a system user(has a regular system user account),
and a postgres user, and that user successfully logs in to his
regular system account - then he is granted access(this is a very
simplistic view). Couple that with suexec and you have pretty
good privacy(no pun intended) for your passwords.
--
Hardy Merrill
Senior Software Engineer
Red Hat, Inc.
Threading issue
At a client site we just installed DBD/dbi. Upon starting up our product that uses this method to connect with the database we received this error: /usr/lib/dld.sl: Can't shl_load() a library containing Thread Local Storage: /usr/lib/libcl.2 /usr/lib/dld.sl: Exec format error install_driver(Oracle) failed: Can't load '/opt/perl5/lib/site_perl/5.6.1/PA-RISC1.1/auto/DBD/Oracle/ Oracle.sl' for module DBD::Oracle: Exec format error at /opt/perl5/lib/5.6.1/PA-RISC1.1/DynaLoader.pm line 206. at (eval 6) line 3 Compilation failed in require at (eval 6) line 3. Perhaps a required shared library or dll isn't installed where expected at cdm_be line 3817 This file is at the location asked for. Any insights? -Bruce ___ CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
RE: Hiding the db password
Thanks for the tips guys. I will post my solution if any when I get one. but it seems like someone will always be able to see the passwords... John Proverbs 3:5 Trust in the Lord with all your heart and lean not on your own understanding;
Problem in installing perl modules DBI 1.13 and DBD::Oracle
Hi, I have a problem while installing DBI1.13 and DBD::Oracle modules linking with Perl5.00503 which is existing as installed already. I supplied PREFIX=/home/sched/opwsched/devtools as the dir_path where in I am interested to install DBI and DBD. But always I am getting permission problem as it is pointing to /usr/local/lib/perl5 where I do not have permission to open a dir. I have Unix user ID (SOLARIS 2.7) but not ROOT. I enclosed perl -V output also. I attached the error listing on execution of the commands. 1) perl Makefile.PL PREFIX= /home/sched/opwsched/devtools 2) make 3) make test 4)perl -V. Is there a way to install these modules in the dir_path I specified in PREFIX, without ROOT permissions. Please suggest me the solution. Does it require to install DBI and DBD in /usr/local/lib ... always ? where PERL5 installed already. Thanks, Ram. ATT Orlando, FL ERROR LISTING: cfoifdv4/home/sched/opwsched/devtools/DBI1.13 CASTDEV perl Makefile.PL PREFIX= /home/sched/opwsched/devtools *** 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 Writing Makefile for DBI Remember to actually *read* the README file! Use 'make' to build the software (dmake or nmake on Windows). Then 'make test' to execute self tests. Then 'make install' to install the DBI and then delete this working directory before unpacking and building any DBD::* drivers. cfoifdv4/home/sched/opwsched/devtools/DBI1.13 CASTDEV make mkdir blib mkdir blib/lib mkdir blib/arch mkdir blib/arch/auto mkdir blib/arch/auto/DBI mkdir blib/lib/auto mkdir blib/lib/auto/DBI mkdir blib/man1 mkdir blib/man3 cp FAQ.pm blib/lib/FAQ.pm cp Shell.pm blib/lib/Shell.pm cp ProxyServer.pm blib/lib/ProxyServer.pm cp ExampleP.pm blib/lib/ExampleP.pm cp Proxy.pm blib/lib/Proxy.pm cp DBIXS.h blib/arch/auto/DBI/DBIXS.h cp DBIODBC.pm blib/lib/DBIODBC.pm cp dbd_xsh.h blib/arch/auto/DBI/dbd_xsh.h cp Format.pm blib/lib/Format.pm cp dbi_sql.h blib/arch/auto/DBI/dbi_sql.h cp DBD.pm blib/lib/DBD.pm cp NullP.pm blib/lib/NullP.pm cp Sponge.pm blib/lib/Sponge.pm cp Driver.xst blib/arch/auto/DBI/Driver.xst cp DBI.pm blib/lib/DBI.pm cp W32ODBC.pm blib/lib/W32ODBC.pm cp ADO.pm blib/lib/ADO.pm /usr/local/bin/perl -I/usr/local/lib/perl5/5.00503/sun4-solaris -I/usr/local/lib /perl5/5.00503 /usr/local/lib/perl5/5.00503/ExtUtils/xsubpp -typemap /usr/local /lib/perl5/5.00503/ExtUtils/typemap DBI.xs xstmp.c mv xstmp.c DBI.c gcc -c -I/usr/local/include -O -DVERSION=\1.13\ -DXS_VERSION=\1.13\ -f PIC -I/usr/local/lib/perl5/5.00503/sun4-solaris/CORE -Wall -Wno-comment -DDBI_NO _THREADS DBI.c Running Mkbootstrap for DBI () chmod 644 DBI.bs LD_RUN_PATH= gcc -o blib/arch/auto/DBI/DBI.so -G -L/usr/local/lib DBI.o 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.00503/sun4-s olaris -I/usr/local/lib/perl5/5.00503 dbiproxy.PL dbiproxy Extracted dbiproxy from dbiproxy.PL with variable substitutions. mkdir blib/script cp dbiproxy blib/script/dbiproxy /usr/local/bin/perl -I/usr/local/lib/perl5/5.00503/sun4-solaris -I/usr/local/lib /perl5/5.00503 -MExtUtils::MakeMaker -e MY-fixin(shift) blib/script/dbiproxy /usr/local/bin/perl -Iblib/arch -Iblib/lib -I/usr/local/lib/perl5/5.00503/sun4-s olaris -I/usr/local/lib/perl5/5.00503 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.00503/sun4-solaris -I/usr/local/lib /perl5/5.00503 -MExtUtils::MakeMaker -e MY-fixin(shift) blib/script/dbish Manifying blib/man1/dbiproxy.1 Manifying blib/man3/DBIODBC.3 Manifying blib/man3/Shell.3 Manifying blib/man3/FAQ.3 Manifying blib/man3/ProxyServer.3 Manifying blib/man3/Format.3 Manifying blib/man3/DBD.3 Manifying blib/man3/DBI.3 Manifying blib/man1/dbish.1 Manifying blib/man3/Proxy.3 Manifying blib/man3/W32ODBC.3 Manifying blib/man3/ADO.3 cfoifdv4/home/sched/opwsched/devtools/DBI1.13 CASTDEV make test PERL_DL_NONLAZY=1 /usr/local/bin/perl -Iblib/arch -Iblib/lib -I/usr/local/lib/pe rl5/5.00503/sun4-solaris -I/usr/local/lib/perl5/5.00503 test.pl test.pl DBI test application $Revision: 10.3 $ opendir(./../../../..): Permission denied at /usr/local/lib/perl5/5.00503/blib.p m line 47 Cannot find blib even in /../../../../.. Switch: DBI 1.13 by Tim Bunce, 1.13 Available Drivers: ADO,
RE: Hiding the db password
Pardon my jumping in here, but I believe the answer to your question is
Yes.
On another track, is OS authentication an option for the DB you are using?
No password is required if OS authentication is used. The OS does the
authentication and the DB just asks the OS. Works pretty well for SQL
Server. I've not had much use for it in Oracle, but I know it exists.
I'm not sure if its supported in DBI, though. Of course, that doesn't
solve your problem if another user can 'su' to your special OS user with
DB privileges, if you're really trying to secure the DB. If security is
that important, maybe you should consider another box where you can secure
everything. If a password is required by an automatic program, then the
password has to be available to the automatic program. By definition,
root (or Administrator) can always see that file (you do want it backed
up, don't you?). By convention, that password is required to be presented
as plaintext.
Just my $0.02.
Dave
On Oct 22, Kuo, David scribed:
In this case, local users still have access to the DB.pm and it has the
user/password info, isn't it?
David Kuo
-Original Message-
From: Hardy Merrill [mailto:hmerrill;redhat.com]
Sent: Tuesday, October 22, 2002 3:35 PM
To: Juha-Mikko Ahonen
Cc: Hardy Merrill; John Gedeon; [EMAIL PROTECTED]
Subject: Re: Hiding the db password
Juha-Mikko Ahonen [[EMAIL PROTECTED]] wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tuesday 22 October 2002 21:41, Hardy Merrill wrote:
Assuming you create a Perl module outside of the webserver's
document root, the tricky thing is that for cgi scripts, the
user that the web server is running as needs at least read
access to that file that contains the DB passwords.
This solution works as long as all of the programmers are trusted
users and only non-programmers are untrusted. Creating a script which
prints out the DB password would be quite easy. But if the programmers
are not a concern, then there is no problem with the filesystem rights
management.
Better still would be to, instead of just placing the passwords
in a separate module, to place the entire connect into its own
subroutine in that separate module. That way, the only access
to that password info would be through the also encapsulated
connect subroutine - so the only exposed call is to the connect
subroutine:
/var/my_perl_modules
DB.pm - contains subroutine my_connect, which either
returns a good $dbh, or undef(if unsuccessful)
/var/www/cgi-bin/my_script.cgi
--
use lib '/var/my_perl_modules';
use DB;
my $dbh = DB::my_connect();
if (!$dbh) {
...bad error...do something drastic
}
No password info is exposed to scripts that use the connect. Can
you see a way in this scenario that a non-root programmer could
easily print the password info?
Another solution (as Henri Asseily pointed out) would be asking the
password each time the Apache configuration changes, someone unplugs
the power, frustrated cracker forkbombs the system... Not a bad
solution on a stable environment, though. SSL uses the same method.
The simplest method of all would be to eliminate unauthorized access
using the database management systems authorization schemas. For
example, you could allow access to the database for only selected Unix
users. This is done using the access control methods offered by the
DBMS.
If no-one can su(1) to www-data (or some other user Apache runs as),
only root, www-data and other selected users can connect to the
database. No passwords needed.
I've done this before with the Postgres DB - using Postgres ident
authentication, a user only gets access to a database if that
user is both a system user(has a regular system user account),
and a postgres user, and that user successfully logs in to his
regular system account - then he is granted access(this is a very
simplistic view). Couple that with suexec and you have pretty
good privacy(no pun intended) for your passwords.
Re: Hiding the db password
Kuo, David [[EMAIL PROTECTED]] wrote:
In this case, local users still have access to the DB.pm and it has the
user/password info, isn't it?
No - previously in this thread I described setting permissions
for DB.pm in a *nix environment to 750, where the owner is
john(7 for read, write, and execute), the group is just_us
(members of this group would be John, his boss, and the user that
the webserver runs under - permissions 5 for read and execute),
and 0 for world, meaning no-one else can even see the DB.pm file.
Hardy
David Kuo
-Original Message-
From: Hardy Merrill [mailto:hmerrill;redhat.com]
Sent: Tuesday, October 22, 2002 3:35 PM
To: Juha-Mikko Ahonen
Cc: Hardy Merrill; John Gedeon; [EMAIL PROTECTED]
Subject: Re: Hiding the db password
Juha-Mikko Ahonen [[EMAIL PROTECTED]] wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tuesday 22 October 2002 21:41, Hardy Merrill wrote:
Assuming you create a Perl module outside of the webserver's
document root, the tricky thing is that for cgi scripts, the
user that the web server is running as needs at least read
access to that file that contains the DB passwords.
This solution works as long as all of the programmers are trusted
users and only non-programmers are untrusted. Creating a script which
prints out the DB password would be quite easy. But if the programmers
are not a concern, then there is no problem with the filesystem rights
management.
Better still would be to, instead of just placing the passwords
in a separate module, to place the entire connect into its own
subroutine in that separate module. That way, the only access
to that password info would be through the also encapsulated
connect subroutine - so the only exposed call is to the connect
subroutine:
/var/my_perl_modules
DB.pm - contains subroutine my_connect, which either
returns a good $dbh, or undef(if unsuccessful)
/var/www/cgi-bin/my_script.cgi
--
use lib '/var/my_perl_modules';
use DB;
my $dbh = DB::my_connect();
if (!$dbh) {
...bad error...do something drastic
}
No password info is exposed to scripts that use the connect. Can
you see a way in this scenario that a non-root programmer could
easily print the password info?
Another solution (as Henri Asseily pointed out) would be asking the
password each time the Apache configuration changes, someone unplugs
the power, frustrated cracker forkbombs the system... Not a bad
solution on a stable environment, though. SSL uses the same method.
The simplest method of all would be to eliminate unauthorized access
using the database management systems authorization schemas. For
example, you could allow access to the database for only selected Unix
users. This is done using the access control methods offered by the
DBMS.
If no-one can su(1) to www-data (or some other user Apache runs as),
only root, www-data and other selected users can connect to the
database. No passwords needed.
I've done this before with the Postgres DB - using Postgres ident
authentication, a user only gets access to a database if that
user is both a system user(has a regular system user account),
and a postgres user, and that user successfully logs in to his
regular system account - then he is granted access(this is a very
simplistic view). Couple that with suexec and you have pretty
good privacy(no pun intended) for your passwords.
--
Hardy Merrill
Senior Software Engineer
Red Hat, Inc.
Oracle placeholders in DDL statements
I'm using DBI with Oracle 8.1.6. Does anyone know if you can use placeholders with DDL statments, such as: CREATE TABLE FOO AS SELECT * FROM BAR WHERE name = ? I get an Oracle ORA-01036 error (illegal variable name/number) which suggests it can't be done. A DBA here thinks it should only fail on older Oracle versions. I have not found any definitive answers in the Oracle docs we have or on the web so far. Steve Sapovits GSI Commerce [EMAIL PROTECTED]
Re: Oracle placeholders in DDL statements
Maybe you could post some of your code? Are you binding a value to that placeholder? -Joe --- Steve Sapovits [EMAIL PROTECTED] wrote: I'm using DBI with Oracle 8.1.6. Does anyone know if you can use placeholders with DDL statments, such as: CREATE TABLE FOO AS SELECT * FROM BAR WHERE name = ? I get an Oracle ORA-01036 error (illegal variable name/number) which suggests it can't be done. A DBA here thinks it should only fail on older Oracle versions. I have not found any definitive answers in the Oracle docs we have or on the web so far. Steve Sapovits GSI Commerce [EMAIL PROTECTED] __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com
RE: Hiding the db password
Perhaps, but if you're using Oracle, you could store the password in the DB as obfuscated, which is how Oracle refers to encryption. Tbe table containing the password would be stored in a different schema than the one to which you'd normally attach to. In order to get the password, you would login using a dummy account and password. This dummy account would not be able to do anything except execute a function or procedure in the password schema. This function/procedure would return the text of the password (or the hash, if you wanted to decrypt in Perl) as well as audit the call. At least this way, the password's usage could be monitored to see if it has been stolen. Not perfect (I don't know if there is a perfect solution), but almost... HTH! GL! :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: John Gedeon [mailto:jgedeon;qualcomm.com] Sent: Tuesday, October 22, 2002 2:46 PM To: [EMAIL PROTECTED] Subject: RE: Hiding the db password Thanks for the tips guys. I will post my solution if any when I get one. but it seems like someone will always be able to see the passwords... John Proverbs 3:5 Trust in the Lord with all your heart and lean not on your own understanding;
RE: Hiding the db password
But I thought since the user is going to access the db through the cgi
script should be accessible to no-body. And the DB.pm module should be
accessible too since it is referenced by the cgi. You at least need to have
4 (read only permission) for no-body in order to run the cgi.
David Kuo
-Original Message-
From: Hardy Merrill [mailto:hmerrill;redhat.com]
Sent: Tuesday, October 22, 2002 3:55 PM
To: Kuo, David
Cc: 'Hardy Merrill'; Juha-Mikko Ahonen; John Gedeon; [EMAIL PROTECTED]
Subject: Re: Hiding the db password
Kuo, David [[EMAIL PROTECTED]] wrote:
In this case, local users still have access to the DB.pm and it has the
user/password info, isn't it?
No - previously in this thread I described setting permissions
for DB.pm in a *nix environment to 750, where the owner is
john(7 for read, write, and execute), the group is just_us
(members of this group would be John, his boss, and the user that
the webserver runs under - permissions 5 for read and execute),
and 0 for world, meaning no-one else can even see the DB.pm file.
Hardy
David Kuo
-Original Message-
From: Hardy Merrill [mailto:hmerrill;redhat.com]
Sent: Tuesday, October 22, 2002 3:35 PM
To: Juha-Mikko Ahonen
Cc: Hardy Merrill; John Gedeon; [EMAIL PROTECTED]
Subject: Re: Hiding the db password
Juha-Mikko Ahonen [[EMAIL PROTECTED]] wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tuesday 22 October 2002 21:41, Hardy Merrill wrote:
Assuming you create a Perl module outside of the webserver's
document root, the tricky thing is that for cgi scripts, the
user that the web server is running as needs at least read
access to that file that contains the DB passwords.
This solution works as long as all of the programmers are trusted
users and only non-programmers are untrusted. Creating a script which
prints out the DB password would be quite easy. But if the programmers
are not a concern, then there is no problem with the filesystem rights
management.
Better still would be to, instead of just placing the passwords
in a separate module, to place the entire connect into its own
subroutine in that separate module. That way, the only access
to that password info would be through the also encapsulated
connect subroutine - so the only exposed call is to the connect
subroutine:
/var/my_perl_modules
DB.pm - contains subroutine my_connect, which either
returns a good $dbh, or undef(if unsuccessful)
/var/www/cgi-bin/my_script.cgi
--
use lib '/var/my_perl_modules';
use DB;
my $dbh = DB::my_connect();
if (!$dbh) {
...bad error...do something drastic
}
No password info is exposed to scripts that use the connect. Can
you see a way in this scenario that a non-root programmer could
easily print the password info?
Another solution (as Henri Asseily pointed out) would be asking the
password each time the Apache configuration changes, someone unplugs
the power, frustrated cracker forkbombs the system... Not a bad
solution on a stable environment, though. SSL uses the same method.
The simplest method of all would be to eliminate unauthorized access
using the database management systems authorization schemas. For
example, you could allow access to the database for only selected Unix
users. This is done using the access control methods offered by the
DBMS.
If no-one can su(1) to www-data (or some other user Apache runs as),
only root, www-data and other selected users can connect to the
database. No passwords needed.
I've done this before with the Postgres DB - using Postgres ident
authentication, a user only gets access to a database if that
user is both a system user(has a regular system user account),
and a postgres user, and that user successfully logs in to his
regular system account - then he is granted access(this is a very
simplistic view). Couple that with suexec and you have pretty
good privacy(no pun intended) for your passwords.
--
Hardy Merrill
Senior Software Engineer
Red Hat, Inc.
RE: Oracle placeholders in DDL statements
This does it if I substitute the 'XXX' values with our access params.
#!/usr/local/bin/perl
use strict;
use DBI;
my $dbh = DBI-connect('DBI:Oracle:XXX', 'XXX', 'XXX', {RaiseError = 1});
$dbh-do(CREATE TABLE foo (col_1 VARCHAR2(10), col_2 NUMBER));
$dbh-do(INSERT INTO foo VALUES ('foo', 1));
my $sth =
$dbh-prepare(CREATE TABLE foo2 AS SELECT * FROM foo WHERE col_2 = ?);
$sth-execute(1);
Steve Sapovits
GSI Commerce
Work Email: [EMAIL PROTECTED]
Home Email: [EMAIL PROTECTED]
Work Phone: 610-491-7087
Cell: 610-574-7706
Pager: 877-239-4003
-Original Message-
From: Joe Raube [SMTP:[EMAIL PROTECTED]]
Sent: Tuesday, October 22, 2002 4:19 PM
To: Steve Sapovits; [EMAIL PROTECTED]
Subject: Re: Oracle placeholders in DDL statements
Maybe you could post some of your code?
Are you binding a value to that placeholder?
-Joe
--- Steve Sapovits [EMAIL PROTECTED] wrote:
I'm using DBI with Oracle 8.1.6. Does anyone know if you can use
placeholders with DDL statments, such as:
CREATE TABLE FOO AS SELECT * FROM BAR WHERE name = ?
I get an Oracle ORA-01036 error (illegal variable name/number)
which suggests it can't be done. A DBA here thinks it should only
fail on older Oracle versions. I have not found any definitive
answers in the Oracle docs we have or on the web so far.
Steve Sapovits
GSI Commerce
[EMAIL PROTECTED]
__
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos More
http://faith.yahoo.com
Inserting text into database
I have a Web application that has a textarea field where a user can type a
message and then the message will be stored in the database. Here is the
code for the insert:
insert into alerts (alrt, ncmp, suno, mess, prty, spst, dnst, crea, cdat)
values ('$max_alert', '$company','$supplier', '$message', '$priority', '2',
'2', '$userid', SYSDATE)
This works fine unless there is a single quote in the message, which
obviously is a big problem since I cannot restrict my users to not enter
quotes into their text message. How can I avoid having this issue?
Re: Inserting text into database
That's an easy one that's been answered many times on this list.
IMHO, use placeholders - read the excellent documentation included
with the Perl DBI module by doing
perldoc DBI
at a command prompt, and search for placeholder. Using placeholders
takes care of all quoting issues, and it can improve performance. But
it's worth it even if you only use it for quoting.
HTH.
--
Hardy Merrill
Senior Software Engineer
Red Hat, Inc.
[EMAIL PROTECTED] [[EMAIL PROTECTED]] wrote:
I have a Web application that has a textarea field where a user can type a
message and then the message will be stored in the database. Here is the
code for the insert:
insert into alerts (alrt, ncmp, suno, mess, prty, spst, dnst, crea, cdat)
values ('$max_alert', '$company','$supplier', '$message', '$priority', '2',
'2', '$userid', SYSDATE)
This works fine unless there is a single quote in the message, which
obviously is a big problem since I cannot restrict my users to not enter
quotes into their text message. How can I avoid having this issue?
Re: Inserting text into database
On Tue, 22 Oct 2002 16:22:04 -0400 [EMAIL PROTECTED] wrote:
insert into alerts (alrt, ncmp, suno, mess, prty, spst, dnst, crea, cdat)
values ('$max_alert', '$company','$supplier', '$message',
'$priority', '2',
'2', '$userid', SYSDATE)
This works fine unless there is a single quote in the message, which
obviously is a big problem since I cannot restrict my users to not enter
quotes into their text message. How can I avoid having this issue?
# add this near the top of your code
use HTML::Entities;
# add this just before your insert command
encode_entities($message)
Mike Schienle
Interactive Visuals, Inc.
http://www.ivsoftware.com
-
This message sent using EMUmail -- http://www.emumail.com
-
Jumping through hoops to get E-mail on the road?
You've got two choices: Join the circus, or use MollyMail.
Molly Mail -- http://www.mollymail.com
Re: Oracle placeholders in DDL statements
On Tue, 22 Oct 2002 16:41:48 -0400 Steve Sapovits [EMAIL PROTECTED] wrote:
This does it if I substitute the 'XXX' values with our access params.
#!/usr/local/bin/perl
use strict;
use DBI;
my $dbh = DBI-connect('DBI:Oracle:XXX', 'XXX', 'XXX', {RaiseError =
1});
$dbh-do(CREATE TABLE foo (col_1 VARCHAR2(10), col_2 NUMBER));
$dbh-do(INSERT INTO foo VALUES ('foo', 1));
my $sth =
$dbh-prepare(CREATE TABLE foo2 AS SELECT * FROM foo WHERE col_2 =
?);
$sth-execute(1);
Try adding DBI-trace(4) to see what DBI thinks is happening.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.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.
Re: Inserting text into database
On Tue, 22 Oct 2002 16:16:12 MDT Mike Schienle [EMAIL PROTECTED] wrote:
On Tue, 22 Oct 2002 16:22:04 -0400 [EMAIL PROTECTED] wrote:
insert into alerts (alrt, ncmp, suno, mess, prty, spst, dnst, crea, cdat)
values ('$max_alert', '$company','$supplier', '$message',
'$priority', '2',
'2', '$userid', SYSDATE)
This works fine unless there is a single quote in the message, which
obviously is a big problem since I cannot restrict my users to not enter
quotes into their text message. How can I avoid having this issue?
# add this near the top of your code
use HTML::Entities;
# add this just before your insert command
encode_entities($message)
This would require decoding after fetching and would make searching
difficult. There are already quoting methods specific to DBI and Oracle.
As Hardy suggested, placeholders would be even better.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.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.
Re: Inserting text into database
Michael A Chase wrote:
On Tue, 22 Oct 2002 16:16:12 MDT Mike Schienle [EMAIL PROTECTED] wrote:
On Tue, 22 Oct 2002 16:22:04 -0400 [EMAIL PROTECTED] wrote:
insert into alerts (alrt, ncmp, suno, mess, prty, spst, dnst, crea, cdat)
values ('$max_alert', '$company','$supplier', '$message',
'$priority', '2',
'2', '$userid', SYSDATE)
This works fine unless there is a single quote in the message, which
obviously is a big problem since I cannot restrict my users to not enter
quotes into their text message. How can I avoid having this issue?
# add this near the top of your code
use HTML::Entities;
# add this just before your insert command
encode_entities($message)
This would require decoding after fetching and would make searching
difficult. There are already quoting methods specific to DBI and Oracle.
As Hardy suggested, placeholders would be even better.
Besides it wouldn't actually solve the problem - the ' character is
passed untouched by HTML::Entities.
Steve
--
Steve Piner
Web Applications Developer
Marketview Limited
http://www.marketview.co.nz
Re: Oracle placeholders in DDL statements
Looks like an Oracle error, which is what I'd expect. I'm just
wondering if Oracle is supposed to support this or not ...
Here's the trace output (sorry for any wrapping):
DBI 1.30-nothread dispatch trace level set to 4
Note: perl is running without the recommended perl -w option
- DBI-connect(DBI:Oracle:XXX, XXX, , HASH(0x199564))
- DBI-install_driver(Oracle) for solaris perl=5.006001 pid=22975
ruid=103 euid=103
install_driver: DBD::Oracle version 1.12 loaded from
/usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris
/DBD/Oracle.pm
New DBI::dr (for DBD::Oracle::dr, parent=, id=)
dbih_setup_handle(DBI::dr=HASH(0x2b06fc)=DBI::dr=HASH(0x33b224),
DBD::Oracle::dr, 0, Null!)
dbih_make_com(Null!, DBD::Oracle::dr, 88) thr#0
- install_driver= DBI::dr=HASH(0x2b06fc)
- connect for DBD::Oracle::dr (DBI::dr=HASH(0x2b06fc)~0x33b224
'catman' 'catuser' HASH(0x277a00))
New DBI::db (for DBD::Oracle::db, parent=DBI::dr=HASH(0x33b224), id=)
dbih_setup_handle(DBI::db=HASH(0x335a78)=DBI::db=HASH(0x26b9ac),
DBD::Oracle::db, 2b0450, Null!)
dbih_make_com(DBI::dr=HASH(0x33b224), DBD::Oracle::db, 116) thr#0
- connect= DBI::db=HASH(0x335a78) at DBI.pm line 503 via cbindt line 8
- STORE for DBD::Oracle::db (DBI::db=HASH(0x26b9ac)~INNER 'RaiseError' 1)
STORE DBI::db=HASH(0x26b9ac) 'RaiseError' = 1
- STORE= 1 at DBI.pm line 545 via cbindt line 8
- STORE for DBD::Oracle::db (DBI::db=HASH(0x26b9ac)~INNER 'PrintError' 1)
STORE DBI::db=HASH(0x26b9ac) 'PrintError' = 1
- STORE= 1 at DBI.pm line 545 via cbindt line 8
- STORE for DBD::Oracle::db (DBI::db=HASH(0x26b9ac)~INNER 'AutoCommit' 1)
- STORE= 1 at DBI.pm line 545 via cbindt line 8
- connect= DBI::db=HASH(0x335a78)
- do in DBD::_::db for DBD::Oracle::db
(DBI::db=HASH(0x335a78)~0x26b9ac 'CREATE TABLE foo (col_1 VARCHAR
2(10), col_2 NUMBER)')
[steve@dopey:/home/steve/gsi/perl_test] $ cbindt
DBI 1.30-nothread dispatch trace level set to 4
Note: perl is running without the recommended perl -w option
- DBI-connect(DBI:Oracle:catman, catuser, , HASH(0x199564))
- DBI-install_driver(Oracle) for solaris perl=5.006001 pid=22978
ruid=103 euid=103
install_driver: DBD::Oracle version 1.12 loaded from
/usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris
/DBD/Oracle.pm
New DBI::dr (for DBD::Oracle::dr, parent=, id=)
dbih_setup_handle(DBI::dr=HASH(0x2b06fc)=DBI::dr=HASH(0x33b224),
DBD::Oracle::dr, 0, Null!)
dbih_make_com(Null!, DBD::Oracle::dr, 88) thr#0
- install_driver= DBI::dr=HASH(0x2b06fc)
- connect for DBD::Oracle::dr (DBI::dr=HASH(0x2b06fc)~0x33b224
'catman' 'catuser' HASH(0x277a00))
New DBI::db (for DBD::Oracle::db, parent=DBI::dr=HASH(0x33b224), id=)
dbih_setup_handle(DBI::db=HASH(0x335a78)=DBI::db=HASH(0x26b9ac),
DBD::Oracle::db, 2b0450, Null!)
dbih_make_com(DBI::dr=HASH(0x33b224), DBD::Oracle::db, 116) thr#0
- connect= DBI::db=HASH(0x335a78) at DBI.pm line 503 via cbindt line 8
- STORE for DBD::Oracle::db (DBI::db=HASH(0x26b9ac)~INNER 'RaiseError' 1)
STORE DBI::db=HASH(0x26b9ac) 'RaiseError' = 1
- STORE= 1 at DBI.pm line 545 via cbindt line 8
- STORE for DBD::Oracle::db (DBI::db=HASH(0x26b9ac)~INNER 'PrintError' 1)
STORE DBI::db=HASH(0x26b9ac) 'PrintError' = 1
- STORE= 1 at DBI.pm line 545 via cbindt line 8
- STORE for DBD::Oracle::db (DBI::db=HASH(0x26b9ac)~INNER 'AutoCommit' 1)
- STORE= 1 at DBI.pm line 545 via cbindt line 8
- connect= DBI::db=HASH(0x335a78)
- do in DBD::_::db for DBD::Oracle::db
(DBI::db=HASH(0x335a78)~0x26b9ac 'CREATE TABLE foo (col_1 VARCHAR
2(10), col_2 NUMBER)')
1 - prepare for DBD::Oracle::db (DBI::db=HASH(0x26b9ac)~INNER 'CREATE
TABLE foo (col_1 VARCHAR2(10), col_2
NUMBER)' undef)
New DBI::st (for DBD::Oracle::st, parent=DBI::db=HASH(0x26b9ac), id=)
dbih_setup_handle(DBI::st=HASH(0x33b218)=DBI::st=HASH(0x26b988),
DBD::Oracle::st, 33b254, Null!)
dbih_make_com(DBI::db=HASH(0x26b9ac), DBD::Oracle::st, 208) thr#0
dbd_st_prepare'd sql CREATE
dbd_describe skipped for CREATE
1 - prepare= DBI::st=HASH(0x33b218) at DBI.pm line 1212 via cbindt line 10
- execute for DBD::Oracle::st (DBI::st=HASH(0x33b218)~0x26b988)
dbd_st_execute CREATE (out0, lob0)...
dbd_st_execute CREATE returned (SUCCESS, rpc0, fn1, out0)
- execute= '0E0' at DBI.pm line 1213 via cbindt line 10
- rows for DBD::Oracle::st (DBI::st=HASH(0x33b218)~0x26b988)
- rows= 0 at DBI.pm line 1214 via cbindt line 10
- do= '0E0' at cbindt line 10
DESTROY ignored for outer handle DBI::st=HASH(0x33b218) (inner
DBI::st=HASH(0x26b988))
- DESTROY for DBD::Oracle::st (DBI::st=HASH(0x26b988)~INNER)
- DESTROY= undef at cbindt line 11
dbih_clearcom 0x33b218 (com 0x372e08, type 3) done.
- do for DBD::Oracle::db (DBI::db=HASH(0x335a78)~0x26b9ac 'INSERT INTO
foo VALUES ('foo', 1)')
1 - prepare for DBD::Oracle::db
Re: Oracle placeholders in DDL statements
On Tue, 22 Oct 2002 20:37:34 -0400 Steve Sapovits [EMAIL PROTECTED] wrote:
Looks like an Oracle error, which is what I'd expect. I'm just
wondering if Oracle is supposed to support this or not ...
Here's the trace output (sorry for any wrapping):
.. . .
DBI::st=HASH(0x26b988))
- DESTROY for DBD::Oracle::st (DBI::st=HASH(0x26b988)~INNER)
- DESTROY= undef at cbindt line 13
dbih_clearcom 0x33b2c0 (com 0x372e08, type 3) done.
- prepare for DBD::Oracle::db (DBI::db=HASH(0x335a78)~0x26b9ac
'CREATE TABLE foo2 AS SELECT * FROM foo WHERE col_2 = ?')
New DBI::st (for DBD::Oracle::st, parent=DBI::db=HASH(0x26b9ac), id=)
dbih_setup_handle(DBI::st=HASH(0x26ba60)=DBI::st=HASH(0x277a00),
DBD::Oracle::st, 33b398, Null!)
dbih_make_com(DBI::db=HASH(0x26b9ac), DBD::Oracle::st, 208) thr#0
dbd_preparse scanned 1 distinct placeholders
dbd_st_prepare'd sql CREATE
dbd_describe skipped for CREATE
- prepare= DBI::st=HASH(0x26ba60) at cbindt line 13
- execute for DBD::Oracle::st (DBI::st=HASH(0x26ba60)~0x277a00 1)
bind :p1 == 1 (type 0)
bind :p1 == 1 (size 1/2/0, ptype 5, otype 1)
bind :p1 == '1' (size 1/1, otype 1, indp 0, at_exec 1)
OCIErrorGet after OCIBindByName (er1:ok): -1, 1036: ORA-01036:
illegal variable name/number
!! ERROR: 1036 'ORA-01036: illegal variable name/number (DBD ERROR:
OCIBindByName)'
- execute= undef at cbindt line 16
DBD::Oracle::st execute failed: ORA-01036: illegal variable name/number
(DBD ERROR: OCIBindByName) at cbindt line 16.
DBD::Oracle::st execute failed: ORA-01036: illegal variable name/number
(DBD ERROR: OCIBindByName) at cbindt line 16.
.. . .
This does it if I substitute the 'XXX' values with our access params.
#!/usr/local/bin/perl
use strict;
use DBI;
my $dbh = DBI-connect('DBI:Oracle:XXX', 'XXX', 'XXX', {RaiseError =
1});
$dbh-do(CREATE TABLE foo (col_1 VARCHAR2(10), col_2 NUMBER));
$dbh-do(INSERT INTO foo VALUES ('foo', 1));
my $sth =
$dbh-prepare(CREATE TABLE foo2 AS SELECT * FROM foo WHERE col_2 =
?);
$sth-execute(1);
When you talk to Oracle, just tell them it's an OCI application. You
should be able to get the parsed statement text from one of the database
views. I would expect the '?' to be converted to ':p1', but it would be
worth seeing if it's something different.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.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.
Re: Oracle placeholders in DDL statements
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, Oct 22, 2002 at 08:37:34PM -0400, Steve Sapovits wrote: Looks like an Oracle error, which is what I'd expect. I'm just wondering if Oracle is supposed to support this or not ... Can you do a trace(10) instead? I'm curious to see the OCI calls involved. - -- Stephen Clouse [EMAIL PROTECTED] Senior Programmer, IQ Coordinator Project Lead The IQ Group, Inc. http://www.theiqgroup.com/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE9tg2gA4aoazQ9p2cRAqk0AJ9qSf/Wetqte71HIhNG3ZBXTvpGnACg4Dlh HEXIPnN5apQe9P1PcBw92aY= =t2V9 -END PGP SIGNATURE-
Re: Oracle placeholders in DDL statements
Here's trace(10) output. It dumped core at the end, but after the error. DBI 1.30-nothread dispatch trace level set to 10 Note: perl is running without the recommended perl -w option - DBI-connect(DBI:Oracle:XXX, XXX, , HASH(0x199564)) - DBI-install_driver(Oracle) for solaris perl=5.006001 pid=25962 ruid=103 euid=103 install_driver: DBD::Oracle version 1.12 loaded from /usr/local/lib/perl5/site_perl/5.6.1/sun4-solaris /DBD/Oracle.pm New DBI::dr (for DBD::Oracle::dr, parent=, id=) dbih_setup_handle(DBI::dr=HASH(0x2b06fc)=DBI::dr=HASH(0x33b224), DBD::Oracle::dr, 0, Null!) dbih_make_com(Null!, DBD::Oracle::dr, 88) thr#0 dbih_setup_attrib(DBI::dr=HASH(0x33b224), Err, Null!) SCALAR(0x26b7e0) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x33b224), State, Null!) SCALAR(0x2b4d28) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x33b224), Errstr, Null!) SCALAR(0x26b7bc) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x33b224), Debug, Null!) 0 (already defined) dbih_setup_attrib(DBI::dr=HASH(0x33b224), FetchHashKeyName, Null!) 'NAME' (already defined) - install_driver= DBI::dr=HASH(0x2b06fc) connect DISPATCH (DBI::dr=HASH(0x2b06fc) rc1/3 @5 g0 ima1 pid#25962) at /usr/local/lib/perl5/site_ perl/5.6.1/sun4-solaris/DBI.pm line 503 via cbindt line 8 - connect for DBD::Oracle::dr (DBI::dr=HASH(0x2b06fc)~0x33b224 'catman' 'catuser' HASH(0x277a00)) New DBI::db (for DBD::Oracle::db, parent=DBI::dr=HASH(0x33b224), id=) dbih_setup_handle(DBI::db=HASH(0x335a78)=DBI::db=HASH(0x26b9dc), DBD::Oracle::db, 2b0450, Null!) dbih_make_com(DBI::dr=HASH(0x33b224), DBD::Oracle::db, 116) thr#0 dbih_setup_attrib(DBI::db=HASH(0x26b9dc), Err, DBI::dr=HASH(0x33b224)) SCALAR(0x26b7e0) (already defined) dbih_setup_attrib(DBI::db=HASH(0x26b9dc), State, DBI::dr=HASH(0x33b224)) SCALAR(0x2b4d28) (already define d) dbih_setup_attrib(DBI::db=HASH(0x26b9dc), Errstr, DBI::dr=HASH(0x33b224)) SCALAR(0x26b7bc) (already defin ed) dbih_setup_attrib(DBI::db=HASH(0x26b9dc), Debug, DBI::dr=HASH(0x33b224)) 0 (already defined) dbih_setup_attrib(DBI::db=HASH(0x26b9dc), FetchHashKeyName, DBI::dr=HASH(0x33b224)) 'NAME' (already defin ed) dbih_setup_attrib(DBI::db=HASH(0x26b9dc), HandleError, DBI::dr=HASH(0x33b224)) undef (not defined) Environment variables: _=cbindt MANPATH=/usr/local/man:/usr/man:/usr/share/man:/usr/X/man:/opt/schily/man:/usr/openwin/man _INIT_UTS_RELEASE=5.7 plt=/home/steve/perl_test/lib/GSI HZ=100 FPATH=/home/steve/sh PAGER=less -r _INIT_UTS_MACHINE=sun4u VISUAL=/usr/local/bin/vim DOMAIN=gspt.net PATH=.:/usr/X/bin:/usr/openwin/bin:/usr/local/bin:/home/steve/bin:/bin:/home/steve/sh:/usr/bin:/usr/u cb:/etc:/sbin:/usr/sbin:/usr/ccs/bin:/usr/local/sbin:/opt/schily/bin:/export/engineering/bin:/export/engineer ing/bin/qad:/gsi/shared/usr/bin:/usr/local/apache/bin:/usr/local/ssl/bin:/opt/PGP:/JavaDeveloper:/u01/app/ora cle/product/8.1.6/bin KSH_ENV=/home/steve/.kshrc REMOTE_HOST=192.168.160.15 MAILPATH=/var/mail/steve _INIT_UTS_VERSION=Generic_106541-14 COLUMNS=80 plb=/usr/local/lib/perl5 AWK=/bin/nawk CODEBASE=/home/steve/dev MAKE=/usr/local/bin/gmake OPENWINHOME=/usr/openwin EXINIT=:set report=0|:set sw=4|:set nows|:set tabstop=4|:set visualbell|:set directory=/home/steve/tm p|:set visualbell EDITOR=/usr/local/bin/vim _INIT_RUN_NPREV=0 HISTFILE=/home/steve/.khistory.dopey LOGNAME=steve _INIT_UTS_NODENAME=dopey _INIT_UTS_ISA=sparc MAIL=/var/mail/steve KSH_EDITOR=vi pager=less editor=/usr/local/bin/vim ORACLE_SID=qadb2 PS1=[steve@dopey:/home/steve/gsi/perl_test] $ _INIT_PREV_LEVEL=S HOST=dopey TERMCAP=d0|vt100|vt100-am|vt100am:do=^J:co#80:li#24:cl=50\E[;H\E[2J:sf=5\ED:le=^H:bs:am:cm=5\E[%i%d;% dH:nd=2\E[C:up=2\E[A:ce=3\E[K:cd=50\E[J:so=2\E[7m:se=2\E[m:us=2\E[4m:ue=2\E[m:md=2\E[1m:mr=2\E[7m:mb=2\E[5m:m e=2\E[m:is=\E[1;24r\E[24;1H:rf=/usr/share/lib/tabset/vt100:rs=\E\E[?3l\E[?4l\E[?5l\E[?7h\E[?8h:ks=\E[?1h\E=: ke=\E[?1l\E:ku=\EOA:kd=\EOB:kr=\EOC:kl=\EOD:kb=^H:ho=\E[H:k1=\EOP:k2=\EOQ:k3=\EOR:k4=\EOS:pt:sr=5\EM:vt#3:xn :sc=\E7:rc=\E8:cs=\E[%i%d;%dr: USER=steve XTERM=xterm -geometry 80x40 DISPLAY=192.168.160.15:0.0 CVSROOT=:pserver:steve@doc:/opt/cvsroot SHELL=/bin/ksh JAVA_HOME=/usr/java1.2 HISTSIZE=256 PLATFORM=SunOS HOME=/home/steve _INIT_UTS_SYSNAME=SunOS FCEDIT=/usr/local/bin/vim LD_LIBRARY_PATH=/usr/local/lib:/usr/X/lib:/usr/openwin/lib TERM=vt100 XAPPLRESDIR=/usr/X/lib/app-defaults:/usr/X/lib/X11/app-defaults ORACLE_HOME=/u01/app/oracle/product/8.1.6 PWD=/home/steve/gsi/perl_test
FW: Installing DBD::Oracle on Compaq Dec Alpha Tru64 OS with 9i
At this point, it would not be prudent to overlay [or add to] the base build on my machine, supplied by the vendor, as other areas in my company are using that basic Perl. I guess an additional installation of the stable 5.8.0 release to an alternate $prefix/path might supply a Perl build robust enough to contain this libperl.a file? I don't have root authority, so I would build to somewhere in my own directory area. If that is successful, then I can install DBD::Oracle [using the new 5.8.0 release] and then build a binary for my area's use. Comments? -Original Message- From: Rose, Bill Sent: Monday, October 21, 2002 4:58 PM To: '[EMAIL PROTECTED]' Subject: Installing DBD::Oracle on Compaq Dec Alpha Tru64 OS with 9i DBI 1.30 install went textbook. For DBD::Oracle I did a static dynamic build and they both failed at make -f Makefile.aperl perl. Failed to find /usr/lib/perl-5.6.0/lib/5.6.0/alpha-dec_osf/CORE/libperl.a. How would my Systems Admin install PERL [or add to it] so that the libperl.a shows up in the proper dir? Thanks. Bill
RE: Installing DBD::Oracle on Compaq Dec Alpha Tru64 OS with 9i
Bill, I don't have exactly your problem, but I run perl 5.6 and Oracle 8.1.7 on Tru64 V4.0 and V5.1. I do not have root priviledges either, but I have built perl, DBI and some of the DBD modules without any problems. Just choose a 'good' prefix when you build perl. As I want the same perl/DBI environment across multiple machines, I have to make sure it is some directory that I have write access to on all systems. Michael Fox -Original Message- From: Rose, Bill [mailto:Bill.Rose;bankofamerica.com] Sent: Wednesday, October 23, 2002 1:56 PM To: '[EMAIL PROTECTED]' Subject: FW: Installing DBD::Oracle on Compaq Dec Alpha Tru64 OS with 9i At this point, it would not be prudent to overlay [or add to] the base build on my machine, supplied by the vendor, as other areas in my company are using that basic Perl. I guess an additional installation of the stable 5.8.0 release to an alternate $prefix/path might supply a Perl build robust enough to contain this libperl.a file? I don't have root authority, so I would build to somewhere in my own directory area. If that is successful, then I can install DBD::Oracle [using the new 5.8.0 release] and then build a binary for my area's use. Comments? -Original Message- From: Rose, Bill Sent: Monday, October 21, 2002 4:58 PM To: '[EMAIL PROTECTED]' Subject: Installing DBD::Oracle on Compaq Dec Alpha Tru64 OS with 9i DBI 1.30 install went textbook. For DBD::Oracle I did a static dynamic build and they both failed at make -f Makefile.aperl perl. Failed to find /usr/lib/perl-5.6.0/lib/5.6.0/alpha-dec_osf/CORE/libperl.a. How would my Systems Admin install PERL [or add to it] so that the libperl.a shows up in the proper dir? Thanks. Bill Australia Post is committed to providing our customers with excellent service. If we can assist you in any way please either telephone 13 13 18 or visit our website www.auspost.com.au. CAUTION This e-mail and any files transmitted with it are privileged and confidential information intended for the use of the addressee. The confidentiality and/or privilege in this e-mail is not waived, lost or destroyed if it has been transmitted to you in error. If you have received this e-mail in error you must (a) not disseminate, copy or take any action in reliance on it; (b) please notify Australia Post immediately by return e-mail to the sender; and (c) please delete the original e-mail.
Re: Oracle placeholders in DDL statements
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, Oct 22, 2002 at 11:30:48PM -0400, Steve Sapovits wrote: Here's trace(10) output. It dumped core at the end, but after the error. Yeah, I'm seeing the exact same thing on 9.0.1. Even ran it through gdb and compared stuff versus a simple select. It certainly *feels* like a DBD::Oracle problem but I sure don't see it. - -- Stephen Clouse [EMAIL PROTECTED] Senior Programmer, IQ Coordinator Project Lead The IQ Group, Inc. http://www.theiqgroup.com/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE9tiSUA4aoazQ9p2cRAhdnAJ954QfZ5t3QkDfDaMYp6wPNCeTT/gCghAAU 6A+Xqk8DfPPPnHKgNB93RX0= =v42M -END PGP SIGNATURE-
