Boolean with procparam?

[ploring]

:o

Seems like I would have run into this in the years and years I've been writing
CF, but alas, no. How does one pass a boolean with cfprocparam? I have to call
a handful of stored procs in a new CF app. I do not have the option of changing
the stored procedures because they are used by many other apps too.

Thanks in advance!

[ploring]

:o

Seems like I would have run into this in the years and years I've been writing
CF, but alas, no. How does one pass a boolean with cfprocparam? I have to call
a handful of stored procs in a new CF app. I do not have the option of changing
the stored procedures because they are used by many other apps too.

Thanks in advance!

[Mountain Lover]

It *should* be the same as cfqueryparam...cf_sql_bit
HTH

--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[ploring]

Naw, using a 0/1 with cf_sql_bit, passing it to a stored proc param in oracle
defined like this:
procedure myprocedure (p_mybooeanvariable in
boolean default false)

Fails with PLS-00306: wrong number or types of arguments in call to
'myprocedure'

[jonwrob]

The first thing that comes to mind, so it may not be the best, would be to
create a different stored procedure to call the one you cannot change. Instead
of boolean parameters, you could use something else in this procedure.

JR

[mxstu]

Just for grins .... did you try cf_sql_bit with a "value" of "true" or "false"?

[mxstu]

Hmm, the
[url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/[/url]
wwhelp.htm?context=ColdFusion_Documentation&file=0 0000317.htm doesn't show
anything for Oracle and boolean. It's been a while since I've used Oracle, but
is boolean a valid Oracle data type?

[CF_Oracle]

Yes boolean is a correct type. So try CF_SQL_BINARY. Reference source you linked to is incorrect anyway.

[mxstu]

Originally posted by: CF_Oracle
Yes boolean is a correct type. So try CF_SQL_BINARY. Reference source you
linked to is incorrect anyway.


CF_Oracle - I'm assuming by your moniker that you've used Oracle more recently
than I have ;-) Is boolean a true Oracle data type (not just PL/SQL)? Also,
in what way are the livedocs wrong?

[paross1]

Boolean is a PL/SQL datatype, which is NOT supported by the Oracle RDBMS.
Hence, there is no such thing as a column of type Boolean in an Oracle table.
Per chapter 12 of Oracle PL/SQL Programming, 3rd edition (Feuerstein), "Because
there is no counterpart for the PL/SQL Boolean in the Oracle RDBMS, you can
neither SELECT into a Boolean variable nor insert a TRUE or FALSE value
directly into a database column." This implies that you are unlikely to find an
equivalent of a Boolean CFSQLType for your CFPROCPARAM.

Phil

[mxstu]

Thanks Phil. That's exactly what I was asking.

Should have just looked at some of the JDBC documentation which spells it out
pretty clearly...

- Boolean is not a supported JDBC type. Accessing Boolean type from Java using
JDBC is not possible as of now. So a mapping of Boolean to another SQL type
that is understood by JDBC is required. Integer type is understood by JDBC and
PL/SQL

-Oracle JDBC drivers do not support calling arguments or return values of the
PL/SQL TABLE, BOOLEAN, or RECORD types. This is a restriction of the OCI layer.

As a work-around for booleans, you can define an additional PL/SQL stored
procedure that accepts the BOOLEAN argument as a CHAR or NUMBER and passes it
as a BOOLEAN to the original stored procedure. For more information on this
topic, see "Boolean Parameters in PL/SQL Stored Procedures".

[CF_Oracle]

mxtu, cf_sql_date type does not work - see discussion at
[url]http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=6&thread[/url]
id=1010746&highlight_key=y&keyword1=oracle.
Also, cf_sql_date and cf_sql_time work fine at least in CF7 with Oracle 9i but
it's not shown in the referenced table.

[CF_Oracle]

See a good blog for workaround with stored procedures using boolean : [url]http://awads.net/wp/2005/08/25/coldfusion-to-oracle-via-a-boolean/[/url]