Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
boolean cfsqltype
Author: Eddie Awad
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216114
> I think you are reading me wrong. When I write that boolean is a
> native SQL datatype I am not refering to some implementation, I
> am refering to the SQL standard. See ISO/IEC 9075-2:2003 4.1.2
Jochem,
I think we misunderstood each other. I was referring to Oracle's
implementation.
No harm done :)
--
Eddie Awad.
http://awads.net/
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216111
Eddie Awad wrote:
>
>>
>> Boolean is a native SQL datatype.
>
> In Oracle, Boolean is not a native SQL datatype.
I think you are reading me wrong. When I write that boolean is a
native SQL datatype I am not refering to some implementation, I
am refering to the SQL standard. See ISO/IEC 9075-2:2003 4.1.2
And I find it far more likely that the reason there is no
cf_sql_boolean type is that there is no boolean type in JDBC then
that some implementation choose to implement a certain type in
some weird way.
Jochem
Author: Eddie Awad
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216109
> Boolean is a native SQL datatype.
In Oracle, Boolean is not a native SQL datatype. It is a PL/SQL
datatype. In other words, you cannot create a database table having a
column with a boolean datatype:
H:\SQLplus>sqlplus scott/tiger
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Aug 23 15:03:57 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
scott@eddev> create table t (mycol boolean);
create table t (mycol boolean)
*
ERROR at line 1:
ORA-00902: invalid datatype
scott@eddev> create table t (mycol varchar2(1));
Table created.
Related Oracle documentation:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c13datyp.htm
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/03_types.htm
--
Eddie Awad.
http://awads.net/
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216107
Eddie Awad wrote:
>
> Oracle's PL/SQL Boolean data type does not have an equivalent in
> ColdFusion, I think this may be because "Boolean" is not a native SQL
> data type.
Boolean is a native SQL datatype. Strangely enough it is not a
JDBC datatype though.
Jochem
Author: S. Isaac Dealey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216106
----- Excess quoted text cut - see Original Post for more -----
Seems like it'd make a lot more sense to use a bit or int column and
the values 0 or 1 since these are commonly used as boolean values in
config files and most databases around the world when a boolean
datatype isn't available or isn't used. Plus CF can evaluate a 0 or 1
value in a query column on its own without the need to determine if
it's using Y/N or T/F. I've seen people use Y/N or T/F or "Yes" and
"No" in db columns and I LOATHE them all. I'd accept a boolean
datatype if the db has one, although I'm not likely to create a column
with that data type myself.
s. isaac dealey 954.522.6080
new epoch : isn't it time for a change?
add features without fixtures with
the onTap open source framework
http://www.fusiontap.com
http://coldfusion.sys-con.com/author/4806Dealey.htm
Author: Deanna Schneider
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216101
Yah, I changed it to number and used 1/0. But, I'm still annoyed. :P
----- Excess quoted text cut - see Original Post for more -----
Author: Eddie Awad
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216097
----- Excess quoted text cut - see Original Post for more -----
Oracle's PL/SQL Boolean data type does not have an equivalent in
ColdFusion, I think this may be because "Boolean" is not a native SQL
data type. So, what I usually end up doing is to change the data type
of the Boolean argument in my stored procedure to something like
varchar2(1), accepting values of Y/N or T/F.
--
Eddie Awad.
http://awads.net/
Author: Deanna Schneider
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216079
Nope. Again it defaults it to char if you use no type.
Dang - this is an annoying lack of functionality.
----- Excess quoted text cut - see Original Post for more -----
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216076
Deanna Schneider wrote:
> Both bit and integer fail as well. I hard-coded a 1 in there, just so
> I was sure I was sending a 1 or 0. No go.
Try no type and value TRUE.
Jochem
Author: Deanna Schneider
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216074
Both bit and integer fail as well. I hard-coded a 1 in there, just so
I was sure I was sending a 1 or 0. No go.
----- Excess quoted text cut - see Original Post for more -----
Author: S. Isaac Dealey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216073
See if cf_sql_bit works.
----- Excess quoted text cut - see Original Post for more -----
s. isaac dealey 954.522.6080
new epoch : isn't it time for a change?
add features without fixtures with
the onTap open source framework
http://www.fusiontap.com
http://coldfusion.sys-con.com/author/4806Dealey.htm
Author: Barney Boisvert
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216071
Did you try cf_sql_bit or cf_sql_integer? Not boolean, I know, but
those seem like they'd have a better chance of working right than a
CHAR value (which presumably CF uses as a default for unknown types,
rather than erroring).
cheers,
barneyb
----- Excess quoted text cut - see Original Post for more -----
--
Barney Boisvert
bboisvert@gmail.com
360.319.6145
http://www.barneyb.com/
Got Gmail? I have 50 invites.
Author: Mark A Kruger
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216072
Deanna,
Try a "CF_SQL_BIT" and pass 0 or 1.
-Mark
So, I just noticed that there isn't a documented cf_sql_boolean type
for use with stored procedures. If you use it, though, it doesn't fail
to compile, and it converts it to a cf_sql_char. But, with my stored
proc in Oracle 10g - it's not going in as a boolean. (I get the error
about wrong number or type of paramaters.)
Does anyone know if there's a workaround that will really send a
boolean to the stored proc, or do I need to alter my stored proc to
not expect a boolean?
Author: Deanna Schneider
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:41862#216070
So, I just noticed that there isn't a documented cf_sql_boolean type
for use with stored procedures. If you use it, though, it doesn't fail
to compile, and it converts it to a cf_sql_char. But, with my stored
proc in Oracle 10g - it's not going in as a boolean. (I get the error
about wrong number or type of paramaters.)
Does anyone know if there's a workaround that will really send a
boolean to the stored proc, or do I need to alter my stored proc to
not expect a boolean?
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
