Re: execute
I prefer to raiserrors and catch them using eval, like this:
$dbh-{RaiseError} = 1;
$sth = $dbh-prepare($insert_sql); ### prepare error will cause die
foreach $one (@many) {
eval {
$sth-execute($one); ### execute error caught in eval's $@
};
if ($@) {
warn Error on Execute: $@;
# add other application on-error-clean-up code here
}
}
And for a specific problem like trying to insert a key that is
already there(unique constraint), you could make your error trapping
smarter(although in this case more database specific):
if ($@) {
if ($@ =~ /unique constraint/i) {
warn Warning on Execute - tried to insert a duplicate key: $@;
}
else {
die Fatal error on Execute: $@;
}
}
HTH.
--
Hardy Merrill
Mission Critical Linux, Inc.
http://www.missioncriticallinux.com
Sterin, Ilya [[EMAIL PROTECTED]] wrote:
Or an easier, more general way would be to turn off RaiseError
RaiseError = 1
By default PrintError is on, which instead of croaking on errors, calls
warn. You can then define the __WARN__ handler and only croak based on the
warn message contents.
One thing to remember, when you prepare if there is something wrong with the
prepare, it will not return a proper statement handle, so after you call
execute() it will still die telling you that you are trying to call a non
existant method on an undefined handle. The only way to catch these is to
use eval{}.
Ilya
-Original Message-
From: Adam Frielink [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 31, 2001 6:46 PM
To: [EMAIL PROTECTED]
Subject: RE: execute
My guess is that you are not catching the errors...
Try something like the following
$sth-execute($param1, $param2, $param3) or warn Execute Error:
$DBI::errstr\n;
Which will drop a warning message but will not terminate the application.
You could collect those messages and debu later.
Adam Frielink
Tyco Plastics
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 31, 2001 2:23 PM
To: [EMAIL PROTECTED]
Subject: execute
Hello, I am writing to beg the help of a problem I have
encountered. I am
doing a simple prepare (which does an insert) and execute
within a loop.
I am running this script parsing the same data so I am having the
expected
unique constraint errors. My script is bombing when the execute
encounters a single error but I would like it to continue onto the next
insert despite the error. Is this possible. Thanks,
Regards,
Joseph Teasdale
Enterprise Management Engineer
IT Masters, Inc.
o:512.256.1216 x131
c:512.422.0194
www.itmasters.com
Looping through recordset twice
Is there a way to back through a recordset after I've already gone through it? I want to go through the recordset, determining if certain groups of records meet my criteria. Then I want to go through again and print the groups that qualify. Since I have to deal with groups of records, I couldn't see how to just do this in the WHERE clause of the originating SQL. Thoughts? Don.
Re: Looping through recordset twice
I don't think so. Why don't you put the selected records in an auxiliary structure (hash, array) and then go through that structure? Saludos, Marcelo. Don Seiler Don.Seiler@Ce To: [EMAIL PROTECTED] llcom.com cc: Subject: Looping through recordset twice 01/11/2001 15:34 Please respond to Don Seiler Is there a way to back through a recordset after I've already gone through it? I want to go through the recordset, determining if certain groups of records meet my criteria. Then I want to go through again and print the groups that qualify. Since I have to deal with groups of records, I couldn't see how to just do this in the WHERE clause of the originating SQL. Thoughts? Don.
Re: Looping through recordset twice
I was considering this, but just thought it would be easier to just set the flag in an aux structure and loop through my original recordset again, checking the flag each time. Anyone know if there is a definitive yes/no on this? Thanks, Don. -- Don Seiler [EMAIL PROTECTED] Database Administrator / Sr Software Engineer NSightTel Billing LLCPhone: 920.617.7501 1580 Mid Valley DriveFax:920.617.7493 De Pere, WI 54115 Cell: 920.606.3240 Pager: [EMAIL PROTECTED] / 920.613.2000 On Thu, 1 Nov 2001, Marcelo Guelfi wrote: I don't think so. Why don't you put the selected records in an auxiliary structure (hash, array) and then go through that structure? Saludos, Marcelo. Don Seiler Don.Seiler@Ce To: [EMAIL PROTECTED] llcom.com cc: Subject: Looping through recordset twice 01/11/2001 15:34 Please respond to Don Seiler Is there a way to back through a recordset after I've already gone through it? I want to go through the recordset, determining if certain groups of records meet my criteria. Then I want to go through again and print the groups that qualify. Since I have to deal with groups of records, I couldn't see how to just do this in the WHERE clause of the originating SQL. Thoughts? Don.
RE: Looping through recordset twice
In Oracle database SQL The from clause can contain another sql statement as temp table. In which you can select the demanded records, then testify for the qualification in the main SQL. For example: Select a.column1, a.column2 From (select column1, column2 From table_name Where blah blah...) a Where a.whatever_column = 'whatever'; Not sure if this is what you are looking for. Alan Kong -Original Message- From: Marcelo Guelfi [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 01, 2001 1:50 PM To: Don Seiler Cc: [EMAIL PROTECTED] Subject: Re: Looping through recordset twice I don't think so. Why don't you put the selected records in an auxiliary structure (hash, array) and then go through that structure? Saludos, Marcelo. Don Seiler Don.Seiler@Ce To: [EMAIL PROTECTED] llcom.com cc: Subject: Looping through recordset twice 01/11/2001 15:34 Please respond to Don Seiler Is there a way to back through a recordset after I've already gone through it? I want to go through the recordset, determining if certain groups of records meet my criteria. Then I want to go through again and print the groups that qualify. Since I have to deal with groups of records, I couldn't see how to just do this in the WHERE clause of the originating SQL. Thoughts? Don.
Re: Looping through recordset twice
Actually the nature of the problem is what stopped me from doing this. I won't know which records I want until I look at the group of them. Example: I have a table of records. There is a groupnum column. Many records have the same groupnum, i.e. they are in the same group. I'm only interested in selecting the group as a whole. I will only know if I want this group based on examining all of the records for that group. I suppose I could put each record for that group into an array, then perform my calculation and print the array if I want to and then empty the array in either case. *shrug* Don. On Thu, 1 Nov 2001, Marcelo Guelfi wrote: I don't think so. Why don't you put the selected records in an auxiliary structure (hash, array) and then go through that structure? Saludos, Marcelo. Don Seiler Don.Seiler@Ce To: [EMAIL PROTECTED] llcom.com cc: Subject: Looping through recordset twice 01/11/2001 15:34 Please respond to Don Seiler Is there a way to back through a recordset after I've already gone through it? I want to go through the recordset, determining if certain groups of records meet my criteria. Then I want to go through again and print the groups that qualify. Since I have to deal with groups of records, I couldn't see how to just do this in the WHERE clause of the originating SQL. Thoughts? Don.
MS Access Too Few Parameters Error Help
Hello,
I am trying to execute an update to an Access database. The statement
has two placeholders, and I put two variables between the parentheses in
the execute statement, and yet Access tells me it expects 3 parameters.
This is the prepare:
my $sth_del = $dbh-prepare(
UPDATE [DBI_TEST]
SET retired_date = ?
WHERE person_id = ?) or die Cant prepare retired date: .
$dbh-errstr;
This is the execute:
foreach my $del_rec (@del_recs) {
$sth_del-execute( $ret_date, $del_rec ) or die Cant insert retired
date: . $dbh-errstr;
}
I really don't understand why it wants three parameters. What exactly
does this mean?
Thanks,
Alan
The result I get from a level two trace is:
DBI::db=HASH(0x2278c2c) trace level set to 2 in DBI 1.14-nothread
- prepare for DBD::ODBC::db (DBI::db=HASH(0x2278c68)~0x2278c2c
'UPDATE [DBI
_TEST]
SET retired_date = ?
WHERE person_id = ?')
dbd_preparse scanned 2 distinct placeholders
dbd_st_prepare'd sql f37040784
UPDATE [DBI_TEST]
SET retired_date = ?
WHERE person_id = ?
- prepare= DBI::st=HASH(0x2278ff8) at jcdb_update.pl line 254.
- execute for DBD::ODBC::st (DBI::st=HASH(0x2278ff8)~0x2278f74
'#11/1/2001#
' 22218)
bind 1 == '#11/1/2001#' (attribs: )
bind 1 == '#11/1/2001#' (size 11/12/0, ptype 4, otype 1)
bind 1: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=11.
bind 2 == '22218' (attribs: )
bind 2 == 22218 (size 5/6/0, ptype 6, otype 1)
bind 2: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=5.
dbd_st_execute (for sql f37040784 after)...
st_execute/SQLExecute error -1 recorded: [Microsoft][ODBC Microsoft
Access Drive
r] Too few parameters. Expected 3. (SQL-07001)(DBD:
st_execute/SQLExecute err=-1
)
Re: Looping through recordset twice
Don Seiler writes: I was considering this, but just thought it would be easier to just set the flag in an aux structure and loop through my original recordset again, checking the flag each time. Anyone know if there is a definitive yes/no on this? There are various issues to consider, but they depend on your specific situation. First, if the number of records that are going to match your criteria is limited, then I'd definitely just push these to an array. Going through the record set a second time is potentially a problem if the records that match your criteria are likely to change between runs (if there are updates between the runs, for example). That said, why can't you use the WHERE clause to effectively retrieve the rows that match your criteria and avoid the problem? Michael -- Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler [EMAIL PROTECTED] - [EMAIL PROTECTED] International Sybase User Group - http://www.isug.com
Re: Looping through recordset twice
Basically, when I get to a new group number. The record set is ordered by group number, so all records in a group are together. As I'm looping through records in a group, I do some evaluation and add values to variables. When I get to a new group number, I look at the values. If they meet my criteria I add the last group number to an array. Then when I'm done I planned to loop again through the record set and if the group number matches one in the array I'd print it. This is probably horribly inefficient and I'm leaning towards saving the records to a tmp array and if they qualify saving that to master array for later printing. Don. On Thu, 1 Nov 2001, Michael Peppler wrote: Don Seiler writes: Actually the nature of the problem is what stopped me from doing this. I won't know which records I want until I look at the group of them. Example: I have a table of records. There is a groupnum column. Many records have the same groupnum, i.e. they are in the same group. I'm only interested in selecting the group as a whole. I will only know if I want this group based on examining all of the records for that group. Hmmm - what condition determins that a group is complete?
Re: Looping through recordset twice
Perhaps I'm missing it, then. basically my query is this: select cust_no, acct_type, acct_status, group_num from cust,acct where cust.cust_no=acct.cust_no order by group_num the values of acct_type and acct_status for all of the records in a group determine if I want that group or not. I don't think I can make that determination until I've gone through the recordset though. -- Don Seiler [EMAIL PROTECTED] Database Administrator / Sr Software Engineer NSightTel Billing LLCPhone: 920.617.7501 1580 Mid Valley DriveFax:920.617.7493 De Pere, WI 54115 Cell: 920.606.3240 Pager: [EMAIL PROTECTED] / 920.613.2000 On Thu, 1 Nov 2001, Marcelo Guelfi wrote: Are you sure that you can't use the GROUP BY clause? Saludos, Marcelo. Don Seiler Don.Seiler@Ce To: Michael Peppler [EMAIL PROTECTED] llcom.com cc: Marcelo Guelfi/Uruguay/Contr/IBM@IBMUY, [EMAIL PROTECTED] Subject: Re: Looping through recordset twice 01/11/2001 16:13 Please respond to Don Seiler Basically, when I get to a new group number. The record set is ordered by group number, so all records in a group are together. As I'm looping through records in a group, I do some evaluation and add values to variables. When I get to a new group number, I look at the values. If they meet my criteria I add the last group number to an array. Then when I'm done I planned to loop again through the record set and if the group number matches one in the array I'd print it. This is probably horribly inefficient and I'm leaning towards saving the records to a tmp array and if they qualify saving that to master array for later printing. Don. On Thu, 1 Nov 2001, Michael Peppler wrote: Don Seiler writes: Actually the nature of the problem is what stopped me from doing this. I won't know which records I want until I look at the group of them. Example: I have a table of records. There is a groupnum column. Many records have the same groupnum, i.e. they are in the same group. I'm only interested in selecting the group as a whole. I will only know if I want this group based on examining all of the records for that group. Hmmm - what condition determins that a group is complete?
Re: Looping through recordset twice
I'll second the suggestion made a few messages back about storing your
results in some other data structure (as opposed to re-traversing the data
structure that DBI gives you).
In your circumstances, I'd probably put the retrieved rows in a hash of
arrays (keyed by group number) before doing anything else. Then I'd
examine each array of records in the group and delete it from the hash if
it didn't qualify. Then the HoA that is left over can be traversed for
whatever the final output is.
The code might look something like this:
my %groups;
while (my $hashref = $sth-fetchrow_hashref) {
push @{$groups{$hashref-{group_num}}}, $hashref;
}
then later...
foreach my $group_number (keys %groups) {
delete $groups{$group_number} unless group_is_OK($groups{$group_number});
}
then finally...
spew_group($_) foreach values %groups;
YMMV on exact implementation. In similar implementations, I will often
have other stuff in the data structure beyond that which I got directly out
of the database. It all depends.
At 01:26 PM 11/1/01 -0600, Don Seiler wrote:
Perhaps I'm missing it, then.
basically my query is this:
select cust_no, acct_type, acct_status, group_num
from cust,acct
where cust.cust_no=acct.cust_no
order by group_num
the values of acct_type and acct_status for all of the records in
a group determine if I want that group or not. I don't think I can make
that determination until I've gone through the recordset though.
--
Don Seiler [EMAIL PROTECTED]
Database Administrator / Sr Software Engineer
NSightTel Billing LLCPhone: 920.617.7501
1580 Mid Valley DriveFax:920.617.7493
De Pere, WI 54115 Cell: 920.606.3240
Pager: [EMAIL PROTECTED] / 920.613.2000
On Thu, 1 Nov 2001, Marcelo Guelfi wrote:
Are you sure that you can't use the GROUP BY clause?
Saludos,
Marcelo.
Don Seiler
Don.Seiler@Ce To: Michael Peppler
[EMAIL PROTECTED]
llcom.com cc: Marcelo
Guelfi/Uruguay/Contr/IBM@IBMUY, [EMAIL PROTECTED]
Subject: Re: Looping
through recordset twice
01/11/2001
16:13
Please respond
to Don
Seiler
Basically, when I get to a new group number. The record set is ordered by
group number, so all records in a group are together. As I'm looping
through records in a group, I do some evaluation and add values to
variables. When I get to a new group number, I look at the values. If
they meet my criteria I add the last group number to an array.
Then when I'm done I planned to loop again through the record set and if
the group number matches one in the array I'd print it.
This is probably horribly inefficient and I'm leaning towards saving the
records to a tmp array and if they qualify saving that to master array for
later printing.
Don.
On Thu, 1 Nov 2001, Michael Peppler wrote:
Don Seiler writes:
Actually the nature of the problem is what stopped me from doing this.
I won't know which records I want until I look at the group of them.
Example: I have a table of records. There is a groupnum column.
Many
records have the same groupnum, i.e. they are in the same group.
I'm
only interested in selecting the group as a whole. I will only know
if I
want this group based on examining all of the records for that group.
Hmmm - what condition determins that a group is complete?
Re: Looping through recordset twice
That will probably be the solution.
Thanks to all involved!
Don.
On Thu, 1 Nov 2001, David Marshall wrote:
I'll second the suggestion made a few messages back about storing your
results in some other data structure (as opposed to re-traversing the data
structure that DBI gives you).
In your circumstances, I'd probably put the retrieved rows in a hash of
arrays (keyed by group number) before doing anything else. Then I'd
examine each array of records in the group and delete it from the hash if
it didn't qualify. Then the HoA that is left over can be traversed for
whatever the final output is.
The code might look something like this:
my %groups;
while (my $hashref = $sth-fetchrow_hashref) {
push @{$groups{$hashref-{group_num}}}, $hashref;
}
then later...
foreach my $group_number (keys %groups) {
delete $groups{$group_number} unless group_is_OK($groups{$group_number});
}
then finally...
spew_group($_) foreach values %groups;
YMMV on exact implementation. In similar implementations, I will often
have other stuff in the data structure beyond that which I got directly out
of the database. It all depends.
At 01:26 PM 11/1/01 -0600, Don Seiler wrote:
Perhaps I'm missing it, then.
basically my query is this:
select cust_no, acct_type, acct_status, group_num
from cust,acct
where cust.cust_no=acct.cust_no
order by group_num
the values of acct_type and acct_status for all of the records in
a group determine if I want that group or not. I don't think I can make
that determination until I've gone through the recordset though.
--
Don Seiler [EMAIL PROTECTED]
Database Administrator / Sr Software Engineer
NSightTel Billing LLCPhone: 920.617.7501
1580 Mid Valley DriveFax:920.617.7493
De Pere, WI 54115 Cell: 920.606.3240
Pager: [EMAIL PROTECTED] / 920.613.2000
On Thu, 1 Nov 2001, Marcelo Guelfi wrote:
Are you sure that you can't use the GROUP BY clause?
Saludos,
Marcelo.
Don Seiler
Don.Seiler@Ce To: Michael Peppler
[EMAIL PROTECTED]
llcom.com cc: Marcelo
Guelfi/Uruguay/Contr/IBM@IBMUY, [EMAIL PROTECTED]
Subject: Re: Looping
through recordset twice
01/11/2001
16:13
Please respond
to Don
Seiler
Basically, when I get to a new group number. The record set is ordered by
group number, so all records in a group are together. As I'm looping
through records in a group, I do some evaluation and add values to
variables. When I get to a new group number, I look at the values. If
they meet my criteria I add the last group number to an array.
Then when I'm done I planned to loop again through the record set and if
the group number matches one in the array I'd print it.
This is probably horribly inefficient and I'm leaning towards saving the
records to a tmp array and if they qualify saving that to master array for
later printing.
Don.
On Thu, 1 Nov 2001, Michael Peppler wrote:
Don Seiler writes:
Actually the nature of the problem is what stopped me from doing this.
I won't know which records I want until I look at the group of them.
Example: I have a table of records. There is a groupnum column.
Many
records have the same groupnum, i.e. they are in the same group.
I'm
only interested in selecting the group as a whole. I will only know
if I
want this group based on examining all of the records for that group.
Hmmm - what condition determins that a group is complete?
RE: Looping through recordset twice
From: Don Seiler [mailto:[EMAIL PROTECTED]] Basically, when I get to a new group number. The record set is ordered by group number, so all records in a group are together. As I'm looping through records in a group, I do some evaluation and add values to variables. When I get to a new group number, I look at the values. If they meet my criteria I add the last group number to an array. Print all the records in a group to a temp file (IO::File::new_tmpfile), then if it meets your criteria, seek to the beginning of the file and use File::Copy to print it (copy it) to \*STDOUT or whereever your destination is. HTH, Douglas Wilson
RE: First try: Compiling Static Perl with DBI and DBD::Informix
At 12:28 11/01/2001, Mahdi A. Sbeih wrote: Hi Gary, Thanks for these steps, I have some questions for you: - The shell script at the beginning of your email is to be used after compiling Perl statically with all needed modules other than DBD::Informix, right? After everything BUT the DBD::Informix. And only if you find that you can't compile anything after you compile in the DBD::Informix - When compiling DBI for example, how to force it to go in the Perl executable? It happens by itself. :) When you run make install (after make and make test) it either just adds ..pm stuff to your /usr/local/lib/perl5 or does that and makes a new perl executable. If the latter, make will display a message about putting the new perl executable in /usr/local/bin In other words how to force each module compiled to go inside the Perl executable? See above. Not every single item actually makes a new executable. Even in static mode. -- [EMAIL PROTECTED]
Re: MS Access Too Few Parameters Error Help
On Thu, 01 Nov 2001 11:03:56 -0800, Alan Hogue wrote: I am trying to execute an update to an Access database. The statement has two placeholders, and I put two variables between the parentheses in the execute statement, and yet Access tells me it expects 3 parameters. This behaviour has had me baffled for a long while, too. It is typical, as I've mentioned here before, for when you mistyped a field name. It looks as if Access thinks of unknown field names as parameters, as it does when you run such a query in Access. -- Bart.
Column Names
May be a simple Q... How do I get column names and order of column names for a select * from ... query. I dont want to use selecthash_ref just for column names. Thanks in advance, --MVRamana _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
[netlabs #64] Re: How to install DBI for Oracle
I am developing an Intranet for my company. We have an oracle database version 8i running on another Linux machine. which I want to use as a backborn. I have tried to Install DBI on Windows NT Server using: perl Makefile.pl and I get an error: Can't locate loadable for modle DBI ...In DBI.pm. line 189 When read the DBI at that line it tells me to install the DBI properly using: perl Makefile.pl I have downloaded DBD-Oracle-1.07.tar.gz and DBI-1.20.tar.gz. All I have done is to extract them but I do not know how to install them. Please help. Kavuma Ivan Systems Administrator. - Do You Yahoo!? Find a job, post your resume on Yahoo! Careers.
RE: [netlabs #64] Re: How to install DBI for Oracle
Use ActivePerl from www.activestate.com. Use it's ppm utility to install modules (binaries). See docs for ppm. Ilya -Original Message- From: Ivan Kavuma [mailto:[EMAIL PROTECTED]] Sent: Friday, November 02, 2001 1:51 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: [netlabs #64] Re: How to install DBI for Oracle I am developing an Intranet for my company. We have an oracle database version 8i running on another Linux machine. which I want to use as a backborn. I have tried to Install DBI on Windows NT Server using: perl Makefile.pl and I get an error: Can't locate loadable for modle DBI ...In DBI.pm. line 189 When read the DBI at that line it tells me to install the DBI properly using: perl Makefile.pl I have downloaded DBD-Oracle-1.07.tar.gz and DBI-1.20.tar.gz. All I have done is to extract them but I do not know how to install them. Please help. Kavuma Ivan Systems Administrator. - Do You Yahoo!? Find a job, post your resume on Yahoo! Careers.
