I have just created a small form connecting from Servoy over the JDBC-ODBC Bridge to a SQL server database. I have been able to get data back into the form with no problems. The JDBC-ODBC Bridge is only able to support the calls that the ODBC driver supports. Looking at SQL server through ODBC test a columns call reveals that there should indeed be an IS_NULLABLE column which sql server has and passes back into Servoy. Looking at a Foxpro database here and trying to link it into Servoy we get the same error. This is because the Foxpro ODBC driver is not ODBC 3.5 and does not therefore have an IS_NULLABLE column within the columns metadata. You can see this from the output of ODBCtest:
Full Connect(Use If Needed)
Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
Successfully connected to DSN ‘Visual FoxPro Database’.
SQLTables:
In: StatementHandle = 0x00761968, CatalogName = SQL_NULL_HANDLE,
NameLength1 = 0, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0,
TableName = SQL_NULL_HANDLE, NameLength3 = 0, TableType = SQL_NULL_HANDLE, NameLength4 = 0
Return: SQL_SUCCESS=0
Get Data All:
“table_qualifier”, “table_owner”, “table_name”, “table_type”, “remarks”
“d:\I386”, , “bookmrk”, “TABLE”, “”
“d:\I386”, , “groups”, “TABLE”, “”
“d:\I386”, , “grpsyll”, “TABLE”, “”
“d:\I386”, , “prgrss2”, “TABLE”, “”
“d:\I386”, , “progress”, “TABLE”, “”
“d:\I386”, , “settings”, “TABLE”, “”
“d:\I386”, , “syllabi2”, “TABLE”, “”
“d:\I386”, , “syllabus”, “TABLE”, “”
“d:\I386”, , “usergrp”, “TABLE”, “”
“d:\I386”, , “users”, “TABLE”, “”
10 rows fetched from 5 columns.
SQLColumns:
In: StatementHandle = 0x00761968, CatalogName = SQL_NULL_HANDLE,
NameLength1 = 0, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0, TableName = “bookmrk”,
NameLength3 = 7, ColumnName = SQL_NULL_HANDLE, NameLength4 = 0
Return: SQL_SUCCESS=0
Get Data All:
“table_qualifier”, “table_owner”, “table_name”, “column_name”, “data_type”, “type_name”, “precision”, “length”, “scale”, “radix”, “nullable”, “remarks”
“d:\I386”, , “bookmrk”, “syl_name”, 1, “C”, 64, 64, , , 0, “”
“d:\I386”, , “bookmrk”, “username”, 1, “C”, 32, 32, , , 0, “”
“d:\I386”, , “bookmrk”, “topic_id”, 2, “N”, 4, 4, 0, 10, 0, “”
3 rows fetched from 12 columns.
As you can see above the Foxpro database has ‘nullable’ rather than ‘is_nullable’ and it is this that is causing the problem. Unfortunately then the problem is within the Foxpro ODBC driver and the level of ODBC conformance that it is at. You can see as follows the SQL server ODBC driver does not exhibit this behaviour:
Get Data All:
“TABLE_CAT”, “TABLE_SCHEM”, “TABLE_NAME”, “COLUMN_NAME”, “DATA_TYPE”, “TYPE_NAME”, “COLUMN_SIZE”, “BUFFER_LENGTH”, “DECIMAL_DIGITS”, “NUM_PREC_RADIX”, “NULLABLE”, “REMARKS”, “COLUMN_DEF”, “SQL_DATA_TYPE”, “SQL_DATETIME_SUB”, “CHAR_OCTET_LENGTH”, “ORDINAL_POSITION”, “IS_NULLABLE”, “SS_DATA_TYPE”
“easysoft”, “dbo”, “products”, “product”, 1, “char”, 4, 4, , , 0, , , 1, , 4, 1, “NO”, 47
“easysoft”, “dbo”, “products”, “description”, 12, “varchar”, 128, 128, , , 0, , , 12, , 128, 2, “NO”, 39
2 rows fetched from 19 columns.