Best ODBC data type for Boolean data

Best ODBC data type for Boolean data

Post by Andrew Chal » Wed, 18 Dec 2002 04:57:34



I have some data in memory that is represented by Boolean values. When I
write this to a table with ODBC what is the best choice of ODBC data type
and SQL data type?

Many thanks.

 
 
 

Best ODBC data type for Boolean data

Post by Bob Hairgro » Wed, 18 Dec 2002 06:10:26


On Mon, 16 Dec 2002 13:57:34 -0600, "Andrew Chalk"


>I have some data in memory that is represented by Boolean values. When I
>write this to a table with ODBC what is the best choice of ODBC data type
>and SQL data type?

A lot of people use either INTEGER or CHAR(1). You can set up the
field like this:

CREATE TABLE foo (
   bar INTEGER NOT NULL DEFAULT 0,
   /* ... */
   CONSTRAINT chk_bar CHECK (bar IN (0, 1)));

or like this:

CREATE TABLE foo (
   bar CHAR(1) NOT NULL DEFAULT '0',
   /* ... */
   CONSTRAINT chk_bar CHECK (bar IN ('0', '1')));

MS-Access uses a non-standard "integer" which is actually an
SQL_SMALLINT with values of 0 or -1.

Bob Hairgrove


 
 
 

Best ODBC data type for Boolean data

Post by Andrew Chal » Wed, 18 Dec 2002 06:48:34


Many thanks!


> On Mon, 16 Dec 2002 13:57:34 -0600, "Andrew Chalk"

> >I have some data in memory that is represented by Boolean values. When I
> >write this to a table with ODBC what is the best choice of ODBC data type
> >and SQL data type?

> A lot of people use either INTEGER or CHAR(1). You can set up the
> field like this:

> CREATE TABLE foo (
>    bar INTEGER NOT NULL DEFAULT 0,
>    /* ... */
>    CONSTRAINT chk_bar CHECK (bar IN (0, 1)));

> or like this:

> CREATE TABLE foo (
>    bar CHAR(1) NOT NULL DEFAULT '0',
>    /* ... */
>    CONSTRAINT chk_bar CHECK (bar IN ('0', '1')));

> MS-Access uses a non-standard "integer" which is actually an
> SQL_SMALLINT with values of 0 or -1.

> Bob Hairgrove


 
 
 

Best ODBC data type for Boolean data

Post by Michael Visinti » Thu, 19 Dec 2002 02:16:07


If you will have a lot of records of this type and want to save space, then
use a bit field.  You can store a 0 or 1 there.

Keep in mind that the VB code will interpret it as True or False, not 0 and
1.

Michael


> Many thanks!



> > On Mon, 16 Dec 2002 13:57:34 -0600, "Andrew Chalk"

> > >I have some data in memory that is represented by Boolean values. When
I
> > >write this to a table with ODBC what is the best choice of ODBC data
type
> > >and SQL data type?

> > A lot of people use either INTEGER or CHAR(1). You can set up the
> > field like this:

> > CREATE TABLE foo (
> >    bar INTEGER NOT NULL DEFAULT 0,
> >    /* ... */
> >    CONSTRAINT chk_bar CHECK (bar IN (0, 1)));

> > or like this:

> > CREATE TABLE foo (
> >    bar CHAR(1) NOT NULL DEFAULT '0',
> >    /* ... */
> >    CONSTRAINT chk_bar CHECK (bar IN ('0', '1')));

> > MS-Access uses a non-standard "integer" which is actually an
> > SQL_SMALLINT with values of 0 or -1.

> > Bob Hairgrove


 
 
 

Best ODBC data type for Boolean data

Post by Andrew Chal » Thu, 19 Dec 2002 06:43:51


Thanks.


> If you will have a lot of records of this type and want to save space,
then
> use a bit field.  You can store a 0 or 1 there.

> Keep in mind that the VB code will interpret it as True or False, not 0
and
> 1.

> Michael



> > Many thanks!



> > > On Mon, 16 Dec 2002 13:57:34 -0600, "Andrew Chalk"

> > > >I have some data in memory that is represented by Boolean values.
When
> I
> > > >write this to a table with ODBC what is the best choice of ODBC data
> type
> > > >and SQL data type?

> > > A lot of people use either INTEGER or CHAR(1). You can set up the
> > > field like this:

> > > CREATE TABLE foo (
> > >    bar INTEGER NOT NULL DEFAULT 0,
> > >    /* ... */
> > >    CONSTRAINT chk_bar CHECK (bar IN (0, 1)));

> > > or like this:

> > > CREATE TABLE foo (
> > >    bar CHAR(1) NOT NULL DEFAULT '0',
> > >    /* ... */
> > >    CONSTRAINT chk_bar CHECK (bar IN ('0', '1')));

> > > MS-Access uses a non-standard "integer" which is actually an
> > > SQL_SMALLINT with values of 0 or -1.

> > > Bob Hairgrove