Data type mismatch warning using DBI

Discussion in 'Perl Misc' started by Peter Jamieson, Sep 5, 2008.

  1. 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!
     
    Peter Jamieson, Sep 5, 2008
    #1
    1. Advertising

  2. Peter Jamieson

    smallpond Guest

    On Sep 4, 11:38 pm, "Peter Jamieson"
    <> wrote:
    > 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
     
    smallpond, Sep 5, 2008
    #2
    1. Advertising

  3. "smallpond" <> wrote in message
    news:...
    > On Sep 4, 11:38 pm, "Peter Jamieson"
    > <> wrote:
    >> 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?
     
    Peter Jamieson, Sep 5, 2008
    #3
  4. Peter Jamieson

    smallpond Guest

    On Sep 5, 11:09 am, "Peter Jamieson"
    <> wrote:
    > "smallpond" <> wrote in message
    >
    > news:...
    >
    >
    >
    > > On Sep 4, 11:38 pm, "Peter Jamieson"
    > > <> wrote:
    > >> 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
     
    smallpond, Sep 5, 2008
    #4
  5. Peter Jamieson

    J. Gleixner Guest

    Peter Jamieson wrote:
    > "smallpond" <> wrote in message
    > news:...
    >> On Sep 4, 11:38 pm, "Peter Jamieson"
    >> <> wrote:
    >>> 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
     
    J. Gleixner, Sep 5, 2008
    #5
  6. Peter Jamieson

    Paul Lalli Guest

    On Sep 4, 11:38 pm, "Peter Jamieson"
    <> wrote:
    > 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
     
    Paul Lalli, Sep 5, 2008
    #6
  7. Peter Jamieson <> wrote:
    *SKIP*
    > 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*

    --
    Torvalds' goal for Linux is very simple: World Domination
     
    Eric Pozharski, Sep 5, 2008
    #7
  8. 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!
     
    Peter Jamieson, Sep 10, 2008
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. data type mismatch error

    , Aug 10, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    654
    Karl Seguin
    Aug 10, 2005
  2. psychomad
    Replies:
    2
    Views:
    5,295
    Alexey Smirnov
    Apr 10, 2007
  3. Bobby Edward
    Replies:
    1
    Views:
    4,520
    Kevin Spencer
    Mar 5, 2008
  4. Replies:
    2
    Views:
    300
  5. programmingzeal
    Replies:
    0
    Views:
    1,170
    programmingzeal
    May 6, 2012
Loading...

Share This Page