RE: Oracle and bindcolumns

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
.

Relevant Pages

  • Re: Location of linked table username/password
    ... I use this procedure to re-create links to Oracle. ... Public Function LinkOracleTables(strDSN As String, strSchema As Variant, ... Dim dbODBC As Database, strConnect As String, strSQL As String ... Exit Sub ...
    (microsoft.public.access.externaldata)
  • Re: A little Rolodex [revised]
    ... including alpha sort and searching for any embedded string, ... In this application, a database is a directory, ... you may optionally provide any alternate UCASE program, ... NN -> first store NN as key length ...
    (comp.sys.hp48)
  • Re: return multiple rows from sql statement
    ... strings from input values is almost certainly a safe path to SQL ... Also, being a MySQL function, it knows what MySQL needs or uses. ... All characters that are entered in the fields make their way into the database unaltered. ... The insert of what surprisinlgly was NOT a syntax error, but a string called "mysql_insert_id" into an integer field resulted in the value zero being put in. ...
    (comp.lang.php)
  • Re: Invalid variant type conversion
    ... because within regular programming, there would be no agreement upon what ... applications built by standard Delphi controls, ... NULL is the same as "" in a database. ... >> The empty string tells it all. ...
    (comp.lang.pascal.delphi.misc)
  • Re: Code to delete/unlink Linked tables
    ... Public intLinkODBCTables As Variant, intLinkDB2Tables As Variant ... Public strLinkBackendDB As String, strLinkDSNname As String, strLinkLibName ... ' MsgBox "This database is in MDE format...I will delete/recreate ODBC ... Public Sub fncLinkDB2Table() ...
    (microsoft.public.access.modulesdaovba)