Bugs item #1066751, was opened at 2004-11-15 08:12
Message generated for change (Comment added) made by sf-robot
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1066751&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL/ODBC
Group: SQL CVS Head
>Status: Closed
Resolution: Fixed
Priority: 5
Private: No
Submitted By: Sjoerd Mullender (sjoerd)
Assigned to: Sjoerd Mullender (sjoerd)
Summary: SQLColumns uses incorrect query
Initial Comment:
The query SQLColumns uses to query the server contains
this:
cast(c."type" as smallint) as data_type,
cast(c."type" as varchar) as type_name,
This is obviously incorrect. The c."type" (from
sys.columns) cannot be cast both to smallint and
varchar (it is a varchar column).
The bug also occurs in 2.4.2.
----------------------------------------------------------------------
>Comment By: SourceForge Robot (sf-robot)
Date: 2007-11-29 19:20
Message:
Logged In: YES
user_id=1312539
Originator: NO
This Tracker item was closed automatically by the system. It was
previously set to a Pending status, and the original submitter
did not respond within 365 days (the time period specified by
the administrator of this Tracker).
----------------------------------------------------------------------
Comment By: Stefan Manegold (stmane)
Date: 2006-11-29 01:04
Message:
Logged In: YES
user_id=572415
Originator: NO
over-ruling "sf-robot":
re-set to "Pending" since adding a proper test has neither been done nor
finally been discarded, yet.
User (submitter) & developer (assignee),
please check, again.
----------------------------------------------------------------------
Comment By: SourceForge Robot (sf-robot)
Date: 2006-11-09 19:20
Message:
Logged In: YES
user_id=1312539
This Tracker item was closed automatically by the system. It was
previously set to a Pending status, and the original submitter
did not respond within 365 days (the time period specified by
the administrator of this Tracker).
----------------------------------------------------------------------
Comment By: Sjoerd Mullender (sjoerd)
Date: 2005-11-09 03:40
Message:
Logged In: YES
user_id=43607
BugDay_2005-11-09, sjoerd: NO TEST / POSTPONED
We need tests for *all* ODBC functions.
(The report was a reminder that the implementation of
SQLColumns was not correct.)
----------------------------------------------------------------------
Comment By: Wouter Alink (vzzzbx)
Date: 2005-11-09 03:14
Message:
Logged In: YES
user_id=621590
BugDay_2005-11-09, vzzzbx: RE-ASSIGNED TO sjoerd
(i don't understand this bug, but appearantly something went
wrong in the ODBC driver)
----------------------------------------------------------------------
Comment By: Sjoerd Mullender (sjoerd)
Date: 2004-11-15 12:33
Message:
Logged In: YES
user_id=43607
The value in columns.type is not correct for the type_name
column of the result. The former is the server's internal
name, whereas the latter is the SQL name. The former could
be something like "month_interval" or "int" when the SQL
name should be "interval month" and "integer" respectively.
Anyway, the bug is fixed in ODBC.
----------------------------------------------------------------------
Comment By: Fabian (mr-meltdown)
Date: 2004-11-15 08:22
Message:
Logged In: YES
user_id=963970
I assume the problem here is not the cast itself, but the
column usage.
JDBC:
String query =
"SELECT '" + cat + "' AS \"TABLE_CAT\",
\"schemas\".\"name\" AS \"TABLE_SCHEM\", " +
"\"tables\".\"name\" AS \"TABLE_NAME\",
\"columns\".\"name\" AS \"COLUMN_NAME\", " +
"cast(CASE \"columns\".\"type\" " +
"WHEN 'table' THEN " + Types.ARRAY + " " +
"WHEN 'boolean' THEN " + Types.BOOLEAN + " " +
"WHEN 'bool' THEN " + Types.BOOLEAN + " " +
"WHEN 'ubyte' THEN " + Types.CHAR + " " +
"WHEN 'char' THEN " + Types.CHAR + " " +
"WHEN 'character' THEN " + Types.CHAR + " " +
"WHEN 'varchar' THEN " + Types.VARCHAR + " " +
"WHEN 'text' THEN " + Types.LONGVARCHAR + " " +
"WHEN 'tinytext' THEN " + Types.LONGVARCHAR
+ " " +
"WHEN 'string' THEN " + Types.LONGVARCHAR +
" " +
"WHEN 'tinyint' THEN " + Types.TINYINT + " " +
"WHEN 'smallint' THEN " + Types.SMALLINT + " " +
"WHEN 'mediumint' THEN " + Types.INTEGER + " " +
"WHEN 'oid' THEN " + Types.OTHER + " " +
"WHEN 'int' THEN " + Types.INTEGER + " " +
"WHEN 'integer' THEN " + Types.INTEGER + " " +
"WHEN 'bigint' THEN " + Types.BIGINT + " " +
"WHEN 'number' THEN " + Types.INTEGER + " " +
"WHEN 'decimal' THEN " + Types.DECIMAL + " " +
"WHEN 'numeric' THEN " + Types.NUMERIC + " " +
"WHEN 'float' THEN " + Types.FLOAT + " " +
"WHEN 'double' THEN " + Types.DOUBLE + " " +
"WHEN 'real' THEN " + Types.DOUBLE + " " +
"WHEN 'month_interval' THEN " +
Types.INTEGER + " " +
"WHEN 'sec_interval' THEN " + Types.BIGINT +
" " +
"WHEN 'date' THEN " + Types.DATE + " " +
"WHEN 'time' THEN " + Types.TIME + " " +
"WHEN 'datetime' THEN " + Types.TIMESTAMP +
" " +
"WHEN 'timestamp' THEN " + Types.TIMESTAMP +
" " +
"WHEN 'blob' THEN " + Types.BLOB + " " +
"ELSE " + Types.OTHER + " " +
"END AS smallint) AS \"DATA_TYPE\", " +
"\"columns\".\"type\" AS \"TYPE_NAME\", " +
"\"columns\".\"type_digits\" AS \"COLUMN_SIZE\", " +
"\"columns\".\"type_scale\" AS
\"DECIMAL_DIGITS\", 0 AS \"BUFFER_LENGTH\", " +
"10 AS \"NUM_PREC_RADIX\", " +
"cast(CASE \"null\" " +
"WHEN true THEN " +
ResultSetMetaData.columnNullable + " " +
"WHEN false THEN " +
ResultSetMetaData.columnNoNulls + " " +
"END AS int) AS \"NULLABLE\", cast(null AS
varchar) AS \"REMARKS\", " +
"\"columns\".\"default\" AS \"COLUMN_DEF\", 0 AS
\"SQL_DATA_TYPE\", " +
"0 AS \"SQL_DATETIME_SUB\", 0 AS
\"CHAR_OCTET_LENGTH\", " +
"\"columns\".\"number\" + 1 AS
\"ORDINAL_POSITION\", " +
"cast(null AS varchar) AS \"SCOPE_CATALOG\", " +
"cast(null AS varchar) AS \"SCOPE_SCHEMA\", " +
"cast(null AS varchar) AS \"SCOPE_TABLE\", " +
"cast(" +
((MonetDriver)driver).getJavaType("other") + " AS smallint)
AS \"SOURCE_DATA_TYPE\", " +
"CASE \"null\" " +
"WHEN true THEN CAST ('YES' AS varchar) " +
"WHEN false THEN CAST ('NO' AS varchar) " +
"END AS \"IS_NULLABLE\" " +
"FROM \"sys\".\"columns\" AS \"columns\", " +
"\"sys\".\"tables\" AS \"tables\", " +
"\"sys\".\"schemas\" AS \"schemas\" " +
"WHERE \"columns\".\"table_id\" =
\"tables\".\"id\" " +
"AND \"tables\".\"schema_id\" =
\"schemas\".\"id\" ";
It's only such a small query...
the above stated problem is solved with the CASE statement
to get the right DATA_TYPE, while the real value is used as
TYPE_NAME
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1066751&group_id=56967
-------------------------------------------------------------------------
SF.Net email is sponsored by: The Future of Linux Business White Paper
from Novell. From the desktop to the data center, Linux is going
mainstream. Let it simplify your IT future.
http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
