Character max length for Boolean (YesNo) column?

[Jamie Collins]

Code to reproduce:

Sub Test()

Const PATH As String = "" & _
"C:\"
Const FILENAME_JET As String = "" & _
"New_Jet_DB.mdb"

Const CONN_STRING_JET As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH><FILENAME>"

' Build connection string
Dim strConJet As String
strConJet = CONN_STRING_JET
strConJet = Replace(strConJet, "<PATH>", PATH)
strConJet = Replace(strConJet, "<FILENAME>", FILENAME_JET)

' Create DB
Dim Cat As Object
Set Cat = CreateObject("ADOX.Catalog")
Cat.Create strConJet

With Cat.ActiveConnection

' Create table
.Execute "CREATE TABLE Test1 (MyBooleanCol YESNO NOT NULL DEFAULT
True)"

' Get schema info
Dim oRs As Object
Set oRs = .OpenSchema(4, Array(Null, Null, "Test1", "MyBooleanCol"))

End With

With oRs

' Print relevant info
Debug.Print "Is nullable=" & !IS_NULLABLE
Debug.Print "Has default=" & !COLUMN_HASDEFAULT; ""
Debug.Print "Default value=" & !COLUMN_DEFAULT
Debug.Print "Character max length=" & !CHARACTER_MAXIMUM_LENGTH

.Close

End With

End Sub

Results:

Is nullable=False
Has default=True
Default value=True
Character max length=2

Why does my Boolean column have a character max length at all? Why is
it two?

I can code around this apparent anomaly, of course, but I wondered is
there some significance I haven't considered?

Thanks,
Jamie.

--

 

[david epsom dot com dot au]

I think the schema standard (of which this is an
implementation) predates the standards definition
of Boolean, so it is possible that the character
length may refer to a standards-compatible text
field. Or not.

(david)

 

[George Nicholson]

Well, isn't '-1' stored for True/Yes? (even though any non-zero value would
equate to True)

 

[Jamie Collins]

Thanks George. I think you've hit on the answer to my second question.

Jamie.

--

 

[Jamie Collins]

I think the schema standard (of which this is an
implementation) predates the standards definition
of Boolean, so it is possible that the character
length may refer to a standards-compatible text
field. Or not.

david, This sounds interesting. Is a way of viewing the schema
standard, other than using ADO/ADOX? I can use SELECT queries for my
SQL Server schema e.g.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS

I've not seen anything similar for Jet.

Thanks,
Jamie.

--

 

[david epsom dot com dot au]

um... when I said 'standard', I meant the ANSI (or ISO)
standard that defines the terms used in schemas.

Regarding viewing the schema, I'm don't know a way to
get column names using a SELECT query against a Jet
database. You can get the table names from the
MsysObjects table, and there may be something with
XML schema.

(david)

 

[Jamie Collins]

um... when I said 'standard', I meant the ANSI (or ISO)
standard that defines the terms used in schemas.

Ah. From my notes (Celko):

SQL92 didn't have a BOOLEAN datatype. A BOOLEAN would have to store
TRUE, FALSE and UNKNOWN in SQL's three-valued logic. And it would
have to take a NULL to be a datatype. Arrrrghh! That gives us FOUR
valued logic and destroys a lot of the foundations of the model of the
language. The BOOLEAN in SQL-99 is not technically a datatype -- it
allows TRUE, FALSE and UNKNOWN, but not NULL; datatypes have to be
NULL-able.

Regarding viewing the schema, I'm don't know a way to
get column names using a SELECT query against a Jet
database.

Oh well. Thanks david.

Jamie.

--