Description:
Currently, sybase user defined data types are just put into the mysql "create table" statement as is. This doesn't work, but each user defined data type is associated with an underlying storage type. The query to retrieve column information can be altered to provide the storage type when needed, thus allowing the resulting mysql statement to be valid.
How to repeat:
Take any sybase database containing a table with at least one column having a user defined data type. Convert to mysql and notice the sql error due to an invalid data type (the name of the user defined data type just gets copied into the mysql statement).
Suggested fix:
In the file ReverseEngineeringSybase.java, just replace the old tableColumnSelect value with this new one. As the comments state, this might not work with all versions of sybase, I'm no sybase expert. This code goes right before the reverseEngineerTableColumns function:
/* old query, returns user defined data types
private static String tableColumnSelect = "SELECT COLUMN_NAME = ISNULL(C.name, ''), "
+ " DATA_TYPE = T.name, C.length, NUMERIC_PRECISION = C.prec, "
+ " NUMERIC_SCALE = C.scale, "
+ " IS_NULLABLE = CONVERT(BIT, (C.status & 0x08)) "
+ "FROM syscolumns C, systypes T, sysobjects A "
+ "WHERE USER_NAME(A.uid) = ? AND "
+ " A.id = C.id AND C.id = OBJECT_ID(?) AND "
+ "C.usertype*=T.usertype " + "ORDER BY C.colid";
*/
/* new query, returns actual storage type of user defined data types
(at least for version "SQL Server/12.5/P/Generic/OS/1/OPT/Sat Jun 30 00:01:37 PDT 2001"
as given in the server attribute "DBMS_VER", run sp_server_info to get your version)
The subquery code is adapted from sp_help code
(one section displays "storage" data types for each column, for more information run:
use sybsystemprocs
go
sp_helptext sp_help
go
)
*/
private static String tableColumnSelect = "SELECT COLUMN_NAME = ISNULL(C.name, ''), "
+ " DATA_TYPE = ISNULL(real_types.Storage_type, T.name), C.length, "
+ " NUMERIC_PRECISION = C.prec, NUMERIC_SCALE = C.scale, IS_NULLABLE = CONVERT(BIT, (C.status & 0x08)) "
+ "FROM syscolumns C, systypes T, sysobjects A, "
+ " (select User_type = s.name, Storage_type = st.name from systypes s, systypes st "
+ " where s.type = st.type and s.usertype > 99 and "
+ " st.name not in ('sysname', 'longsysname', 'nchar', 'nvarchar') and st.usertype < 100) real_types "
+ "WHERE USER_NAME(A.uid) = ? AND A.id = C.id AND C.id = OBJECT_ID(?) AND C.usertype*=T.usertype "
+ " AND T.name*=real_types.User_type " + "ORDER BY C.colid";
Description: Currently, sybase user defined data types are just put into the mysql "create table" statement as is. This doesn't work, but each user defined data type is associated with an underlying storage type. The query to retrieve column information can be altered to provide the storage type when needed, thus allowing the resulting mysql statement to be valid. How to repeat: Take any sybase database containing a table with at least one column having a user defined data type. Convert to mysql and notice the sql error due to an invalid data type (the name of the user defined data type just gets copied into the mysql statement). Suggested fix: In the file ReverseEngineeringSybase.java, just replace the old tableColumnSelect value with this new one. As the comments state, this might not work with all versions of sybase, I'm no sybase expert. This code goes right before the reverseEngineerTableColumns function: /* old query, returns user defined data types private static String tableColumnSelect = "SELECT COLUMN_NAME = ISNULL(C.name, ''), " + " DATA_TYPE = T.name, C.length, NUMERIC_PRECISION = C.prec, " + " NUMERIC_SCALE = C.scale, " + " IS_NULLABLE = CONVERT(BIT, (C.status & 0x08)) " + "FROM syscolumns C, systypes T, sysobjects A " + "WHERE USER_NAME(A.uid) = ? AND " + " A.id = C.id AND C.id = OBJECT_ID(?) AND " + "C.usertype*=T.usertype " + "ORDER BY C.colid"; */ /* new query, returns actual storage type of user defined data types (at least for version "SQL Server/12.5/P/Generic/OS/1/OPT/Sat Jun 30 00:01:37 PDT 2001" as given in the server attribute "DBMS_VER", run sp_server_info to get your version) The subquery code is adapted from sp_help code (one section displays "storage" data types for each column, for more information run: use sybsystemprocs go sp_helptext sp_help go ) */ private static String tableColumnSelect = "SELECT COLUMN_NAME = ISNULL(C.name, ''), " + " DATA_TYPE = ISNULL(real_types.Storage_type, T.name), C.length, " + " NUMERIC_PRECISION = C.prec, NUMERIC_SCALE = C.scale, IS_NULLABLE = CONVERT(BIT, (C.status & 0x08)) " + "FROM syscolumns C, systypes T, sysobjects A, " + " (select User_type = s.name, Storage_type = st.name from systypes s, systypes st " + " where s.type = st.type and s.usertype > 99 and " + " st.name not in ('sysname', 'longsysname', 'nchar', 'nvarchar') and st.usertype < 100) real_types " + "WHERE USER_NAME(A.uid) = ? AND A.id = C.id AND C.id = OBJECT_ID(?) AND C.usertype*=T.usertype " + " AND T.name*=real_types.User_type " + "ORDER BY C.colid";