Any gotchas there? I am opening an Access db via ODBC and binding those
columns (including a date field) and passing that to the Oracle handle
to do inserts (i.e. Access -> Oracle migration).
Robert
Robert Hicks wrote:
> Any gotchas there? I am opening an Access db via ODBC and binding
> those columns (including a date field) and passing that to the Oracle
> handle to do inserts (i.e. Access -> Oracle migration).
Only gotcha is with formatting -- you'll need to either:
1) "alter session set nls_date_format =3D '...'" to the date format =
you're
supplying Oracle, or:
2) use to_char(?,'...') on the date fields
Philip
Garrett, Philip (MAN-Corporate) wrote:
> Robert Hicks wrote:
>> Any gotchas there? I am opening an Access db via ODBC and binding
>> those columns (including a date field) and passing that to the Oracle
>> handle to do inserts (i.e. Access -> Oracle migration).
>
> Only gotcha is with formatting -- you'll need to either:
>
> 1) "alter session set nls_date_format = '...'" to the date format you're
> supplying Oracle, or:
>
> 2) use to_char(?,'...') on the date fields
>
> Philip
Oracle won't accept it if I do TO_DATE($start_date, 'DD/MM/YYYY') ?
Robert
--=__Part6F4B117F.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Sorry for the top-post - Groupwise :-(
Notice how Philip suggested using "to_char" - *not*
"to_date".
You probably already know this, but on the chance you don't,
you use "to_date" if you have a string that contains a date and
you want to put that date into a "DATE" column in the database.
You use "to_char" if you want to pull a "DATE" column out of
the database into a string (scalar) variable.
HTH.
Hardy Merrill
>>> Robert Hicks
Garrett, Philip (MAN-Corporate) wrote:
> Robert Hicks wrote:
>> Any gotchas there? I am opening an Access db via ODBC and binding
>> those columns (including a date field) and passing that to the
Oracle
>> handle to do inserts (i.e. Access -> Oracle migration).
>
> Only gotcha is with formatting -- you'll need to either:
>
> 1) "alter session set nls_date_format = '...'" to the date format
you're
> supplying Oracle, or:
>
> 2) use to_char(?,'...') on the date fields
>
> Philip
Oracle won't accept it if I do TO_DATE($start_date, 'DD/MM/YYYY') ?
Robert
--=__Part6F4B117F.1__=--
Robert Hicks wrote:
> Garrett, Philip (MAN-Corporate) wrote:
>> Robert Hicks wrote:
>>> Any gotchas there? I am opening an Access db via ODBC and binding
>>> those columns (including a date field) and passing that to the
>>> Oracle handle to do inserts (i.e. Access -> Oracle migration).
>>=20
>> Only gotcha is with formatting -- you'll need to either:
>>=20
>> 1) "alter session set nls_date_format =3D '...'" to the date format
>> you're supplying Oracle, or:=20
>>=20
>> 2) use to_char(?,'...') on the date fields
>>=20
>> Philip
>=20
> Oracle won't accept it if I do TO_DATE($start_date, 'DD/MM/YYYY') ?
Yeah, sorry -- that should have been TO_DATE.
Philip
Hardy Merrill wrote:
> Sorry for the top-post - Groupwise :-(
>
> Notice how Philip suggested using "to_char" - *not*
> "to_date".
>
> You probably already know this, but on the chance you don't,
> you use "to_date" if you have a string that contains a date and
> you want to put that date into a "DATE" column in the database.
> You use "to_char" if you want to pull a "DATE" column out of
> the database into a string (scalar) variable.
>
> HTH.
>
> Hardy Merrill
I think I get it yes. So here is what I am doing. Access has a date
field that I am pulling out and when I print the "$start_date" variable
it looks like this:
2006-09-15 00:00:00
That is a string now to Perl...correct? Now I am inserted that string
into the Oracle database as a DATE. So I am doing, using the variable
from the bindcolumn parameter:
TO_DATE($start_date, 'MM/DD/YYYY')
to insert that string into Oracle as a DATE and passing in the date
format along with it.
Do I have that right?
Robert
Robert Hicks wrote:
> Hardy Merrill wrote:
>> Sorry for the top-post - Groupwise :-(
>>=20
>> Notice how Philip suggested using "to_char" - *not* "to_date".
>>=20
>> You probably already know this, but on the chance you don't,
>> you use "to_date" if you have a string that contains a date and
>> you want to put that date into a "DATE" column in the database.
>> You use "to_char" if you want to pull a "DATE" column out of
>> the database into a string (scalar) variable.
>>=20
> I think I get it yes. So here is what I am doing. Access has a date
> field that I am pulling out and when I print the "$start_date"
> variable it looks like this:
>=20
> 2006-09-15 00:00:00
>=20
> That is a string now to Perl...correct?
Yes.
> Now I am inserted that string into the Oracle database as a DATE. So I
> am doing, using the variable from the bindcolumn parameter:
>=20
> TO_DATE($start_date, 'MM/DD/YYYY')
>=20
> to insert that string into Oracle as a DATE and passing in the date
> format along with it.
>=20
> Do I have that right?
Yeah, pretty much. You should use bind parameters to pass the date into
Oracle, though -- NOT put it directly in the string. This ensures you
will have no problems with quoting or nulls.
Example:
my $sql =3D qq{
INSERT INTO MY_TABLE (THE_DATE)
VALUES (TO_DATE(?,'YYYY-MM-DD HH24:MI:SS'))
};
my $sth =3D $dbh->prepare($sql) || die $dbh->errstr;
while (my $date =3D get_date_from_access()) {
$sth->execute($date) || die $sth->errstr;
}
Philip
--=__PartD0F4AEFC.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Yes that looks correct.
If you're still having trouble with it, reply back and post
the small section of code that you're having trouble with,
and the error you are getting.
Hardy Merrill
>>> Robert Hicks
Hardy Merrill wrote:
> Sorry for the top-post - Groupwise :-(
>
> Notice how Philip suggested using "to_char" - *not*
> "to_date".
>
> You probably already know this, but on the chance you don't,
> you use "to_date" if you have a string that contains a date and
> you want to put that date into a "DATE" column in the database.
> You use "to_char" if you want to pull a "DATE" column out of
> the database into a string (scalar) variable.
>
> HTH.
>
> Hardy Merrill
I think I get it yes. So here is what I am doing. Access has a date
field that I am pulling out and when I print the "$start_date" variable
it looks like this:
2006-09-15 00:00:00
That is a string now to Perl...correct? Now I am inserted that string
into the Oracle database as a DATE. So I am doing, using the variable
from the bindcolumn parameter:
TO_DATE($start_date, 'MM/DD/YYYY')
to insert that string into Oracle as a DATE and passing in the date
format along with it.
Do I have that right?
Robert
--=__PartD0F4AEFC.1__=--
Garrett, Philip (MAN-Corporate) wrote:
> Robert Hicks wrote:
>> Hardy Merrill wrote:
>>> Sorry for the top-post - Groupwise :-(
>>>
>>> Notice how Philip suggested using "to_char" - *not* "to_date".
>>>
>>> You probably already know this, but on the chance you don't,
>>> you use "to_date" if you have a string that contains a date and
>>> you want to put that date into a "DATE" column in the database.
>>> You use "to_char" if you want to pull a "DATE" column out of
>>> the database into a string (scalar) variable.
>>>
>> I think I get it yes. So here is what I am doing. Access has a date
>> field that I am pulling out and when I print the "$start_date"
>> variable it looks like this:
>>
>> 2006-09-15 00:00:00
>>
>> That is a string now to Perl...correct?
>
> Yes.
>
>> Now I am inserted that string into the Oracle database as a DATE. So I
>> am doing, using the variable from the bindcolumn parameter:
>>
>> TO_DATE($start_date, 'MM/DD/YYYY')
>>
>> to insert that string into Oracle as a DATE and passing in the date
>> format along with it.
>>
>> Do I have that right?
>
> Yeah, pretty much. You should use bind parameters to pass the date into
> Oracle, though -- NOT put it directly in the string. This ensures you
> will have no problems with quoting or nulls.
>
> Example:
>
> my $sql = qq{
> INSERT INTO MY_TABLE (THE_DATE)
> VALUES (TO_DATE(?,'YYYY-MM-DD HH24:MI:SS'))
> };
> my $sth = $dbh->prepare($sql) || die $dbh->errstr;
>
> while (my $date = get_date_from_access()) {
> $sth->execute($date) || die $sth->errstr;
> }
>
> Philip
That is exactly what I am doing. : )
Robert
--jRHKVT23PllUwdXP
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
On 2006-10-18 10:19:24 -0400, Hardy Merrill wrote:
> I think I get it yes. So here is what I am doing. Access has a date=20
> field that I am pulling out and when I print the "$start_date" variable
>=20
> it looks like this:
>=20
> 2006-09-15 00:00:00
>=20
> That is a string now to Perl...correct? Now I am inserted that string=20
> into the Oracle database as a DATE. So I am doing, using the variable=20
> from the bindcolumn parameter:
>=20
> TO_DATE($start_date, 'MM/DD/YYYY')
>=20
> to insert that string into Oracle as a DATE and passing in the date=20
> format along with it.
The date format is wrong. If you tell Oracle to expect a date in
MM/DD/YYYY format and then feed it a string like '2006-09-15 00:00:00'
it will complain that they don't match. You will eitther have to change
the format string to match the actual date format (i.e., 'YYYY-MM-DD
HH24:MI:SS') or change the date format to match the format string.
Also, TO_DATE($start_date, 'MM/DD/YYYY') will almost certainly result in
a syntax error. That expands into something like=20
TO_DATE(2006-09-15 00:00:00, 'MM/DD/YYYY')
which is not not correct SQL (the quotes are missing). Use placeholders.
Finally, you might want to look at the execute_array method. For recent
versions of DBD::Oracle is a lot faster then execute for bulk inserts.=20
hp
--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
--jRHKVT23PllUwdXP
Content-Type: application/pgp-signature
Content-Disposition: inline
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
iQDQAwUBRTcmdlLjemazOuKpAQKrUwXRARD1/Gh0ESZK4rxltDu0UDlGaFVw ux3U
SvCeP7NDG8XhgkPfS/sVSnzuWC1Sw4CmPz/e288/3WflTguVKP/g+KOZPhOi YYtQ
N4pPxZLHCE8RzFYxnvskZ4zS2U0hHkNBZS0PULMmfDNTxnhOdVBk3JCkVHqk V7ea
LWYX7s12wHvWxtFvghuaJb/snFJPqB9XUb1GYTAvzoCAjEacNjOTZC/QjBzl uwK/
LU0H+mZfNl5B0C2nF4uHzk0iKg==
=FDZz
-----END PGP SIGNATURE-----
--jRHKVT23PllUwdXP--
Peter J. Holzer wrote:
> On 2006-10-18 10:19:24 -0400, Hardy Merrill wrote:
>> I think I get it yes. So here is what I am doing. Access has a date
>> field that I am pulling out and when I print the "$start_date" variable
>>
>> it looks like this:
>>
>> 2006-09-15 00:00:00
>>
>> That is a string now to Perl...correct? Now I am inserted that string
>> into the Oracle database as a DATE. So I am doing, using the variable
>> from the bindcolumn parameter:
>>
>> TO_DATE($start_date, 'MM/DD/YYYY')
>>
>> to insert that string into Oracle as a DATE and passing in the date
>> format along with it.
>
> The date format is wrong. If you tell Oracle to expect a date in
> MM/DD/YYYY format and then feed it a string like '2006-09-15 00:00:00'
> it will complain that they don't match. You will eitther have to change
> the format string to match the actual date format (i.e., 'YYYY-MM-DD
> HH24:MI:SS') or change the date format to match the format string.
>
I did...and I realized my mistake when that error came up. : )
Robert