in reply to Re^5: Issue while inserting Thai Characters into Database..
in thread Issue while inserting Thai Characters into Database..

Thanks for your response Martin.
I tried inserting date in SQL Server directly, it is allowing me 'YYYY-MM-DD' and 'MM-DD-YYYY' formats. It is getting displayed as 'MM/DD/YYYY HH:MM:SS AM'format.
But when i try inserting from Perl it is giving errors as shown below. 

my $sth = $dbh->prepare(<<SQL) or die "$DBI::errstr\n";
INSERT INTO BK_imageINfo (BusDate) VALUES ({ts ?})
SQL
*******OR*******
my $sth = $dbh->prepare(<<SQL) or die "$DBI::errstr\n";
INSERT INTO BK_imageINfo (BusDate) VALUES ({d ?})
SQL

$sth->execute($bdate);

[download]
Input1. my $bdate = "2010-01-20 00:00:00";
ERROR: DBD::ODBC::st execute failed: MicrosoftODBC SQL Server DriverSQL ServerIncorrect syntax near '@P1'. (SQL-42000).
MicrosoftODBC SQL Server DriverSQL ServerStatement(s) could not be prepared. (SQL-42000)

Input2. my $bdate = "01-20-2010 00:00:00";
ERROR: DBD::ODBC::st execute failed: MicrosoftODBC SQL Server DriverInvalid character value for cast specification (SQL-22018)

Replies are listed 'Best First'.
Re^7: Issue while inserting Thai Characters into Database..
by mje (Curate) on Jan 21, 2010 at 13:46 UTC

    Don't do that, make the whole parameter the string "{ts 'MYDATEHERE'}". You cannot stick a parameter in the middle of the ODBC date/time syntax, the parameter needs to be the whole thing.

    my $sql= q/INSERT INTO BK_imageINfo (BusDate) VALUES (?)/;
.
.
.
execute(q/{ts '2010-01-20 00:00:00'}/);

    [download]
[reply]
[d/l]
      I tried several options with timestamp. But i got "MicrosoftODBC SQL Server DriverInvalid precision value (SQL-HY104)" error message.
      But below code with date and no timestamp is working fine without any issues. 
      my $sql= "INSERT INTO BK_imageINfo (BusDate) VALUES (?)";
.
my $bdate = "2010-01-20";
$sth->execute("{d '$bdate'}");

      [download]
      Through my script, now i am able to insert date as well as Thai Characters to SQL Server database without any issues...

      Thanks much for your extensive support Martin.

      Thanks,
      Ajay
[reply]
[d/l]

        I now realise you probably mean an actual timestamp column as opposed to a datetime column - yes? timestamp columns are special in SQL Server and you generally cannot insert into them but you can set a DEFAULT on the column to get the current time. If you have a column of type timestamp you should not be inserting into it - if you need to then make it a datetime instead and use the ODBC "ts" syntax.

[reply]

In Section Seekers of Perl Wisdom