tdbc oddities and questions

[Gerhard Reithofer]

Hi TClers,
I'm writing often database driven applications. In the hope to be more
portable I want to use TDBC for all database engines in future.
Currently I'm using the various db specific interfaces:
sqlite or sqlite3, Pgtcl or pgintcl, mysqltcl, tclodbc and Oratcl.

For that I create common TCL wrappers which abstract various sql syntax
differences, like give me a list of tables, a unified table description,
an insert_or_update function, etc.
Planned backends are: SQLite, PostgreSQL, MySQL and ODBC which offers
also connections to MSSQL and Oracle.

But when I use the TDBC "tables" command for SQLite I get a dictionary
like:
test {type table name test tbl_name test rootpage ...}
sales_stat {type table name sales_stat tbl_name sales_stat ...}
test_view {type view name test_view tbl_name test_view ...}
sqlite_sequence {type table name sqlite_sequence tbl_name ...}

As you may see I can distinguish tables and views but also the sequence
(which is created automatically for an autoincrement column) is
classified a type "table".

Q: Any idea how these 2 objects can be identified correctly?

Another problem seems to be the odbc driver which creates double values
for numeric fields if they are larger than 32-bit integers.
But that is some time ago (IMHO with 8.5) and may not be valid anymore.

Q: Has someone any newer information about this issue?

As the MSSQL server is also available for Linux, see:
https://www.microsoft.com/de-de/sql-server/sql-server-2017

Q: I'd like to ask if a TDBC driver for Microsoft SQL Server might be
on plan sometime?

TIA,
Gerhard

--
Gerhard Reithofer - Techn. EDV Reithofer - http://www.tech-edv.co.at

[undro...@gmail.com]

Am Donnerstag, 2. August 2018 00:08:05 UTC+2 schrieb Gerhard Reithofer:

Hello Gerhard,

> ...

> But when I use the TDBC "tables" command for SQLite I get a dictionary
> like:
> test {type table name test tbl_name test rootpage ...}
> sales_stat {type table name sales_stat tbl_name sales_stat ...}
> test_view {type view name test_view tbl_name test_view ...}
> sqlite_sequence {type table name sqlite_sequence tbl_name ...}
>
> As you may see I can distinguish tables and views but also the sequence
> (which is created automatically for an autoincrement column) is
> classified a type "table".
>
> Q: Any idea how these 2 objects can be identified correctly?

sqlite_sequence is indeed a special system table. The name is hardwired into the SQLite engine. Thus, your best option is to deal with this case by filtering it out. I believe to remember that in the SQLite engine table/view names beginning with "sqlite_" are reserved anyway, so "sqlite_*" might be a good filter pattern to sort out system related things.

> Another problem seems to be the odbc driver which creates double values
> for numeric fields if they are larger than 32-bit integers.
> But that is some time ago (IMHO with 8.5) and may not be valid anymore.
>
> Q: Has someone any newer information about this issue?

Reading the current tdbcodbc 1.1.0 sources there's a mapping of SQL_BIGINT to Tcl_WideInt objects. With which numeric fields (i.e. data type in the DDL) do you observe the mapping to double?

Best,
Christian