Finding out the type expected for a placeholder
Ed Avis
insert into some_table values (:a, :b)
and then find out what types the database is expecting for the two
placeholders? I'm using DBD::Oracle.
Really I just want to know whether a string or an integer is expected.
--
Ed Avis <avi...@kbcfp.com>
Michael A Chase
Placeholders are VARCHAR2 by default. If you know what columns you are
inserting into (and you should), you can use $dbh->column_info() to find out
the column types.
http://search.cpan.org/~timb/DBI/DBI.pm#Database_Handle_Methods
--
Mac :})
Ed Avis
>>Is there a way I can prepare a statement handle for some SQL like
>>
>> insert into some_table values (:a, :b)
>>
>>and then find out what types the database is expecting for the two
>>placeholders? I'm using DBD::Oracle.
>Placeholders are VARCHAR2 by default. If you know what columns you are
>inserting into (and you should), you can use $dbh->column_info() to
>find out the column types.
Sorry, I gave the wrong example. In fact my query is just a select
statement.
I'd like to find out whether Oracle is expecting a given bind variable
to be a datetime, an integer or whatever.
--
Ed Avis <avi...@kbcfp.com>
Ed Avis
>>I'd like to find out whether Oracle is expecting a given bind
>>variable to be a datetime, an integer or whatever.
>
>The original example may have been wrong, but the advice still stands.
>You can control the type of each placeholder.
No; because I don't know in advance what the query will be. The user is
entering the SQL to use, with bind variables, and I would like to
prepare the statement handle and find out from the database what the
expected type of each bind variable will be.
--
Ed Avis <avi...@kbcfp.com>
Michael A Chase
> Michael A Chase wrote:
>
>> Placeholders are VARCHAR2 by default. If you know what columns you
>> are inserting into (and you should), you can use
>> $dbh->column_info() to find out the column types.
>
> Sorry, I gave the wrong example. In fact my query is just a select
> statement.
>
> I'd like to find out whether Oracle is expecting a given bind
> variable to be a datetime, an integer or whatever.
The original example may have been wrong, but the advice still stands.
You can control the type of each placeholder. To decide what type you
want it to be, you have to know what column it is interacting with.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Michael A Chase
> Michael A Chase wrote:
That's showing an awful lot of trust in the users' good intentions and
skill.
All placeholders are VARCHAR unless the type is explicitly given in the
bind_param*() call. If any values provided by the user have to be
treated differently, they'll have to tell you by some method unless you
parse the SQL to figure it out. I think SQL::Statement might be able to
help, but it is likely to take a lot of work to get right since many
cases are likely to be ambiguous.
Michael A Chase
> Michael A Chase wrote:
>
>> All placeholders are VARCHAR unless the type is explicitly given in the
>> bind_param*() call.
>
> I'm not quite sure what you mean. It is quite possible for a
> placeholder to be of DATETIME type, for example. Perl passes the
> date as a string but nonetheless it has to be a date.
I should have been more explicit. All DBI placeholders are VARCHAR unless
explicitly defined differently in bind_param*().
This is not different from Oracle bind variables except that there is no default
type for Oracle bind variables, you always have to define what type the Oracle
bind variable will be before you use it.
--
Mac :})
Ed Avis
>All placeholders are VARCHAR unless the type is explicitly given in the
>bind_param*() call.
I'm not quite sure what you mean. It is quite possible for a
placeholder to be of DATETIME type, for example. Perl passes the
date as a string but nonetheless it has to be a date.
>If any values provided by the user have to be
>treated differently, they'll have to tell you by some method unless you
>parse the SQL to figure it out. I think SQL::Statement might
>be able to help, but it is likely to take a lot of work to get right
>since many cases are likely to be ambiguous.
Yes - it would be very flaky to try and parse the SQL on the client side
and work out the data types. The only reliable way to do it is to ask
the database server what it is expecting.
I wonder what Pro*C and other tools do when they compile some SQL with
placeholders. Surely Pro*C is checking at compile time that the
placeholder type matches the declared type in the source file.
--
Ed Avis <avi...@kbcfp.com>
Tim Bunce
Oracle, like most databases, offers no natural way to do this.
Tim.
John Moon
Sent: Monday, June 13, 2005 12:17 PM
To: dbi-...@perl.org
Subject: RE: Finding out the type expected for a placeholder
You would have to query the database object tables prior to executing
your bind executes. This will give you the information on what each column
type is so you can predefine them.
... jwm wrote
"IF" this is the way you choose to build your update/insert statements
please be aware that "date" formats vary by session... So you would need to
either do a to_date function and different mask for each date you handle or
set the session date format {my $sth = $dbh->prepare(q{alter session set
NLS_DATE_FORMAT = 'dd-Mon-YYYY'}); ...} AND convert each date to that
format... As for knowing the "type" of column for an insert/update I have
not had any problems (or need), with Oracle, to know the "type" of the
column when using placeholders EXCEPT for date types... SO I'm not sure
where your problem is unless it is dates... I use Oracle versions 7.x to
9.x... (There are "types" that I have NOT used - "glob" for one)
Jones Robert TTMS Contractor
Jared Still
> ... jwm wrote
>
> "IF" this is the way you choose to build your update/insert statements
> please be aware that "date" formats vary by session... So you would need to
> either do a to_date function and different mask for each date you handle or
> set the session date format {my $sth = $dbh->prepare(q{alter session set
> NLS_DATE_FORMAT = 'dd-Mon-YYYY'}); ...} AND convert each date to that
> format... As for knowing the "type" of column for an insert/update I have
> not had any problems (or need), with Oracle, to know the "type" of the
> column when using placeholders EXCEPT for date types... SO I'm not sure
> where your problem is unless it is dates... I use Oracle versions 7.x to
> 9.x... (There are "types" that I have NOT used - "glob" for one)
I have found the Date::Manip package extremely useful in the
manipulation of unknown date formats.
It can easily convert unknown formats to known format so that
you only have to deal with a single format in your SQL.
Jared