tie hashes in DBIx::Recordset
Hi,
How do I succesfully preserve the column order (''$fields'=
$joined_col') in my array-of-hashes generated using DBIx::Recordset?
When I run the code below, the column order doesn't differ at all from
when I don't 'tie' the %main::set hash.
/Marcus
___
use Tie::IxHash;
use DBIx::Recordset;
.
sub execute {
tie %main::set, 'Tie::IxHash';
*main::set = DBIx::Recordset-Search({('!DataSource' = $db,
'!Table' = $table,
'$fields' = $joined_col,
'$where'= $query,
'$max' = 10)});
.
}
Re: DBI and PDF...
At 16:18 11/03/2002 -0600, Herbold, John W. wrote:
Does anybody have an example or two of spitting out a result from a DBI call
into a PDF file ?
I am loosing what little hair I have left trying to get PDF::CREATE to put
thing like I want them!
Only way I ever use PDF creators is through Apache's FOP.
(Formatting Object Processor). It takes an XML and XSL/XSL:FO input
and gives you lovely PDF.
It's not Perl, but it is very easy to use:
# eg:
$com = java
. -cp
build/fop.jar;lib/batik.jar;lib/xalan-2.0.0.jar;lib/xerces-1.2.3.jar;lib/jimi-1.0.jar
. org.apache.fop.apps.Fop
. -c conf/userconfig.xml
. ($DEBUG ? ' -d' : '')
. -xsl $self-{pdf_xslt}
. -xml $self-{xml_input_filename}
. -pdf $self-{pdf_output_filename};
$com=~s|/|\\|g;
warn * Command: $com\n if $DEBUG;
# $r = system $com;
$r = system $com;
warn * \t...done.\n if $DEBUG;
warn * System said: $r\n if $DEBUG;
if ($r == 0){
warn * Seems okay!\n if $DEBUG;
$r = 1;
} else {
warn * Seems funny...\n if $DEBUG;
$r = undef;
}
if (-e $self-{pdf_output_filename}){
warn * Made PDF at $self-{pdf_output_filename} if $DEBUG;
} else {
warn * Did not make PDF at $self-{pdf_output_filename};
warn \t * Do you have java on the command line?;
$r = undef;
}
Worth looking into.
Lee
Re: DBI FAQ maintainer sought!
That's in the plans. In fact there's been an FAQ-O-Matic installed on dbi.perl.org for a while but it never quite got off the ground. The dbi.perl.org content is in the process of moving to a new host and a new maintainer. I hope FAQ-O-Matic will be part of that setup and then multiple maintainers can work on subsections of it (e.g., driver authors can maintain subsections about their drivers). But meanwhile, having someone kick some life into the traditional FAQ would be a very good thing. Currently it looks like Ilya's got the job :) Tim. On Mon, Mar 11, 2002 at 08:59:45PM -0500, Jeff Urlwin wrote: How about a FAQ-O-Matic type of setup? I can, probably, provide some hardware for it...(depending upon the needs) Jeff -Original Message- From: Tim Bunce [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 4:05 PM To: [EMAIL PROTECTED] Cc: Chris Pepper; [EMAIL PROTECTED] Subject: DBI FAQ maintainer sought! So, anyone want to volunteer?! Tim. On Mon, Mar 11, 2002 at 11:38:13AM -0700, [EMAIL PROTECTED] wrote: I work for Sun Microsystems and can tell you that this is a known issue for two reasons: 1. As you stated the cc command is a stub in Solaris until you pay for and install the appropriate package. 2. Perl is packaged and delivered with Solaris 8. Obviously unless you have cc installed then you will have to re-compile Perl as you won't have the same compiler as Sun had when compiling it for Solaris, which I believe is a requirement of DBI. I agree this is a good FAQ! Mike Key Chris Pepper wrote: Gents, We're having some trouble building DBI under Solaris 8, and I think our problem is a good FAQ candidate. When following your build procedure, we're getting hung up because it wants to use cc (which is present but disabled on our Solaris 8 system, because we haven't paid for the license). We have a working gcc installation, but I think we're going to have to rebuild perl from source with gcc, so DBI can be built using gcc. If this is a known problem, please add it to the FAQ. Thanks, Chris Pepper -- Chris Pepper: http://www.reppep.com/~pepper/ Rockefeller University: http://www.rockefeller.edu/
Re: DBI FAQ maintainer sought!
On Mon, Mar 11, 2002 at 02:18:25PM -0700, Sterin, Ilya wrote: K, looks like it has fairly enough info. I can take it unless someone else really insists:-) Looks like you're it! Do you need me to host it? No thanks, it should be part of dbi.perl.org. The DBI/FAQ.pm shipped with the DBI is the master and is what needs updating. Sending me a patch would be a fine way to do that. I suggest that you post a message asking people to suggest FAQ's (directly to you rather than the list). That, plus some trawling of the archives should give you ample material! But don't try to achieve perfection first time out. Just a decent update with the *most* Frequently Asked Qusetions's would be wonderful! Many thanks Ilya! Tim. Ilya -Original Message- From: Tim Bunce To: [EMAIL PROTECTED] Cc: Chris Pepper; [EMAIL PROTECTED] Sent: 3/11/02 2:04 PM Subject: DBI FAQ maintainer sought! So, anyone want to volunteer?! Tim. On Mon, Mar 11, 2002 at 11:38:13AM -0700, [EMAIL PROTECTED] wrote: I work for Sun Microsystems and can tell you that this is a known issue for two reasons: 1. As you stated the cc command is a stub in Solaris until you pay for and install the appropriate package. 2. Perl is packaged and delivered with Solaris 8. Obviously unless you have cc installed then you will have to re-compile Perl as you won't have the same compiler as Sun had when compiling it for Solaris, which I believe is a requirement of DBI. I agree this is a good FAQ! Mike Key Chris Pepper wrote: Gents, We're having some trouble building DBI under Solaris 8, and I think our problem is a good FAQ candidate. When following your build procedure, we're getting hung up because it wants to use cc (which is present but disabled on our Solaris 8 system, because we haven't paid for the license). We have a working gcc installation, but I think we're going to have to rebuild perl from source with gcc, so DBI can be built using gcc. If this is a known problem, please add it to the FAQ. Thanks, Chris Pepper -- Chris Pepper: http://www.reppep.com/~pepper/ Rockefeller University: http://www.rockefeller.edu/
RE: MS Access/CGI/Apache on WinNT questions
Jeff, You are correct that it does work from the command line. The share name fssdata share is accessible to me, but just to verify that something funky isn't going on, I copied the mdb file over to the web server and tried to run it from there. I still got the same message. As a newbie to perl and apache, I took on a task of trying to set up a web application to cut my teeth. The existing application has proven all too successful and the user population has outgrown MS Access. An Oracle server is in the works, but until then, I need to get it up and running using MS Access. I Know that this question is perl-101, but, given my inexperience with perl, what do you mean by ...set a trace file and set the DBI tracing level to 4...? Thanks, Pete MacGown -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 9:22 PM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions That, unfortunately, can be a bad message because of the way that DBD::ODBC handles opening the database. What's happening is that DBI can't open the database using the DSN-less style open (SQLDriverConnect) which fails and then tries to call SQLConnect, which expects a 32? character or less DSN. I hope to make this better in the future... In the mean time, I'm going to guess that this may work from the command line and not from the CGI. If that's not the case, this may or may not apply. Check the share name fssdata and ensure it's accessible from the username starting the web service (make sure the web server is not being started as local system, either, as that will have NO access to the network). IF that doesn't work, set a trace file and set the DBI tracing level to 4 (or above) and post it back here. Jeff Hi, I hope other people have been successful at getting CGI scripts running that access MS Access because I haven't. Here is a snippet of code that I am trying to get to work: #!d:/perl/bin/perl -wT use DBI; my $dsn = driver=Microsoft Access Driver (*.mdb);dbq=nrent01\\fssdata\\Roster03b.mdb; my $dbh = DBI-connect(DBI:ODBC:$dsn,admin,xyzzy) || print BRError Opening Database: $DBI::errstr\n; But I get this message: Error Opening Database: [Microsoft][ODBC Driver Manager] Invalid string or buffer length (SQL-S1090)(DBD: db_login/SQLConnect err=-1) Am I doing something amazingly stupid and just can't see it? Is there a better way to get to an MS Access database? Please help! Thanks, Pete MacGown
Host Arrays
Hello everyone Is there a way to use the host array interface of oracle 8i in perl dbi? With arrays, you manipulate an entire array with a single SQL statement. Thus, Oracle communication overhead is reduced markedly, especially in a networked environment. A major portion of runtime is spent on network roundtrips between the client program and the server database. Arrays reduce the roundtrips. For example, suppose you want to insert information about 300 employees into the EMP table. Without arrays your program must do 300 individual INSERTs--one for each employee. With arrays, only one INSERT needs to be done. Or you want to select many records from the database. For example: With embedded SQL in C you can do the following: - char emp_name[1000]; intemp_number[1000]; float salary[1000]; EXEC SQL SELECT ENAME, EMPNO, SAL INTO :emp_name, :emp_number, :salary FROM EMP WHERE SAL 2000; -- All data (maximum 1000) will come in one stream over the network to your client. I have tried the select with the fetchall_arrayref in perl, but behind the scenery multiple fetches are produced and there is no reduce of roundtrips between the client and the database. Thanks, Peter
Re: DBIx::Recordset PrevNextForm query
Actually, %fdat doesn't seem to be printable. After calling :
*set = DBIx::Recordset-Search({%fdat,('!DataSource' = $db,
'!Table' = $table,
'$fields' = $joined_col,
'$where' = $query,
'$max'= 5)});
and testing:
if(undef %fdat) {
print b%fdat undef/b,br;
} else {
print b%fdat def/b,br;
while (($a,$b) = each %fdat) {
print $a = $b;
}}
it says '%fdat undef' but won't print out each %fdat. Trying 'if
(exists($fdat{'$next'})), or any other key from Search above,
gives also a negative result.
I believe %fdat keeps the old parameters (q_col,columns,q_comp) instead of
getting and passing the new 'Search' ones above. Below is the 'Next' rendering
HTML code that points to that.
*
form method=POSTinput type=hidden name=$start value=0
input type=hidden name=$max value=5
input type=submit name=$next value=Next /forminput type=hidden
name=.cgifields value=q_col /input type=hidden name=.cgifields
value=columns /input type=hidden name=.cgifields value=q_comp
//form/body/html
*
How do I get the right %fdat parameters, so that PrevNext Form works? Now only
the first same 5 search results come up (there are many more).
As you can see in my code below the %fdat parameters passed are the ones
generated before my first 'submit'. I have many submits after that.
Further below is the Debug output.
Marcus
*
#!/usr/bin/perl -w
$|++;
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use DBIx::Recordset;
###Connects to db
$db = DBIx::Database - new({'!DataSource' = 'dbi:Pg:dbname=marcus_test',
'!Username' = 'nobody',
'!KeepOpen' = 1}) or die Couldn't connect to
database;
print header(),
start_html(-title='AG Database Access - Query page',
-BGCOLOR='white');
print start_form(),
h1(AG Database Access),hr;
###Choose tables
$tables = $db-AllTables;
print br,b(Select table:),br,
popup_menu(-name ='tables',
-values =[sort keys(%$tables)]),
submit(-name ='ok',
-value ='Ok'),br;
$table = param('tables');
if (param('tables')) {
$columns = $db-AllNames($table);
print b(Select columns to display:),
checkbox_group(-name ='columns',
-values=[@$columns],
-rows =4),br;
$joined_col = join(,,param('columns'));
###Specify query
print b(Specify query:),br,
scrolling_list(-name ='q_col',
-values=[@$columns],
-size =1),
scrolling_list(-name ='q_comp',
-values=[equals,less than or equal,less than],
-size=1),
textfield(-name='q_string',
-size=15),br,
Alternative SQL query: ,
textfield(-name='sql_string',
-size=42),hr;
$one=param('q_col');
$two=param('q_comp');
$three=param('q_string');
if ($two eq equals) {
$query = $one=$three; }
elsif ($two eq less than or equal) {
$query = $one=$three; }
elsif ($two eq less than) {
$query = $one$three; }
if (param(sql_string)) {
$query = param(sql_string);
}
print submit(-name ='submit',
-value ='Submit Query'),
defaults('Reset'),br;
} # if param($tables)
if (param('submit') || param('$next')) {
$DBIx::Recordset::Debug = 2;
*set = DBIx::Recordset-Search({%fdat,('!DataSource' = $db,
'!Table' = $table,
'$fields' = $joined_col,
'$where' = $query,
'$max'= 5)});
if(undef %fdat) {
print b%fdat undef/b,br;
} else {
print b%fdat def/b,br;
while (($a,$b) = each %fdat) {
print $a = $b;
}
}
$DBIx::Recordset::FetchsizeWarn=0;
###Print table and table head
print TABLE BORDER=4 WIDTH=60% BGCOLOR=\#EEEAAA\
BORDERCOLORLIGHT=\#AA\;
print Tr({-align='CENTER'},td[keys(%set)]);
foreach $row (@set) {
print Tr({-align='CENTER'},td[values(%$row)]);
}
print /TABLE;
###Should print out workable next,prev buttons
print $set - PrevNextForm (Previous, Next, \%fdat);
} #if(param('submit') || param('$next')) {
print end_form;
print end_html;
exit;
**
DB: Use already open dbh for dbi:Pg:dbname=marcus_test (id=1, numOpen = 0) DB:
'SELECT id,sbjrank,sbjname FROM mc_bplite_nt_go WHERE score=15 LIMIT 6'
bind_values= bind_types=
[Here is Table]
[Next button]
DB: Disconnect (id=1, numOpen = 0)
**
I have a little problem with PrevNextForm in DBIx::Recordset.
When I run the code below the table is displayed as I want with the
number of rows I specified in $max. But when I press 'Next' the SAME
page
comes up. It's impossible to see further than the $max rows.
I also attach the some of the HTML output.
Marcus
The
RE: Host Arrays
Look back in the archives. I asked this same question back in
January (the thread starts on Jan 24th). In short, yes. Fiddle
with $dbh-{RowCacheSize} to get what you want. If you set it to
200, then when you do your first fetch you will get 200 rows from
Oracle, and the next 199 fetches will be fulfilled from local memory,
not over the network.
--
j.
James FitzGibbon voice/fax 612-761-6121/4277
Consultant, TTS-3D@TPN4H [EMAIL PROTECTED]
-Original Message-
From: Schoenenberger Peter [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 12, 2002 10:02 AM
To: '[EMAIL PROTECTED]'
Subject: Host Arrays
Hello everyone
Is there a way to use the host array interface of oracle 8i
in perl dbi?
With arrays, you manipulate an entire array with a single SQL
statement.
Thus, Oracle communication overhead is reduced markedly,
especially in a
networked environment. A major portion of runtime is spent on network
roundtrips between the client program and the server
database. Arrays reduce
the roundtrips.
For example, suppose you want to insert information about 300
employees into
the EMP table. Without arrays your program must do 300 individual
INSERTs--one for each employee. With arrays, only one INSERT
needs to be
done. Or you want to select many records from the database.
For example: With embedded SQL in C you can do the following:
-
char emp_name[1000];
intemp_number[1000];
float salary[1000];
EXEC SQL SELECT ENAME, EMPNO, SAL
INTO :emp_name, :emp_number, :salary
FROM EMP
WHERE SAL 2000;
--
All data (maximum 1000) will come in one stream over the
network to your
client.
I have tried the select with the fetchall_arrayref in perl,
but behind the
scenery multiple fetches are produced and there is no reduce
of roundtrips
between the client and the database.
Thanks,
Peter
RE: MS Access/CGI/Apache on WinNT questions
Pete, Right before the connect, do the following: DBI-trace(5, c:/trace.txt); Then, send me the trace.txt from the web server's C: drive. (and, look at it yourself to see if the error is recognizable). Some other questions... What version of DBD::ODBC are you using? Are you using MS-Access security? Does the web server user have access to the admin database? Regards, Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 10:05 AM To: [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Jeff, You are correct that it does work from the command line. The share name fssdata share is accessible to me, but just to verify that something funky isn't going on, I copied the mdb file over to the web server and tried to run it from there. I still got the same message. As a newbie to perl and apache, I took on a task of trying to set up a web application to cut my teeth. The existing application has proven all too successful and the user population has outgrown MS Access. An Oracle server is in the works, but until then, I need to get it up and running using MS Access. I Know that this question is perl-101, but, given my inexperience with perl, what do you mean by ...set a trace file and set the DBI tracing level to 4...? Thanks, Pete MacGown -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 9:22 PM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions That, unfortunately, can be a bad message because of the way that DBD::ODBC handles opening the database. What's happening is that DBI can't open the database using the DSN-less style open (SQLDriverConnect) which fails and then tries to call SQLConnect, which expects a 32? character or less DSN. I hope to make this better in the future... In the mean time, I'm going to guess that this may work from the command line and not from the CGI. If that's not the case, this may or may not apply. Check the share name fssdata and ensure it's accessible from the username starting the web service (make sure the web server is not being started as local system, either, as that will have NO access to the network). IF that doesn't work, set a trace file and set the DBI tracing level to 4 (or above) and post it back here. Jeff Hi, I hope other people have been successful at getting CGI scripts running that access MS Access because I haven't. Here is a snippet of code that I am trying to get to work: #!d:/perl/bin/perl -wT use DBI; my $dsn = driver=Microsoft Access Driver (*.mdb);dbq=nrent01\\fssdata\\Roster03b.mdb; my $dbh = DBI-connect(DBI:ODBC:$dsn,admin,xyzzy) || print BRError Opening Database: $DBI::errstr\n; But I get this message: Error Opening Database: [Microsoft][ODBC Driver Manager] Invalid string or buffer length (SQL-S1090)(DBD: db_login/SQLConnect err=-1) Am I doing something amazingly stupid and just can't see it? Is there a better way to get to an MS Access database? Please help! Thanks, Pete MacGown
need some newbie logic help on Insert/Update
Hi,
I'm writing some code to transfer data from Oracle into Mysql on a nightly
basis. I have no problems connecting to the databases and doing inserts or
updates. My problem is with figuring out when to do an insert vs and
update without looping through a bunch of data. Here is some of my code. I
use the same table names and column names in each database.
use DBI;
# First grab data from our Oracle database
my $dbh = DBI-connect($oracle_db,$user,$password,
{RaiseError = 1, PrintError = 1, AutoCommit = 1} ) or
die Unable to connect: . $DBI::errstr . \n;
my $sel = $dbh-prepare( select * from SOME_TABLE );
$sel-execute();
# Now connect to the mysql database
my $mysql_dbh = DBI-connect($mysql_db,$mysql_user,$mysql_password,
{RaiseError = 1,PrintError = 1, AutoCommit = 1} ) or
die Unable to connect: . $DBI::errstr . \n;
# First a template for an Update
my $mysql_upd = $mysql_dbh-prepare(
UPDATE SOME_TABLE set FIRST_NAME=? where ID=? );
# Now a template for an Insert if this is new ID
my $mysql_ins =$mysql_dbh-prepare(
INSERT into SOME_TABLE(FIRST_NAME,ID) values(?,?) );
# Now here is where I have a problem. I could do the following for an
# new records
my $row_hash;
while($row_hash = $sel-fetchrow_hashref) {
$mysql_ins-execute($row_hash-{FIRST_NAME},$row_hash-{ID})
}
#Or I could do the following for an update
my $row_hash;
while($row_hash = $sel-fetchrow_hashref) {
$mysql_upd-execute($row_hash-{FIRST_NAME},$row_hash-{ID})
}
$sel-finish;
$dbh-disconnect;
$mysql_dbh-disconnect;
Now do I have to loop through the MySQL table seeing if I have to do an
INSERT or an UPDATE? Any cool shorthand I can use to do this test?
Thanks
Tim Carlson
Voice: (509) 376 3423
Email: [EMAIL PROTECTED]
EMSL UNIX System Support
RE: MS Access/CGI/Apache on WinNT questions
Hi Jeff: This is a snippet of the code: use DBI; my $dsn = driver=Microsoft Access Driver (*.mdb);dbq=nrent01\\fssdata\\Roster03b.mdb; DBI-trace(5, c:/trace.txt); my $dbh = DBI-connect(DBI:ODBC:$dsn,admin,xyzzy) || print BRError Opening Database: $DBI::errstr\n; my $sth = $dbh-prepare(SELECT [LNAME], [FNAME], [MI] FROM T_EMPLOYEES) || print Error in Prepare: $DBI::errstr\n; $sth-execute() || print BRError: $DBI::errstr\n; } This is what I get: DBI 1.201-nothread dispatch trace level set to 5 - DBI-connect(DBI:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=\\nrent01\fssdata\Roster03b.mdb, admin, ) - DBI-install_driver(ODBC) for MSWin32 perl=5.006001 pid=135 ruid=0 euid=0 install_driver: DBD::ODBC version 0.28 loaded from d:/Perl/site/lib/DBD/ODBC.pm New DBI::dr (for DBD::ODBC::dr, parent=, id=) dbih_setup_handle(DBI::dr=HASH(0x1a31908)=DBI::dr=HASH(0x191aa94), DBD::ODBC::dr, 0, Null!) dbih_make_com(Null!, DBD::ODBC::dr, 92) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Err, Null!) SCALAR(0x1b79f18) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), State, Null!) SCALAR(0x1b79f60) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Errstr, Null!) SCALAR(0x1b79f3c) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Handlers, Null!) ARRAY(0x191aa10) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Debug, Null!) 0 (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), FetchHashKeyName, Null!) 'NAME' (already defined) - install_driver= DBI::dr=HASH(0x1a31908) - connect for DBD::ODBC::dr (DBI::dr=HASH(0x1a31908)~0x191aa94 'driver=Microsoft Access Driver (*.mdb);dbq=\\nrent01\fssdata\Roster03b.mdb' 'admin' HASH(0x191ef94)) Also, I get a Dr. Watson on the NT machine saying Perl.exe choked. Any suggestions? Thanks, Pete -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 11:35 AM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Pete, Right before the connect, do the following: DBI-trace(5, c:/trace.txt); Then, send me the trace.txt from the web server's C: drive. (and, look at it yourself to see if the error is recognizable). Some other questions... What version of DBD::ODBC are you using? Are you using MS-Access security? Does the web server user have access to the admin database? Regards, Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 10:05 AM To: [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Jeff, You are correct that it does work from the command line. The share name fssdata share is accessible to me, but just to verify that something funky isn't going on, I copied the mdb file over to the web server and tried to run it from there. I still got the same message. As a newbie to perl and apache, I took on a task of trying to set up a web application to cut my teeth. The existing application has proven all too successful and the user population has outgrown MS Access. An Oracle server is in the works, but until then, I need to get it up and running using MS Access. I Know that this question is perl-101, but, given my inexperience with perl, what do you mean by ...set a trace file and set the DBI tracing level to 4...? Thanks, Pete MacGown -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 9:22 PM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions That, unfortunately, can be a bad message because of the way that DBD::ODBC handles opening the database. What's happening is that DBI can't open the database using the DSN-less style open (SQLDriverConnect) which fails and then tries to call SQLConnect, which expects a 32? character or less DSN. I hope to make this better in the future... In the mean time, I'm going to guess that this may work from the command line and not from the CGI. If that's not the case, this may or may not apply. Check the share name fssdata and ensure it's accessible from the username starting the web service (make sure the web server is not being started as local system, either, as that will have NO access to the network). IF that doesn't work, set a trace file and set the DBI tracing level to 4 (or above) and post it back here. Jeff Hi, I hope other people have been successful at getting CGI scripts running that access MS Access because I haven't. Here is a snippet of code that I am trying to get to work: #!d:/perl/bin/perl -wT use DBI; my $dsn = driver=Microsoft Access Driver (*.mdb);dbq=nrent01\\fssdata\\Roster03b.mdb; my $dbh = DBI-connect(DBI:ODBC:$dsn,admin,xyzzy) ||
RE: need some newbie logic help on Insert/Update
Now do I have to loop through the MySQL table seeing if I
have to do an
INSERT or an UPDATE? Any cool shorthand I can use to do this test?
How big is the table? i.e. if this is happening nightly, is it
prohibitively expensive to do this:
my $users_in_mysql = $mysql_dbh-selectall_hashref(SELECT ID FROM
SOME_TABLE, ID);
while( ($first_name, $id) = $sel-fetch ) {
if( exists $users_in_mysql-{$id} ) {
# do update
} else {
# do insert
}
}
You might want to watch your user of fetchrow_hashref when you're grabbing
from Oracle. It's convenient but has added CPU cost.
It might also be easier to put all the users into a list using a fetch loop
rather than using the selectall_hashref above. Just an idea.
--
j.
James FitzGibbon voice/fax 612-761-6121/4277
Consultant, TTS-3D@TPN4H [EMAIL PROTECTED]
RE: DBI FAQ maintainer sought!
Great, I'll get a message out tonight, to the list as well as possibly some newsgroup, search the archive, etc... Ilya -Original Message- From: Tim Bunce To: Sterin, Ilya Cc: 'Tim Bunce '; 'Chris Pepper '; '[EMAIL PROTECTED] ' Sent: 3/12/02 7:59 AM Subject: Re: DBI FAQ maintainer sought! On Mon, Mar 11, 2002 at 02:18:25PM -0700, Sterin, Ilya wrote: K, looks like it has fairly enough info. I can take it unless someone else really insists:-) Looks like you're it! Do you need me to host it? No thanks, it should be part of dbi.perl.org. The DBI/FAQ.pm shipped with the DBI is the master and is what needs updating. Sending me a patch would be a fine way to do that. I suggest that you post a message asking people to suggest FAQ's (directly to you rather than the list). That, plus some trawling of the archives should give you ample material! But don't try to achieve perfection first time out. Just a decent update with the *most* Frequently Asked Qusetions's would be wonderful! Many thanks Ilya! Tim. Ilya -Original Message- From: Tim Bunce To: [EMAIL PROTECTED] Cc: Chris Pepper; [EMAIL PROTECTED] Sent: 3/11/02 2:04 PM Subject: DBI FAQ maintainer sought! So, anyone want to volunteer?! Tim. On Mon, Mar 11, 2002 at 11:38:13AM -0700, [EMAIL PROTECTED] wrote: I work for Sun Microsystems and can tell you that this is a known issue for two reasons: 1. As you stated the cc command is a stub in Solaris until you pay for and install the appropriate package. 2. Perl is packaged and delivered with Solaris 8. Obviously unless you have cc installed then you will have to re-compile Perl as you won't have the same compiler as Sun had when compiling it for Solaris, which I believe is a requirement of DBI. I agree this is a good FAQ! Mike Key Chris Pepper wrote: Gents, We're having some trouble building DBI under Solaris 8, and I think our problem is a good FAQ candidate. When following your build procedure, we're getting hung up because it wants to use cc (which is present but disabled on our Solaris 8 system, because we haven't paid for the license). We have a working gcc installation, but I think we're going to have to rebuild perl from source with gcc, so DBI can be built using gcc. If this is a known problem, please add it to the FAQ. Thanks, Chris Pepper -- Chris Pepper: http://www.reppep.com/~pepper/ Rockefeller University: http://www.rockefeller.edu/
RE: need some newbie logic help on Insert/Update
On Tue, 12 Mar 2002, James.FitzGibbon wrote:
How big is the table? i.e. if this is happening nightly, is it
prohibitively expensive to do this:
The table is pretty small
my $users_in_mysql = $mysql_dbh-selectall_hashref(SELECT ID FROM
SOME_TABLE, ID);
while( ($first_name, $id) = $sel-fetch ) {
if( exists $users_in_mysql-{$id} ) {
# do update
} else {
# do insert
}
}
That should work. I got several replies in the last 20 minutes. Some
other suggestions that should work as well:
1) Use REPLACE instead of INSERT/UPDATE. That is MySQL specific but will
work great for me
2) Use the UPDATE inside my loop and then check the return value to see if
the row got updated. If not, use the INSERT.
I'll do some testing on these ideas.
Thanks everybody!
Tim
Tim Carlson
Voice: (509) 376 3423
Email: [EMAIL PROTECTED]
EMSL UNIX System Support
RE: MS Access/CGI/Apache on WinNT questions
Jeff, In answer to your other questions: What version of DBD::ODBC are you using? PPMquery DBI DBI [1.201] Database independent interface for Perl Are you using MS-Access security? I am not using mdw files, if that is what you mean, but I do have the mdb file passworded. Does the web server user have access to the admin database? I believe so, unless Apache web server has a different user than my NT network login. I use a discarded low end NT machine for development. I am logged into the machine as myself when I (re)start Apache. Pete
DBD::Sybase bug
I've just been informed of a potentially nasty bug with DBD::Sybase
when using placeholders in conjunction with a LIKE clause.
For example:
use DBI;
my $param = shift;
my $dbh = DBI-connect('dbi:Sybase:database=testdb', 'sa', '');
my $sth = $dbh-prepare(select * from CAL where CAL_DS like ? + '%');
$sth-execute($param);
while(my $d = $sth-fetch) {
print @$d\n;
}
Assume a CAL table with the following rows:
id CAL_DS
1 A
2 AM
3 AMS
4 AMSTEL
5 DSTEL
Running the script with any argument starting with 'A' will return the
first 5 rows (so $sth-execute('ABC') and $sth-execute('A') have the
same effect).
I've traced this to a bug in the Sybase OpenClient libraries, where
the input parameter description returns a parameter length of 1 for
this query, even though the CAL column is a varchar(20).
The only work-around that I can think of is to move the '%' character
from the SQL string to the parameter (i.e. $sth-execute('AMS%'))
which will work as expected, but is not a generic solution for LIKE
queries if you always want to have the wildcard in the query.
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] *or* [EMAIL PROTECTED]
http://www.mbay.net/~mpeppler
International Sybase User Group: http://www.isug.com
DBI FAQ for Driver specific (was RE: DBI FAQ maintainer sought!)
Great, I'll get a message out tonight, to the list as well as possibly some newsgroup, search the archive, etc... Ilya Ilya -- thanks for volunteering! All -- Keep in mind that I haven't looked at the DBI FAQ for some time...and guessing from the list, many others don't either :( For the long term, can we build/organize the FAQ to deal with driver (or even platform) specific areas. 1) Provide DBI users with a one-stop-shop for FAQ information. 2) There may be something that applies to one driver and applies to another (i.e. Win32 web server permissions for files via DBD::ODBC, DBD::ADO, DBD::CSV and other drivers which are file based. Thoughts/comments? Regards, Jeff
RE: MS Access/CGI/Apache on WinNT questions
What version of DBD::ODBC are you using? What version of the Access driver are you using? When you copied to the local web server, what was the DSN you used? Thanks, Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 12:21 PM To: [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Hi Jeff: This is a snippet of the code: use DBI; my $dsn = driver=Microsoft Access Driver (*.mdb);dbq=nrent01\\fssdata\\Roster03b.mdb; DBI-trace(5, c:/trace.txt); my $dbh = DBI-connect(DBI:ODBC:$dsn,admin,xyzzy) || print BRError Opening Database: $DBI::errstr\n; my $sth = $dbh-prepare(SELECT [LNAME], [FNAME], [MI] FROM T_EMPLOYEES) || print Error in Prepare: $DBI::errstr\n; $sth-execute() || print BRError: $DBI::errstr\n; } This is what I get: DBI 1.201-nothread dispatch trace level set to 5 - DBI-connect(DBI:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=\\nrent01\fssdata\Roster03b.mdb, admin, ) - DBI-install_driver(ODBC) for MSWin32 perl=5.006001 pid=135 ruid=0 euid=0 install_driver: DBD::ODBC version 0.28 loaded from d:/Perl/site/lib/DBD/ODBC.pm New DBI::dr (for DBD::ODBC::dr, parent=, id=) dbih_setup_handle(DBI::dr=HASH(0x1a31908)=DBI::dr=HASH(0x191aa94), DBD::ODBC::dr, 0, Null!) dbih_make_com(Null!, DBD::ODBC::dr, 92) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Err, Null!) SCALAR(0x1b79f18) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), State, Null!) SCALAR(0x1b79f60) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Errstr, Null!) SCALAR(0x1b79f3c) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Handlers, Null!) ARRAY(0x191aa10) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Debug, Null!) 0 (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), FetchHashKeyName, Null!) 'NAME' (already defined) - install_driver= DBI::dr=HASH(0x1a31908) - connect for DBD::ODBC::dr (DBI::dr=HASH(0x1a31908)~0x191aa94 'driver=Microsoft Access Driver (*.mdb);dbq=\\nrent01\fssdata\Roster03b.mdb' 'admin' HASH(0x191ef94)) Also, I get a Dr. Watson on the NT machine saying Perl.exe choked. Any suggestions? Thanks, Pete -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 11:35 AM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Pete, Right before the connect, do the following: DBI-trace(5, c:/trace.txt); Then, send me the trace.txt from the web server's C: drive. (and, look at it yourself to see if the error is recognizable). Some other questions... What version of DBD::ODBC are you using? Are you using MS-Access security? Does the web server user have access to the admin database? Regards, Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 10:05 AM To: [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Jeff, You are correct that it does work from the command line. The share name fssdata share is accessible to me, but just to verify that something funky isn't going on, I copied the mdb file over to the web server and tried to run it from there. I still got the same message. As a newbie to perl and apache, I took on a task of trying to set up a web application to cut my teeth. The existing application has proven all too successful and the user population has outgrown MS Access. An Oracle server is in the works, but until then, I need to get it up and running using MS Access. I Know that this question is perl-101, but, given my inexperience with perl, what do you mean by ...set a trace file and set the DBI tracing level to 4...? Thanks, Pete MacGown -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 9:22 PM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions That, unfortunately, can be a bad message because of the way that DBD::ODBC handles opening the database. What's happening is that DBI can't open the database using the DSN-less style open (SQLDriverConnect) which fails and then tries to call SQLConnect, which expects a 32? character or less DSN. I hope to make this better in the future... In the mean time, I'm going to guess that this may work from the command line and not from the CGI. If that's not the case, this may or may not apply. Check the share name fssdata and ensure it's accessible from the username starting the web service (make sure the web server is not being started as local system, either, as that will have NO access to the network). IF that doesn't work, set a trace file
RE: DBI FAQ for Driver specific (was RE: DBI FAQ maintainer sought!)
I agree, definitely. I'm going to look tonight and see how I can organize/rearrange stuff so that everything is grouped. Don't want to have specific FAQs mixed in with general DBI, etc... It will be more heading based. So there will be a general DBI header, then DBDs, then platform specific stuff. Let me know if you have some other ideas/comments. Ilya -Original Message- From: Jeff Urlwin To: Sterin, Ilya; 'Tim Bunce ' Cc: ''Chris Pepper ' '; [EMAIL PROTECTED] Sent: 3/12/02 11:28 AM Subject: DBI FAQ for Driver specific (was RE: DBI FAQ maintainer sought!) Great, I'll get a message out tonight, to the list as well as possibly some newsgroup, search the archive, etc... Ilya Ilya -- thanks for volunteering! All -- Keep in mind that I haven't looked at the DBI FAQ for some time...and guessing from the list, many others don't either :( For the long term, can we build/organize the FAQ to deal with driver (or even platform) specific areas. 1) Provide DBI users with a one-stop-shop for FAQ information. 2) There may be something that applies to one driver and applies to another (i.e. Win32 web server permissions for files via DBD::ODBC, DBD::ADO, DBD::CSV and other drivers which are file based. Thoughts/comments? Regards, Jeff
RE: MS Access/CGI/Apache on WinNT questions
-Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 1:32 PM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions What version of DBD::ODBC are you using? PPM query ODBC DBD-ODBC [0.28] ODBC driver for the DBI module. When I do a verify, all the packages are up to date. What version of the Access driver are you using? 4.00.4202.00 The database is from MS Office, MS Access 97 When you copied to the local web server, what was the DSN you used? I tried two ways: d:\inetpub\wwwroot\Roster\roster03b.mdb and \\wilntws111\inetpub\wwwroot\Roster\roster03b.mdb where \\wilntws111 is the PC on the network, and \inetpub is a share on that machine. Thanks, Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 12:21 PM To: [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Hi Jeff: This is a snippet of the code: use DBI; my $dsn = driver=Microsoft Access Driver (*.mdb);dbq=nrent01\\fssdata\\Roster03b.mdb; DBI-trace(5, c:/trace.txt); my $dbh = DBI-connect(DBI:ODBC:$dsn,admin,xyzzy) || print BRError Opening Database: $DBI::errstr\n; my $sth = $dbh-prepare(SELECT [LNAME], [FNAME], [MI] FROM T_EMPLOYEES) || print Error in Prepare: $DBI::errstr\n; $sth-execute() || print BRError: $DBI::errstr\n; } This is what I get: DBI 1.201-nothread dispatch trace level set to 5 - DBI-connect(DBI:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=\\nrent01\fssdata\Roster03b.mdb, admin, ) - DBI-install_driver(ODBC) for MSWin32 perl=5.006001 pid=135 ruid=0 euid=0 install_driver: DBD::ODBC version 0.28 loaded from d:/Perl/site/lib/DBD/ODBC.pm New DBI::dr (for DBD::ODBC::dr, parent=, id=) dbih_setup_handle(DBI::dr=HASH(0x1a31908)=DBI::dr=HASH(0x191aa94), DBD::ODBC::dr, 0, Null!) dbih_make_com(Null!, DBD::ODBC::dr, 92) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Err, Null!) SCALAR(0x1b79f18) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), State, Null!) SCALAR(0x1b79f60) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Errstr, Null!) SCALAR(0x1b79f3c) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Handlers, Null!) ARRAY(0x191aa10) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Debug, Null!) 0 (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), FetchHashKeyName, Null!) 'NAME' (already defined) - install_driver= DBI::dr=HASH(0x1a31908) - connect for DBD::ODBC::dr (DBI::dr=HASH(0x1a31908)~0x191aa94 'driver=Microsoft Access Driver (*.mdb);dbq=\\nrent01\fssdata\Roster03b.mdb' 'admin' HASH(0x191ef94)) Also, I get a Dr. Watson on the NT machine saying Perl.exe choked. Any suggestions? Thanks, Pete -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 11:35 AM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Pete, Right before the connect, do the following: DBI-trace(5, c:/trace.txt); Then, send me the trace.txt from the web server's C: drive. (and, look at it yourself to see if the error is recognizable). Some other questions... What version of DBD::ODBC are you using? Are you using MS-Access security? Does the web server user have access to the admin database? Regards, Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 10:05 AM To: [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Jeff, You are correct that it does work from the command line. The share name fssdata share is accessible to me, but just to verify that something funky isn't going on, I copied the mdb file over to the web server and tried to run it from there. I still got the same message. As a newbie to perl and apache, I took on a task of trying to set up a web application to cut my teeth. The existing application has proven all too successful and the user population has outgrown MS Access. An Oracle server is in the works, but until then, I need to get it up and running using MS Access. I Know that this question is perl-101, but, given my inexperience with perl, what do you mean by ...set a trace file and set the DBI tracing level to 4...? Thanks, Pete MacGown -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Monday, March 11, 2002 9:22 PM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions That, unfortunately, can be a bad message because of the way that DBD::ODBC handles opening the database.
Re: need some newbie logic help on Insert/Update
Catching errors on Oracle is really, really slow. How about a BEGIN block that attempts to select the record and does this logic in SQL? DECLARE CURSOR c IS select /*+ FIRST_ROWS */ rowid from Table WHERE key condition; r ROWID; BEGIN OPEN c; FETCH c INTO r; IF c%NOTFOUND THEN INSERT INTO Table (..Values..)'; ELSE UPDATE Table SET column=? WHERE key=? END IF; CLOSE c; END;
RE: need some newbie logic help on Insert/Update
Tom Price wrote: Catching errors on Oracle is really, really slow. How about a BEGIN block that attempts to select the record and does this logic in SQL? Well, it's my DB2 experience/Oracle ignorance that was showing. It's true though, that you will pay a price for attempting the insert and then catching the exception. The select is probably a better route, simply because you don't force the error condition. Additionally it will work where there is no primary key constraint for the target table, where as my suggestion would just blindly insert a duplicate row (or key). Regarding Tim's original question, however, I thought he was trying to do the insert into the MySql database, so I'm not sure that Oracle error handler will impact him much. Does MySql support cursors for updates? Stph
RE: need some newbie logic help on Insert/Update
On Tue, 12 Mar 2002, Stephen Keller wrote: Regarding Tim's original question, however, I thought he was trying to do the insert into the MySql database, so I'm not sure that Oracle error handler will impact him much. Does MySql support cursors for updates? I replied off-line noting that I wasn't going to get any errors out of Oracle since I was just doing a SELECT * and my errors would come from INSERTS into MySQL. That being said, I'm now torn between using a REPLACE vs the other suggestions. I think I would like to keep things as generic as possible since I am cursing at the person who last had this code when it was used for an Oracle to Oracle dump and he used Oraperl. Thanks again for the suggestions. I'm not a DBA.. I just play one at work on rare occasions. :) Tim Tim Carlson Voice: (509) 376 3423 Email: [EMAIL PROTECTED] EMSL UNIX System Support
what's wrong with my code? Thanks
Hi, there, Here is my code. When I ran it, something is not right. my $qstr = qq #SELECT subject,group_group,submitter,TO_CHAR(close_time, 'MM/DD/YY HH24:MI'),description,comments,eta,noc,status,notification_id,master FROM notification WHERE status = 'Active' and group_group = ? UNION select subject,group_group,submitter,TO_CHAR(close_time, 'MM/DD/YY HH24:MI'),description,comments,eta,noc,status,notification_id,master FROM notification WHERE group_group = ? and ROWNUM = 500 ORDER BY close_time DESC#; $list = $dbh-prepare($qstr) or die Couldn't prepare statement: . $DBI-errstr(); $list-execute($group) or die Couldn't execute statement: . $DBI-errstr(); I want the rownum =500 order by close_time desc to act as the condition of the second select statement, which means to be execute before the union. The erroer message I got is DBD::Oracle::db prepare failed: ORA-00904: invalid column name (DBD ERROR: OCIStmtExecute/Describe). I checked several times and couldn't find anything wrong with the column name. When I took the rownum =500 order by close_time desc off, it works ok. But when I added it, it went wrong. Could somebody give me a hint about what may cause the problem? I am really appreciate it. He
RE: what's wrong with my code? Thanks
What is ROWNUM? If there is support for it, might want to make sure that it's supported by your db version. I know some oracle versions support it, but not sure. Best way to test is to run in sql plus first. Ilya -Original Message- From: He Hong To: [EMAIL PROTECTED] Sent: 3/12/02 1:20 PM Subject: what's wrong with my code? Thanks Hi, there, Here is my code. When I ran it, something is not right. my $qstr = qq #SELECT subject,group_group,submitter,TO_CHAR(close_time, 'MM/DD/YY HH24:MI'),description,comments,eta,noc,status,notification_id,master FROM notification WHERE status = 'Active' and group_group = ? UNION select subject,group_group,submitter,TO_CHAR(close_time, 'MM/DD/YY HH24:MI'),description,comments,eta,noc,status,notification_id,master FROM notification WHERE group_group = ? and ROWNUM = 500 ORDER BY close_time DESC#; $list = $dbh-prepare($qstr) or die Couldn't prepare statement: .. $DBI-errstr(); $list-execute($group) or die Couldn't execute statement: . $DBI-errstr(); I want the rownum =500 order by close_time desc to act as the condition of the second select statement, which means to be execute before the union. The erroer message I got is DBD::Oracle::db prepare failed: ORA-00904: invalid column name (DBD ERROR: OCIStmtExecute/Describe). I checked several times and couldn't find anything wrong with the column name. When I took the rownum =500 order by close_time desc off, it works ok. But when I added it, it went wrong. Could somebody give me a hint about what may cause the problem? I am really appreciate it. He
Re: DBI FAQ for Driver specific (was RE: DBI FAQ maintainer sought!)
On Tue, Mar 12, 2002 at 01:28:18PM -0500, Jeff Urlwin wrote: Great, I'll get a message out tonight, to the list as well as possibly some newsgroup, search the archive, etc... Ilya Ilya -- thanks for volunteering! All -- Keep in mind that I haven't looked at the DBI FAQ for some time...and guessing from the list, many others don't either :( For the long term, can we build/organize the FAQ to deal with driver (or even platform) specific areas. 1) Provide DBI users with a one-stop-shop for FAQ information. 2) There may be something that applies to one driver and applies to another (i.e. Win32 web server permissions for files via DBD::ODBC, DBD::ADO, DBD::CSV and other drivers which are file based. Thoughts/comments? Good points but, like I said earlier, I'd urge it kept simple for the next revision because hopefully we'll be using FAQ-O-Matic soon and I'd hate to see excess effort on a text document go to waste. But then I'd hate you guys to loose momentum either, so just do whatever you're comfortable with Ilya! Tim.
Re: what's wrong with my code? Thanks
Yeah, I replaced the column name with 4, that error was gone, but a new error message was generated, which is execute called with 1 bind variables when 2 are needed The $list-execute($group) is asking for two parameters inside the parentless, which is weird. Any idea how this happens? Thanks very much. He At 12:48 PM 3/12/02 -0800, Ravishankar Narasimhan wrote: When you use a Union you need to have by order by 1,2 instead of column_name. --- He Hong [EMAIL PROTECTED] wrote: Hi, there, Here is my code. When I ran it, something is not right. my $qstr = qq #SELECT subject,group_group,submitter,TO_CHAR(close_time, 'MM/DD/YY HH24:MI'),description,comments,eta,noc,status,notification_id,master FROM notification WHERE status = 'Active' and group_group = ? UNION select subject,group_group,submitter,TO_CHAR(close_time, 'MM/DD/YY HH24:MI'),description,comments,eta,noc,status,notification_id,master FROM notification WHERE group_group = ? and ROWNUM = 500 ORDER BY close_time DESC#; $list = $dbh-prepare($qstr) or die Couldn't prepare statement: . $DBI-errstr(); $list-execute($group) or die Couldn't execute statement: . $DBI-errstr(); I want the rownum =500 order by close_time desc to act as the condition of the second select statement, which means to be execute before the union. The erroer message I got is DBD::Oracle::db prepare failed: ORA-00904: invalid column name (DBD ERROR: OCIStmtExecute/Describe). I checked several times and couldn't find anything wrong with the column name. When I took the rownum =500 order by close_time desc off, it works ok. But when I added it, it went wrong. Could somebody give me a hint about what may cause the problem? I am really appreciate it. He __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/
Re: what's wrong with my code? Thanks
On Tue, Mar 12, 2002 at 03:58:44PM -0500, Hong He wrote: Yeah, I replaced the column name with 4, that error was gone, but a new error message was generated, which is execute called with 1 bind variables when 2 are needed The $list-execute($group) is asking for two parameters inside the parentless, which is weird. Any idea how this happens? Your SQL contains two placeholders. In this case, you probably want to bind the same value to both of them. $list-execute($group, $group); Ronald
RE: MS Access/CGI/Apache on WinNT questions
Peter, Did you escape the back-slashes? I.e. \\ instead of \? I will, tonight, upload a new DBI and DBD::ODBC for you which I'd like to see the output of trace. These will be PPD files, for use with PPM and that will get you up to the latest versions. Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 1:59 PM To: 'Jeff Urlwin'; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 1:32 PM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions What version of DBD::ODBC are you using? PPM query ODBC DBD-ODBC [0.28] ODBC driver for the DBI module. When I do a verify, all the packages are up to date. What version of the Access driver are you using? 4.00.4202.00 The database is from MS Office, MS Access 97 When you copied to the local web server, what was the DSN you used? I tried two ways: d:\inetpub\wwwroot\Roster\roster03b.mdb and \\wilntws111\inetpub\wwwroot\Roster\roster03b.mdb where \\wilntws111 is the PC on the network, and \inetpub is a share on that machine. Thanks, Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 12:21 PM To: [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Hi Jeff: This is a snippet of the code: use DBI; my $dsn = driver=Microsoft Access Driver (*.mdb);dbq=nrent01\\fssdata\\Roster03b.mdb; DBI-trace(5, c:/trace.txt); my $dbh = DBI-connect(DBI:ODBC:$dsn,admin,xyzzy) || print BRError Opening Database: $DBI::errstr\n; my $sth = $dbh-prepare(SELECT [LNAME], [FNAME], [MI] FROM T_EMPLOYEES) || print Error in Prepare: $DBI::errstr\n; $sth-execute() || print BRError: $DBI::errstr\n; } This is what I get: DBI 1.201-nothread dispatch trace level set to 5 - DBI-connect(DBI:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=\\nrent01\fssdata\Roster03b.mdb, admin, ) - DBI-install_driver(ODBC) for MSWin32 perl=5.006001 pid=135 ruid=0 euid=0 install_driver: DBD::ODBC version 0.28 loaded from d:/Perl/site/lib/DBD/ODBC.pm New DBI::dr (for DBD::ODBC::dr, parent=, id=) dbih_setup_handle(DBI::dr=HASH(0x1a31908)=DBI::dr=HASH(0x191aa94), DBD::ODBC::dr, 0, Null!) dbih_make_com(Null!, DBD::ODBC::dr, 92) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Err, Null!) SCALAR(0x1b79f18) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), State, Null!) SCALAR(0x1b79f60) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Errstr, Null!) SCALAR(0x1b79f3c) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Handlers, Null!) ARRAY(0x191aa10) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Debug, Null!) 0 (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), FetchHashKeyName, Null!) 'NAME' (already defined) - install_driver= DBI::dr=HASH(0x1a31908) - connect for DBD::ODBC::dr (DBI::dr=HASH(0x1a31908)~0x191aa94 'driver=Microsoft Access Driver (*.mdb);dbq=\\nrent01\fssdata\Roster03b.mdb' 'admin' HASH(0x191ef94)) Also, I get a Dr. Watson on the NT machine saying Perl.exe choked. Any suggestions? Thanks, Pete -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 11:35 AM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Pete, Right before the connect, do the following: DBI-trace(5, c:/trace.txt); Then, send me the trace.txt from the web server's C: drive. (and, look at it yourself to see if the error is recognizable). Some other questions... What version of DBD::ODBC are you using? Are you using MS-Access security? Does the web server user have access to the admin database? Regards, Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 10:05 AM To: [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Jeff, You are correct that it does work from the command line. The share name fssdata share is accessible to me, but just to verify that something funky isn't going on, I copied the mdb file over to the web server and tried to run it from there. I still got the same message. As a newbie to perl and apache, I took on a task of trying to set up a web application to cut my teeth. The existing application has proven all too successful and the user population has outgrown MS Access. An Oracle server is in the works, but until then, I need to get
Re: what's wrong with my code? Thanks
Thank you all. You are right. The problem is fixed. One million thanks. He At 04:09 PM 3/12/02 -0500, Ronald J Kimball wrote: On Tue, Mar 12, 2002 at 03:58:44PM -0500, Hong He wrote: Yeah, I replaced the column name with 4, that error was gone, but a new error message was generated, which is execute called with 1 bind variables when 2 are needed The $list-execute($group) is asking for two parameters inside the parentless, which is weird. Any idea how this happens? Your SQL contains two placeholders. In this case, you probably want to bind the same value to both of them. $list-execute($group, $group); Ronald
driver problem
Begin forwarded message: hey guys, i'm installing DBI from cpan on my mac os x. everything seemed fine however i can't use the module because the mysql.pm was never installed. i've searched the readmes and groups to no avail, it looks like i'm supposed to fetch it from somewhere. can you help me. thanks. danny
RE: driver problem
No, no fetching, you are supposed to install it the same way you installed DBI. The module name is DBD::MySQL and it is on CPAN. You will have to have MySQL client libs installed before you can compile it. Ilya -Original Message- From: danny To: [EMAIL PROTECTED] Sent: 3/12/02 2:25 PM Subject: driver problem Begin forwarded message: hey guys, i'm installing DBI from cpan on my mac os x. everything seemed fine however i can't use the module because the mysql.pm was never installed. i've searched the readmes and groups to no avail, it looks like i'm supposed to fetch it from somewhere. can you help me. thanks. danny
RE: MS Access/CGI/Apache on WinNT questions
Jeff, I use notepad to modify the CGI scripts. I type in \\ so perl will interpret this as a single \. This works for the command line perl. I just can't get it to work on the web version. I won't be into work tomorrow, so won't be able to get the latest versions of DBI until Thursday. I was looking at Win32-ODBC, but I'd rather use DBI and DBD::ODBC because I will end up using it anyway. Thanks, Pete -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 4:12 PM To: MacGown, Peter; 'Jeff Urlwin'; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Peter, Did you escape the back-slashes? I.e. \\ instead of \? I will, tonight, upload a new DBI and DBD::ODBC for you which I'd like to see the output of trace. These will be PPD files, for use with PPM and that will get you up to the latest versions. Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 1:59 PM To: 'Jeff Urlwin'; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 1:32 PM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions What version of DBD::ODBC are you using? PPM query ODBC DBD-ODBC [0.28] ODBC driver for the DBI module. When I do a verify, all the packages are up to date. What version of the Access driver are you using? 4.00.4202.00 The database is from MS Office, MS Access 97 When you copied to the local web server, what was the DSN you used? I tried two ways: d:\inetpub\wwwroot\Roster\roster03b.mdb and \\wilntws111\inetpub\wwwroot\Roster\roster03b.mdb where \\wilntws111 is the PC on the network, and \inetpub is a share on that machine. Thanks, Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 12:21 PM To: [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Hi Jeff: This is a snippet of the code: use DBI; my $dsn = driver=Microsoft Access Driver (*.mdb);dbq=nrent01\\fssdata\\Roster03b.mdb; DBI-trace(5, c:/trace.txt); my $dbh = DBI-connect(DBI:ODBC:$dsn,admin,xyzzy) || print BRError Opening Database: $DBI::errstr\n; my $sth = $dbh-prepare(SELECT [LNAME], [FNAME], [MI] FROM T_EMPLOYEES) || print Error in Prepare: $DBI::errstr\n; $sth-execute() || print BRError: $DBI::errstr\n; } This is what I get: DBI 1.201-nothread dispatch trace level set to 5 - DBI-connect(DBI:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=\\nrent01\fssdata\Roster03b.mdb, admin, ) - DBI-install_driver(ODBC) for MSWin32 perl=5.006001 pid=135 ruid=0 euid=0 install_driver: DBD::ODBC version 0.28 loaded from d:/Perl/site/lib/DBD/ODBC.pm New DBI::dr (for DBD::ODBC::dr, parent=, id=) dbih_setup_handle(DBI::dr=HASH(0x1a31908)=DBI::dr=HASH(0x191aa94), DBD::ODBC::dr, 0, Null!) dbih_make_com(Null!, DBD::ODBC::dr, 92) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Err, Null!) SCALAR(0x1b79f18) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), State, Null!) SCALAR(0x1b79f60) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Errstr, Null!) SCALAR(0x1b79f3c) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Handlers, Null!) ARRAY(0x191aa10) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Debug, Null!) 0 (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), FetchHashKeyName, Null!) 'NAME' (already defined) - install_driver= DBI::dr=HASH(0x1a31908) - connect for DBD::ODBC::dr (DBI::dr=HASH(0x1a31908)~0x191aa94 'driver=Microsoft Access Driver (*.mdb);dbq=\\nrent01\fssdata\Roster03b.mdb' 'admin' HASH(0x191ef94)) Also, I get a Dr. Watson on the NT machine saying Perl.exe choked. Any suggestions? Thanks, Pete -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 11:35 AM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Pete, Right before the connect, do the following: DBI-trace(5, c:/trace.txt); Then, send me the trace.txt from the web server's C: drive. (and, look at it yourself to see if the error is recognizable). Some other questions... What version of DBD::ODBC are you using? Are you using MS-Access security? Does the web server user have access to the admin database? Regards, Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 10:05 AM To: [EMAIL PROTECTED] Subject: RE: MS
Re: Host Arrays
Keep in mind that the cache works only in the DBMS-to-client
direction (correct ?) So INSERT/UPDATE/DELETE and in particular
the SELECT stmt given below will not likely benefit, ie, any time
you're sending placeholder data to the DBMS, it still requires
tuple-at-a-time processing.
Array binding is in the works, and I have a DBD::ODBC hack
(check the archives) I can forward if you're willing to use
experimental, unsupported stuff.
Dean Arnold
- Original Message -
From: James.FitzGibbon [EMAIL PROTECTED]
To: 'Schoenenberger Peter' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, March 12, 2002 8:21 AM
Subject: RE: Host Arrays
Look back in the archives. I asked this same question back in
January (the thread starts on Jan 24th). In short, yes. Fiddle
with $dbh-{RowCacheSize} to get what you want. If you set it to
200, then when you do your first fetch you will get 200 rows from
Oracle, and the next 199 fetches will be fulfilled from local memory,
not over the network.
--
j.
James FitzGibbon voice/fax 612-761-6121/4277
Consultant, TTS-3D@TPN4H [EMAIL PROTECTED]
-Original Message-
From: Schoenenberger Peter [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 12, 2002 10:02 AM
To: '[EMAIL PROTECTED]'
Subject: Host Arrays
Hello everyone
Is there a way to use the host array interface of oracle 8i
in perl dbi?
With arrays, you manipulate an entire array with a single SQL
statement.
Thus, Oracle communication overhead is reduced markedly,
especially in a
networked environment. A major portion of runtime is spent on network
roundtrips between the client program and the server
database. Arrays reduce
the roundtrips.
For example, suppose you want to insert information about 300
employees into
the EMP table. Without arrays your program must do 300 individual
INSERTs--one for each employee. With arrays, only one INSERT
needs to be
done. Or you want to select many records from the database.
For example: With embedded SQL in C you can do the following:
-
char emp_name[1000];
intemp_number[1000];
float salary[1000];
EXEC SQL SELECT ENAME, EMPNO, SAL
INTO :emp_name, :emp_number, :salary
FROM EMP
WHERE SAL 2000;
--
All data (maximum 1000) will come in one stream over the
network to your
client.
I have tried the select with the fetchall_arrayref in perl,
but behind the
scenery multiple fetches are produced and there is no reduce
of roundtrips
between the client and the database.
Thanks,
Peter
RE: MS Access/CGI/Apache on WinNT questions
Sounds good so far. Let's try these: from ppm, you should be able to: install ftp://ftp.esoftmatic.com/outgoing/DBI/DBI.ppd install ftp://ftp.esoftmatic.com/outgoing/DBI/DBD-ODBC.ppd If not, ftp to ftp::/ftp.esoftmatic.com/outgoing/DBI/ and download all the files into, say, c:\tmp and then install c:\tmp\DBI.ppd, etc Let me know if you have issues. Thanks, Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 5:11 PM To: 'Jeff Urlwin'; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Jeff, I use notepad to modify the CGI scripts. I type in \\ so perl will interpret this as a single \. This works for the command line perl. I just can't get it to work on the web version. I won't be into work tomorrow, so won't be able to get the latest versions of DBI until Thursday. I was looking at Win32-ODBC, but I'd rather use DBI and DBD::ODBC because I will end up using it anyway. Thanks, Pete -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 4:12 PM To: MacGown, Peter; 'Jeff Urlwin'; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Peter, Did you escape the back-slashes? I.e. \\ instead of \? I will, tonight, upload a new DBI and DBD::ODBC for you which I'd like to see the output of trace. These will be PPD files, for use with PPM and that will get you up to the latest versions. Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 1:59 PM To: 'Jeff Urlwin'; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 1:32 PM To: MacGown, Peter; [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions What version of DBD::ODBC are you using? PPM query ODBC DBD-ODBC [0.28] ODBC driver for the DBI module. When I do a verify, all the packages are up to date. What version of the Access driver are you using? 4.00.4202.00 The database is from MS Office, MS Access 97 When you copied to the local web server, what was the DSN you used? I tried two ways: d:\inetpub\wwwroot\Roster\roster03b.mdb and \\wilntws111\inetpub\wwwroot\Roster\roster03b.mdb where \\wilntws111 is the PC on the network, and \inetpub is a share on that machine. Thanks, Jeff -Original Message- From: MacGown, Peter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 12:21 PM To: [EMAIL PROTECTED] Subject: RE: MS Access/CGI/Apache on WinNT questions Hi Jeff: This is a snippet of the code: use DBI; my $dsn = driver=Microsoft Access Driver (*.mdb);dbq=nrent01\\fssdata\\Roster03b.mdb; DBI-trace(5, c:/trace.txt); my $dbh = DBI-connect(DBI:ODBC:$dsn,admin,xyzzy) || print BRError Opening Database: $DBI::errstr\n; my $sth = $dbh-prepare(SELECT [LNAME], [FNAME], [MI] FROM T_EMPLOYEES) || print Error in Prepare: $DBI::errstr\n; $sth-execute() || print BRError: $DBI::errstr\n; } This is what I get: DBI 1.201-nothread dispatch trace level set to 5 - DBI-connect(DBI:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=\\nrent01\fssdata\Roster03b.mdb, admin, ) - DBI-install_driver(ODBC) for MSWin32 perl=5.006001 pid=135 ruid=0 euid=0 install_driver: DBD::ODBC version 0.28 loaded from d:/Perl/site/lib/DBD/ODBC.pm New DBI::dr (for DBD::ODBC::dr, parent=, id=) dbih_setup_handle(DBI::dr=HASH(0x1a31908)=DBI::dr=HASH(0x191aa94), DBD::ODBC::dr, 0, Null!) dbih_make_com(Null!, DBD::ODBC::dr, 92) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Err, Null!) SCALAR(0x1b79f18) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), State, Null!) SCALAR(0x1b79f60) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Errstr, Null!) SCALAR(0x1b79f3c) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Handlers, Null!) ARRAY(0x191aa10) (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), Debug, Null!) 0 (already defined) dbih_setup_attrib(DBI::dr=HASH(0x191aa94), FetchHashKeyName, Null!) 'NAME' (already defined) - install_driver= DBI::dr=HASH(0x1a31908) - connect for DBD::ODBC::dr (DBI::dr=HASH(0x1a31908)~0x191aa94 'driver=Microsoft Access Driver (*.mdb);dbq=\\nrent01\fssdata\Roster03b.mdb' 'admin' HASH(0x191ef94)) Also, I get a Dr. Watson on the NT machine saying Perl.exe choked. Any suggestions? Thanks, Pete -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March
Blank Pages ( Revisited )
In the past someone posted a problem with mod_perl and DbI giving blank
pages from a SQL query. I did some digging around and found some info but I
need someone to fill in the holes for me a little.
I created a DB Table webtest. In the table, I filled it with the contents
of /usr/local/dict/propernames. Fine! it worked beautifully.
However, when I do a random query I get some blank pages. I traced this to
the DBI Log using the DBH-Trace(2, /tmp/DBI.log). I found that sometimes
the execute() returns 0E0. Does anybody knows why??
I did a simple logic to check for the value return by execute(), if the
value is 0E0 then do the same query. Do this same query until the value
return by execute() is not 0E0. However, I am still getting blank pages.
I am sending the actual script that I have been working on to provide a
complete picture.
Thank you in advanced
-max
SCRIPT==
1 package DBNE2;
2
3 use strict;
4 use vars qw($DBH);
5 use Apache;
6 use DBI();
7
8 sub handler {
9 $DBH = DBI-connect(DBI:mysql:test,webuser,mult1scan)
|| die $DBH-errstr;
10 my $r = shift;
11 my $h = $DBH-trace(2,/tmp/DBNE2.log);
12
13 my $IdHandle = $DBH-prepare(SELECT MAX(id) FROM webtest);
14 $IdHandle-execute;
15 my $id = $IdHandle-fetchrow;
16
17 my $RowHandlesth = $DBH-prepare(SELECT * FROM webtest WHERE
id = ROUND( (RAND() * ?) + 1));
18 $RowHandlesth-execute($id);
19
20 while (! $RowHandlesth ) {
21 $RowHandlesth-execute($id);
22
23 if ( $RowHandlesth ) {
24 # my row = $RowHandlesth-fetchrow;
25 last;
26 }
27 }
28 my row = $RowHandlesth-fetchrow;
29
30 $r-content_type(text/html);
31 $r-send_http_header;
32 $r-print(row);
33 $RowHandlesth-finish;
34 }
35
36 1;
Re: Blank Pages ( Revisited )
On Wed, 13 Mar 2002 [EMAIL PROTECTED] wrote:
Date: Wed, 13 Mar 2002 06:07:01 GMT
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Blank Pages ( Revisited )
In the past someone posted a problem with mod_perl and DbI giving blank
pages from a SQL query. I did some digging around and found some info but I
need someone to fill in the holes for me a little.
I created a DB Table webtest. In the table, I filled it with the contents
of /usr/local/dict/propernames. Fine! it worked beautifully.
However, when I do a random query I get some blank pages. I traced this to
the DBI Log using the DBH-Trace(2, /tmp/DBI.log). I found that sometimes
the execute() returns 0E0. Does anybody knows why??
I did a simple logic to check for the value return by execute(), if the
value is 0E0 then do the same query. Do this same query until the value
return by execute() is not 0E0. However, I am still getting blank pages.
I am sending the actual script that I have been working on to provide a
complete picture.
Thank you in advanced
A floating point 0 or 0E0 is sometimes returned since it will test as
True, when then number is unknown. Someone who undersands DBI's innards
may comment on if this is the case here.
-max
SCRIPT==
1 package DBNE2;
2
3 use strict;
4 use vars qw($DBH);
5 use Apache;
6 use DBI();
7
8 sub handler {
9 $DBH = DBI-connect(DBI:mysql:test,webuser,mult1scan)
|| die $DBH-errstr;
10 my $r = shift;
11 my $h = $DBH-trace(2,/tmp/DBNE2.log);
12
13 my $IdHandle = $DBH-prepare(SELECT MAX(id) FROM webtest);
14 $IdHandle-execute;
15 my $id = $IdHandle-fetchrow;
16
17 my $RowHandlesth = $DBH-prepare(SELECT * FROM webtest WHERE
id = ROUND( (RAND() * ?) + 1));
18 $RowHandlesth-execute($id);
19
20 while (! $RowHandlesth ) {
21 $RowHandlesth-execute($id);
22
23 if ( $RowHandlesth ) {
24 # my @row = $RowHandlesth-fetchrow;
25 last;
26 }
27 }
28 my @row = $RowHandlesth-fetchrow;
29
30 $r-content_type(text/html);
31 $r-send_http_header;
32 $r-print(@row);
33 $RowHandlesth-finish;
34 }
35
36 1;
Sincerely,
William Mussatto, Senior Systems Engineer
CyberStrategies, Inc
ph. 909-920-9154 ext. 27
Re: recordset
On Wednesday, March 6, 2002, at 04:51 PM, Vorce, Tim (T.) wrote:
I am interested in the module DBIx::Recordset, however, putting
good choice!
use DBIx::Recordset;
at the top of my script, an error occurs, as the module cannot be
located.
the precise error text is always a good thing to include
I think this is the module I need, how did I get it into the
package???
type perl -V to take a look a your @INC variable which shows
where Perl looks for modules. It searches along
@INC for DBIx/Recordset.pm
how did you install DBIx::Recordset? did you manually download it
or use CPAN.pm?
perl Makefile.PL
make
make test
make install
Tim Vorce
[EMAIL PROTECTED]
313-248-9985
$terrence = {
hobby = Class::MakeMethods - hacker (type = 'Just Another'),
addiction = 'Chess on ICC as princepawn',
job = 'seeking' ,
URL = 'http://www.metaperl.com'
};
