http://qs321.pair.com?node_id=188199

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Someone PLEASE explain to me why the code below returns the error:
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL +Server]Syntax error converting datetime from character string. (SQL-2 +2008)DBD:st_execute/SQLExecute err=-1) $dbh = DBI->connect('Database', '******', '*******', 'ODBC', {RaiseErr +or => 1}) or die "Database connection not made: $DBI::errstr"; $dbhstmt = $dbh->prepare("INSERT INTO ConfigTable ( strID, Modificatio +nDate) VALUES (?, ?)"); die "ERROR: Cannot prepare statement: $DBI::errstr\n" unless (defined +$dbhstmt); $dbhstmt->execute( '$SuStCo_strID', '$date' ); $dbhstmt->finish; $dbhstmt->finish; $dbh->disconnect;

Replies are listed 'Best First'.
Re: DBI, SQL and Dates
by fuzzyping (Chaplain) on Aug 06, 2002 at 23:47 UTC
    Because this line...
    $dbhstmt->execute( '$SuStCo_strID', '$date' );
    does not interpolate the values... you need to use double-quotes (or none at all) for the variables to be interpolated correctly.

    -fp
Re: DBI, SQL and Dates
by jsprat (Curate) on Aug 06, 2002 at 23:50 UTC
    $dbhstmt->execute( '$SuStCo_strID', '$date' );

    Your problem is likely to be here, single quoted scalar variables. Why do these need quoting? Remove the quotes and try it again.

Re: DBI, SQL and Dates
by physgreg (Scribe) on Aug 07, 2002 at 08:29 UTC
    If you want the single quotes, then surround the string with double quotes:
    $dbhstmt->execute( "'$SuStCo_strID'", "'$date'" );
    I suggest this, as it's a good idea to have quotes around anything going into SQL, to stop nasty input tricks.
      $dbhstmt->execute( "'$SuStCo_strID'", "'$date'" ); I suggest this, as it's a good idea to have quotes around anything going into SQL, to stop nasty input tricks.

      Bad advice, unless you want to end up with extra quote characters around fields in the database. I've only seen people do that by accident.

      Better to pass strings to execute() either unquoted, or surrounded by double quotes. The latter is needed if you're trying to "pass" on object that overides stringify, and you want to store the stringified representation.

      it is not a great idea to put quotes around everything going into an sql statement, but luckily DBI has methods built in to help you quote intelligently. Check the docs for $dbh->quote() and $dbh->quote_identifier. great tips for using these methods can be found in chromatic's DBI is OK. I find I often use this bit of code from the article ('cause hash slices are so cool):

      my $table = 'uregisternew'; my @fields = qw( country firstname lastname userid password address1 c +ity state province zippostal email phone favorites remaddr gender inc +ome dob occupation age ); my $fields = join(', ', @fields); my $values = join(', ', map { $dbh->quote($_) } @formdata{@fields}); $sql = "INSERT into $table ($fields) values ($values)"; $sth = $dbh->prepare($sql); $sth->execute(); $sth->finish();

      the one change I sometimes have to make is to the line my $fields = join(', ', @fields);. If you're using a DB that allows spaces in column names, you'll need to quote the column names using code like my $fields = join(', ', map {dbh->quote_identifier($_) }@fields);.

      The take home message is, when in doubt use $dbh->quote() on any value you specify in your query, and use $dbh->quote_identifier() on any column or table name. Also, the docs recommend using the quote-like qq{} around sql statements containing variables prepared in this way, so that nothing gets misquoted. rewriting chromatic's example a little, I get a piece of code that looks like this:

      my $table_name = $dbh->quote_identifier(uregisternew); my @fields = qw( country firstname lastname userid password address1 c +ity state province zippostal email phone favorites remaddr gender inc +ome dob occupation age ); my $fields = join(', ', map { $dbh->quote_identifier($_) } @fields); my $values = join(', ', map { $dbh->quote($_) } @formdata{@fields}); $sql = qq{INSERT into $table_name ($fields) values ($values)}; $sth = $dbh->prepare($sql); $sth->execute(); $sth->finish();

      hth,
      --au