Disclaimer: I am a n00b.
It seems like ODBC does not support a BOOLEAN type? Is this true?
If so, what's the standard kludgearound?
Edit: I am using ADO with Delphi on Windows to write the data, but PHP 5 to read it back.
+1
A:
There's SQL_C_BIT, but you need to lookup what a given driver uses for each SQL type. For example, MySQL uses SQL_C_CHAR for bool.
Matthew Flaschen
2010-06-02 10:55:53
+1 I am using ADO with Delphi on Windows to write the data, but PHP 5 to read it back.
Mawg
2010-06-02 11:01:23
But what driver? For example, MySQL's is MySQL Connector/ODBC.
Matthew Flaschen
2010-06-02 11:03:50
+1
A:
I believe it depends on the actual SQL server implementation. You can check the ODBC driver/datasource settings, if you are doing it under Windows -- there might be options such as Bool As Char, or something.
Gnudiff
2010-06-02 10:57:21
+1 but, "I believe it depends on the actual SQL server implementation" - surely that's what ODBC hides from me? Matthew might be closer to the mark when speaking of the driver(?)
Mawg
2010-06-02 11:00:39
+1
A:
SQL itself has traditionally not supported a boolean type, so ODBC is just reflecting this. As ODBC is intended to provide portability across databases, it is generally better to implement booleans in the database as one of the standard types, such as CHAR(1), containing either 'Y' or 'N', rather than use a vendor specific type.
anon
2010-06-02 10:59:35
Thanks, Neil, that seems sensible (and somehow 'better' than using 0 or 1).
Mawg
2010-06-02 11:18:40
I don't agree. ODBC is a C API, so it's easier (though not necessary) to use 0 and 1.
Matthew Flaschen
2010-06-02 11:24:29
@Matthew It is techically no easier, Y and N are (for english speakers) self-documenting, and an integer takes up more space in the database.
anon
2010-06-02 11:40:05
@Neil, you can use a CHAR(1) with the integral values \1 and \0. I would say this is self-documenting for C programmers. I consider `if(lightOn)` more natural than `if(lightOn == 'Y')`.
Matthew Flaschen
2010-06-02 11:53:09
@Matthew SQL databases are accessed by all sorts of programmers, not just C ones. and note that in C the character constants '1' and '0' are both true.
anon
2010-06-02 12:03:40
@Neil, ODBC is still a C API. Other languages that use it have C wrappers. It's also easy to use C-style booleans in most languages even if you need a `!= 0`. I never said '0' and '1', I said \0 and \1. I.E the literal byte values 0 and 1, NUL and Start of Heading. :)
Matthew Flaschen
2010-06-02 12:10:53
@Mathew And those will display nicely when you use a query tool will they? Give me a break.
anon
2010-06-02 12:20:08