RE: timestamp datatype


Hi Andy,

I created a subroutine to assist in the creation of an INSERT statement,
like so:

sub sub_createInsert($$) {
print STDERR "\n***** sub_createInsert() *****\n\n";
my ($tblName, $columnsref) = @_;
my ($sqlString, $delimCols, $bindVars, $column, @cols, @temp, $cnt);
my (@timestamp, @date);
$cnt = 0;
foreach $column (@{$columnsref}) {
$cnt++;
$timestamp[$cnt] = $cnt if ("@{$column}[1]" =~ m/timestamp/i);
$date[$cnt] = $cnt if ("@{$column}[1]" =~ m/date/i);
push (@cols, @{$column}[0]);
}
$delimCols = join(', ', @cols);
$bindVars = join ', ', ('?') x @{$columnsref};
@temp = split(/,/, $bindVars);
for (my $i=1; $i<=$cnt; $i++) {
if ($timestamp[$i]) {
$temp[$i] = "to_date(?, 'YYYY-MM-DD HH24:MM:SS')";
}
elsif ($date[$i]) {
$temp[$i] = "to_date(?)";
}
}
$bindVars = join(', ', @temp);
$sqlString = "insert into " . $tblName . " (" . $delimCols . ")
values" . " (" . $bindVars . ")";
print STDERR "RETURNING SQL STRING: $sqlString\n\n";
return($sqlString);
} #<=== (End of sub_createInsert)

It appears to be working quite nicely. :)

Thanks for all your help.

Peter

-----Original Message-----
From: Andy Hassall [mailto:andy@xxxxxxxxxxx]
Sent: Wednesday, June 28, 2006 11:18 AM
To: Loo, Peter # PHX; 'DBI-Users'
Subject: RE: timestamp datatype

You must tell Oracle what format your DATE or TIMESTAMP data is in,
because it's bound as a string, rather than the raw Oracle DATE or
TIMESTAMP format.
This means you have to either:

(a) Explicitly state it in the SQL statement using TO_DATE(?, 'format')
(or TO_TIMESTAMP or TO_TIMESTAMP_TZ as appropriate), or,

(b) Implicitly state it using NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT or
NLS_TIMESTAMP_TZ_FORMAT - either set as environment variables before you
connect, or using ALTER SESSION after you connect.

Either way, the format you pass must match your data.

For cases where you're building up the statement, including
placeholders, dynamically as you are, the ALTER SESSION route is often
more convenient, as you can continue to bind using just a plain
placeholder, instead of having to consider the type and put a TO_*
function around it.

--
Andy Hassall :: andy@xxxxxxxxxxx :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

-----Original Message-----
From: Loo, Peter # PHX [mailto:Peter.Loo@xxxxxxxxxxxxxxxxxxxxxxxx]
Sent: 28 June 2006 16:14
To: DBI-Users
Subject: timestamp datatype

Hi All,

I am getting the following error when I try INSERTing selected rows from
one database to another.

ORA-01843: not a valid month

Do I have to use to_date() and if so, has anyone done this with bind
variables?

I am using bind variables to INSERT the selected rows. Below is the
code and the table layout.

SOURCE DATABASE TABLE (Netezza)

nv01()=> \d t_bil_payment_type_codes
Table "t_bil_payment_type_codes"
Attribute | Type | Modifier | Default Value
-------------------+-----------------------+----------+---------------
bil_pymt_typ_cde | character(1) | not null |
bil_pymt_typ_desc | character varying(40) | not null |
lst_updt_id | character varying(40) | |
lst_updt_tstmp | timestamp | |
Distributed on hash: "bil_pymt_typ_cde"

nv01()=> select * from t_bil_payment_type_codes;
bil_pymt_typ_cde | bil_pymt_typ_desc | lst_updt_id | lst_updt_tstmp

------------------+-------------------+-------------+-------------------
--
1 | CASH | ODSIHR | 2005-09-08
19:32:38
2 | MEDICAID | ODSIHR | 2005-09-08
19:32:38
3 | MEDICARE | ODSIHR | 2005-09-08
19:32:38
4 | THIRD PARTY | ODSIHR | 2005-09-08
19:32:38
(4 rows)

DESTINATION DATABASE TABLE (Oracle)

SQL> desc t_bil_payment_type_codes
Name Null? Type
----------------------------------------- --------
----------------------------
BIL_PYMT_TYP_CDE NOT NULL CHAR(1)
BIL_PYMT_TYP_DESC NOT NULL VARCHAR2(40)
LST_UPDT_ID VARCHAR2(40)
LST_UPDT_TSTMP TIMESTAMP(6)

SQL> select * from t_bil_payment_type_codes;

B BIL_PYMT_TYP_DESC
- ----------------------------------------
LST_UPDT_ID
----------------------------------------
LST_UPDT_TSTMP
------------------------------------------------------------------------
---
1 CASH
ODSIHR
08-SEP-05 07.32.38.000000 PM

CODE EXCERPT:

$sqlString = qq{select * from t_bil_payment_type_codes};
$tblName = "t_bil_payment_type_codes";

unless ($s_sth = $s_dbh->prepare(qq{$s_sqlString})) {
$MESSAGE = "sub_prepare() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}

unless ($s_sth->execute()) {
$MESSAGE = "sub_execute() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}

unless ($s_arrayref = $s_sth->fetchall_arrayref()) {
$MESSAGE = "sub_fetchallArrayref() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}

if ($dbDriver eq "Oracle") {
$d_sqlString = qq{SELECT COLUMN_NAME, DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('$tblName')
AND OWNER = UPPER('$schema')
ORDER BY COLUMN_ID};
}
elsif ($dbDriver eq "ODBC") {
$d_sqlString = qq{select column_name, data_type
from all_tab_columns
where table_name = lower('$tblName')
and owner = lower('$schema')
order by column_sequence_nbr};

unless ($d_sth = $d_dbh->prepare(qq{$d_sqlString})) {
$MESSAGE = "sub_prepare() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}

unless ($d_sth->execute()) {
$MESSAGE = "sub_execute() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}

unless ($d_arrayref = $d_sth->fetchall_arrayref()) {
$MESSAGE = "sub_fetchallArrayref() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}

foreach $column (@{$d_arrayref}) {
push (@cols, @{$column});
}
$delimCols = join(', ', @cols);
$bindVars = join ', ', ('?') x @{$d_arrayref};
$d_sqlString = "insert into " . $tblName . " (" . $delimCols . ")
values" . " (" . $bindVars . ")";

unless ($d_sth = $d_dbh->prepare(qq{$d_sqlString})) {
$MESSAGE = "sub_prepare() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}

foreach $bindVar (@{$s_arrayref}) {
unless ($d_sth->execute(@$bindVar)) {
$MESSAGE = "sub_executeBind() - $DBI::errstr";
$STATUS = $FAILURE;
sub_exit();
}
}

Thanks a bunch in advance.

Peter



This E-mail message is for the sole use of the intended recipient(s) and
may contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply E-mail, and
destroy all copies of the original message.
.

Relevant Pages

  • Re: problems inserting a utf8 timestamp with DBD::Oracle
    ... Don't think so as I'm not using Oracle 9. ... Well I'm not saying it isn't a bug in Oracle but this one is a real problem I cannot easily workaround as really I need a timestamp and a date. ... Client is Oracle 10.2.0 XE ... If you then comment out the 2nd block of code and uncomment the 3rd block of code it works i.e it appears it does not like having a timestamp and a date in the same insert. ...
    (perl.dbi.users)
  • Re: Date different
    ... >> oracle changed the default types returned for their newest oracle drivers, ... What is going on with DATE and TIMESTAMP? ... The more obvious mapping to java.sql.Date was somewhat ... You set the system property by including a -D option in your java command ...
    (comp.lang.java.programmer)
  • Re: timezone - dst calendar questions
    ... Oracle Enterprise Manager agent were broken. ... HPUX box then compared the timestamp shown by 'ls -l' ... Perhaps the system time zone is wrongly set. ...
    (comp.unix.admin)
  • Re: timezone - dst calendar questions
    ... Oracle Enterprise Manager agent were broken. ... between my desktop and my db server. ... HPUX box then compared the timestamp shown by 'ls -l' ... Perhaps the system time zone is wrongly set. ...
    (comp.unix.admin)
  • Re: timezone - dst calendar questions
    ... Oracle Enterprise Manager agent were broken. ... HPUX box then compared the timestamp shown by 'ls -l' ... Show the system time zone: ...
    (comp.unix.admin)