Data type mismatch warning using DBI

[Peter Jamieson]

I am using Perl 5.8 with the DBI module to routinely send parsed data to an
Access db.
Sometimes the incoming files may have errors so that numeric data appears as
text.
This causes my script to throw a warning:

DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Data
type
mismatch in criteria expression. (SQL-22018) at C:\myscript.pl line 422.

When this happens the script contiues to run and on looking at the db the
particular
record is absent.
What I would like to happen is for my script to die when the above warning
occurs so that
I can examine the input file to see what was causing the data type mismatch
and rectify
the error.

I have checked some DBI tutorials but cannot locate any way of doing this.
Any suggestions or help appreciated!

 

[smallpond]

I am using Perl 5.8 with the DBI module to routinely send parsed data to an
Access db.
Sometimes the incoming files may have errors so that numeric data appears as
text.
This causes my script to throw a warning:

DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Data
type
mismatch in criteria expression. (SQL-22018) at C:\myscript.pl line 422.

When this happens the script contiues to run and on looking at the db the
particular
record is absent.
What I would like to happen is for my script to die when the above warning
occurs so that
I can examine the input file to see what was causing the data type mismatch
and rectify
the error.

I have checked some DBI tutorials but cannot locate any way of doing this.
Any suggestions or help appreciated!

"DBD::ODBC::st execute failed" is not a warning. Are you checking
that
the call at line 422 succeeded?

--S

 

[Peter Jamieson]

I am using Perl 5.8 with the DBI module to routinely send parsed data to
an
Access db.
Sometimes the incoming files may have errors so that numeric data appears
as
text.
This causes my script to throw a warning:

DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
Data
type
mismatch in criteria expression. (SQL-22018) at C:\myscript.pl line 422.

When this happens the script contiues to run and on looking at the db the
particular
record is absent.
What I would like to happen is for my script to die when the above
warning
occurs so that
I can examine the input file to see what was causing the data type
mismatch
and rectify
the error.

I have checked some DBI tutorials but cannot locate any way of doing
this.
Any suggestions or help appreciated!

"DBD::ODBC::st execute failed" is not a warning. Are you checking
that
the call at line 422 succeeded?

--S

Thanks for your input smallpond!

Line 422 is simply the: $input->execute($field1,$field2....etc) line in my
script.
Printing out the values of each field can be done of course to detect the
errant
data in the file (usually a typo) but as the script runs unattended to
process thousands of files it
is not practical since the file with the error is not known in advance.
What I seek is for my code to stop or pause thus warning me that a data-type
error was encountered. Hope this makes sense?

 

[smallpond]

I am using Perl 5.8 with the DBI module to routinely send parsed data to
an
Access db.
Sometimes the incoming files may have errors so that numeric data appears
as
text.
This causes my script to throw a warning:
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
Data
type
mismatch in criteria expression. (SQL-22018) at C:\myscript.pl line 422.
When this happens the script contiues to run and on looking at the db the
particular
record is absent.
What I would like to happen is for my script to die when the above
warning
occurs so that
I can examine the input file to see what was causing the data type
mismatch
and rectify
the error.
I have checked some DBI tutorials but cannot locate any way of doing
this.
Any suggestions or help appreciated!

"DBD::ODBC::st execute failed" is not a warning. Are you checking
that
the call at line 422 succeeded?

--S

Thanks for your input smallpond!

Line 422 is simply the: $input->execute($field1,$field2....etc) line in my
script.
Printing out the values of each field can be done of course to detect the
errant
data in the file (usually a typo) but as the script runs unattended to
process thousands of files it
is not practical since the file with the error is not known in advance.
What I seek is for my code to stop or pause thus warning me that a data-type
error was encountered. Hope this makes sense?

execute returns a value which you have to check to see whether it
succeeded
or failed. One way to do that might be:

$input->execute($field1,$field2....etc) or
myprint_the_error_and_die_sub($input->errstr, $thisfilename, $NR,
$field1, $field2, ...);

--S

 

[J. Gleixner]

I am using Perl 5.8 with the DBI module to routinely send parsed data to
an
Access db.
Sometimes the incoming files may have errors so that numeric data appears
as
text.
This causes my script to throw a warning:

DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver]
Data
type
mismatch in criteria expression. (SQL-22018) at C:\myscript.pl line 422.

When this happens the script contiues to run and on looking at the db the
particular
record is absent.
What I would like to happen is for my script to die when the above
warning
occurs so that
I can examine the input file to see what was causing the data type
mismatch
and rectify
the error.

I have checked some DBI tutorials but cannot locate any way of doing
this.
Any suggestions or help appreciated!

"DBD::ODBC::st execute failed" is not a warning. Are you checking
that
the call at line 422 succeeded?

--S

Thanks for your input smallpond!

Line 422 is simply the: $input->execute($field1,$field2....etc) line in my
script.
Printing out the values of each field can be done of course to detect the
errant
data in the file (usually a typo) but as the script runs unattended to
process thousands of files it
is not practical since the file with the error is not known in advance.
What I seek is for my code to stop or pause thus warning me that a data-type
error was encountered. Hope this makes sense?

As 'smallpond' asked, "Are you checking that the call at line 422
succeeded?"

Check the documentation ( perldoc DBI ) for RaiseError or do what
'smallpond' is suggesting and check that the call succeeded. You
may print out something and wait for input, send you E-Mail, or die
if it fails. Examples of doing this also is conveniently included
in the documentation and on thousands of Web sites.

$sth->execute($product_code, $qty, $price) or die $dbh->errstr;

perldoc -f die

 

[Paul Lalli]

I am using Perl 5.8 with the DBI module to routinely send parsed data to an
Access db. Sometimes the incoming files may have errors so that numeric data
appears as text. This causes my script to throw a warning:

DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Data
type mismatch in criteria expression. (SQL-22018) at C:\myscript.pl line 422.

When this happens the script contiues to run and on looking at the db the
particular record is absent.
What I would like to happen is for my script to die when the above warning
occurs so that I can examine the input file to see what was causing the data
type mismatch and rectify the error.

It sounds to me like you have PrintError turned on, but want
RaiseError turned on instead.

You can read the docs for DBI, but basically:

$dbh->{RaiseError} = 1;

near the beginning of your db work, after the $dbh is created.

Paul Lalli

 

[Eric Pozharski]

When this happens the script contiues to run and on looking at the db
the particular record is absent. What I would like to happen is for
my script to die when the above warning occurs so that I can examine
the input file to see what was causing the data type mismatch and
rectify the error.

Look through C<perldoc DBI> for word I<RaiseError> (description is in
L<ATTRIBUTES COMMON TO ALL HANDLES> section).

BTW, consider validating your input.

*CUT*

 

[Peter Jamieson]

Thank you to smallpond, J. Gleixner, Paul Lalli and Eric Pozharski
for assistance. I am working through your suggestions which have all
proved quite helpful.
Your comments are very much appreciated!