| Bug #24585 | Some 3rd party SW tools can't list procedures | ||
|---|---|---|---|
| Submitted: | 25 Nov 2006 12:06 | Modified: | 4 Jan 2008 11:06 |
| Reporter: | Hein Hoenjet | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 5.00.09-beta | OS: | Any |
| Assigned to: | Jess Balint | CPU Architecture: | Any |
| Tags: | ODBC5-RC | ||
[25 Nov 2006 12:06]
Hein Hoenjet
[27 Nov 2006 12:34]
Tonci Grgin
Hi Hein and thanks for your problem report. I must admit I understand none of it... >I have installed mysql-connector-odbc-5.00.09-beta-gpl-win32.msi and tried to retrieve the dbschema (according .NET) What does (according .NET) means? > and there is no table or column retrieved. Take MS generic ODBC client odbct32w.exe and you'll see that data is extracted easily using ODBC standard. > There no general ERD tool that will work together with MySQL 5. But all commercial databases do work. Did you tried our MySQL workbench? As far as I can tell you are trying to connect 3rd party SW to MySQL server via MySQL connector/ODBC v5 which is Beta... Did you try MyODBC 3.51, connector/J, connector/NET? MySQL connector/ODBC v5 is not yet ready (thus the Beta prefix) but other connectors are prety much stable and complete. So your feature request would be: Make MySQL connector/ODBC v5 work with ERDs I have?
[27 Nov 2006 13:30]
Hein Hoenjet
Hi Tonci Grgin >retrieve the dbschema (according .NET) >What does (according .NET) means? > Actually I am not familiar with the technical details as I am not a .NET programmer. But let's try an experiment. Pls download from http://www.sharewareplaza.com/Happy-Fish-Database-Designer-download_43616.html a trial version. It has nothing to do with this paricular software. I tested many more with exact the same (NO) results. Do the next: File --> New, choose from the list MySQL 5, Then under Database --> Connect to Database, create a connection and connect. Then under Database choose Reverse Engineer. Now there should appear tables and columns etc. Nut nothing happens. If you do this with version 3.51 then it will work, but another problem occurs, the DECIMAL is retrieved as VARCHAR, some ERD tools run beserk with this "feature". So my wish is simple, I would like to retrieve the DB schema in a correct manner preferred for version 5.0 via ODBC. > Regards, Hein
[27 Nov 2006 14:01]
Tonci Grgin
Hi Hein. > But let's try an experiment. It will take some time for me to be able to do this... > If you do this with version 3.51 then it will work, but another problem occurs, the DECIMAL is retrieved as VARCHAR, some ERD tools run beserk with this "feature". AFAIK it is ORACLE feature and I would like MySQL to have it implemented CHAR-way. New release of 3.51 will be available shortly. Well, you have refused to use our ERD tool, connector/J and connector/NET with no reason, yet yo uinsist on using *beta* connector >I would like to retrieve the DB schema in a correct manner preferred for version 5.0 via ODBC. Can I ask why? Surely I can download that tool and set this report to verified but I don't see the point right now... Just to be on the safe side I started MS generic ODBC client odbct32 and tested basic functionality of MySQL connector/ODBC v5: Full Connect(Default) Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3 Successfully connected to DSN '5on5Local'. SQLColumns: In: StatementHandle = 0x008B18D0, CatalogName = "test", NameLength1 = 4, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0, TableName = "user_rating_pairs", NameLength3 = 17, ColumnName = SQL_NULL_HANDLE, NameLength4 = 0 Return: SQL_SUCCESS=0 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" "test", "", "user_rating_pairs", "userId", 4, "integer", 11, 11, 0, 2, 0, <Null>, <Null>, 4, 0, 0, 1, "NO" "test", "", "user_rating_pairs", "movieId", 4, "integer", 11, 11, 0, 2, 0, <Null>, <Null>, 4, 0, 3342336, 2, "NO" "test", "", "user_rating_pairs", "rating", 7, "float", 12, 12, 31, 2, 0, <Null>, <Null>, 7, 0, 0, 3, "NO" "test", "", "user_rating_pairs", "tstamp", 93, "timestamp", 19, 19, 0, 0, 0, <Null>, <Null>, 9, 3, 19, 4, "NO" 4 rows fetched from 18 columns. -- and -- Full Connect(Default) Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3 Successfully connected to DSN '5on5Local'. SQLTables: In: StatementHandle = 0x008B18D0, CatalogName = "test", NameLength1 = 4, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0, TableName = SQL_NULL_HANDLE, NameLength3 = 0, TableType = "TABLE", NameLength4 = 5 Return: SQL_SUCCESS=0 Get Data All: "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "TABLE_TYPE", "REMARKS" "test", "", "a", "TABLE", <Null> "test", "", "aaa", "TABLE", <Null> "test", "", "ab", "TABLE", <Null> "test", "", "ab1", "TABLE", <Null> "test", "", "b", "TABLE", <Null> "test", "", "b22340", "TABLE", <Null> "test", "", "blackhole", "TABLE", <Null> "test", "", "blacklist", "TABLE", <Null> "test", "", "bs2", "TABLE", <Null> "test", "", "bug21770", "TABLE", <Null> "test", "", "bug22333", "TABLE", <Null> "test", "", "bug23657", "TABLE", <Null> "test", "", "bug_scenario", "TABLE", <Null> "test", "", "contact_activity", "TABLE", <Null> "test", "", "data1", "TABLE", <Null> "test", "", "datetimetest", "TABLE", <Null> "test", "", "educateur", "TABLE", <Null> "test", "", "enumbool", "TABLE", <Null> "test", "", "file", "TABLE", <Null> "test", "", "foo", "TABLE", <Null> "test", "", "ground_station_antenna", "TABLE", <Null> "test", "", "issue11418", "TABLE", <Null> "test", "", "issue12589b", "TABLE", <Null> "test", "", "issue12589bb", "TABLE", <Null> "test", "", "issue12589l", "TABLE", <Null> "test", "", "mast", "TABLE", <Null> "test", "", "modellvariable", "TABLE", <Null> "test", "", "modellvariable2", "TABLE", <Null> "test", "", "my_no_keys", "TABLE", <Null> "test", "", "my_no_keys_all_dups", "TABLE", <Null> "test", "", "my_primary_keys", "TABLE", <Null> "test", "", "my_unique_keys", "TABLE", <Null> "test", "", "my_unique_notnull_keys", "TABLE", <Null> "test", "", "mysysconf", "TABLE", <Null> "test", "", "notes", "TABLE", <Null> "test", "", "pbcatcol", "TABLE", <Null> "test", "", "pbcatedt", "TABLE", <Null> "test", "", "pbcatfmt", "TABLE", <Null> "test", "", "pbcattbl", "TABLE", <Null> "test", "", "pbcatvld", "TABLE", <Null> "test", "", "people", "TABLE", <Null> "test", "", "posts", "TABLE", <Null> "test", "", "practice", "TABLE", <Null> "test", "", "practice_client", "TABLE", <Null> "test", "", "recurse2", "TABLE", <Null> "test", "", "sales", "TABLE", <Null> "test", "", "stores", "TABLE", <Null> "test", "", "stringtest", "TABLE", <Null> "test", "", "tblplugin", "TABLE", <Null> "test", "", "tbltransactions", "TABLE", <Null> "test", "", "test", "TABLE", <Null> "test", "", "test1", "TABLE", <Null> "test", "", "test18531", "TABLE", <Null> "test", "", "test2", "TABLE", <Null> "test", "", "test20135", "TABLE", <Null> "test", "", "test20807", "TABLE", <Null> "test", "", "test21614", "TABLE", <Null> "test", "", "test5on5", "TABLE", <Null> "test", "", "test5on52", "TABLE", <Null> "test", "", "test5on53", "TABLE", <Null> "test", "", "test_child3", "TABLE", <Null> "test", "", "test_if_text_x_bug", "TABLE", <Null> "test", "", "test_view", "TABLE", <Null> "test", "", "testbinary", "TABLE", <Null> "test", "", "testbindparam", "TABLE", <Null> "test", "", "testbug22290", "TABLE", <Null> "test", "", "testbug2670", "TABLE", <Null> "test", "", "testint64", "TABLE", <Null> "test", "", "testprepared", "TABLE", <Null> "test", "", "teststream", "TABLE", <Null> "test", "", "testtable", "TABLE", <Null> "test", "", "testtest", "TABLE", <Null> "test", "", "timetest", "TABLE", <Null> "test", "", "titles", "TABLE", <Null> "test", "", "tstdec", "TABLE", <Null> "test", "", "tt1", "TABLE", <Null> "test", "", "user_rating_pairs", "TABLE", <Null> "test", "", "x_booltest", "TABLE", <Null> 78 rows fetched from 5 columns. As you can see, a calls needed for reverse engeneering work... So you should ask your team what ODBC standard your ERD tools use and provide me with at least SQL.log file from DM.
[27 Nov 2006 14:46]
Hein Hoenjet
Axle Studio Business Intelligence
Attachment: SQL.LOG.axledesigner (application/octet-stream, text), 51.81 KiB.
[27 Nov 2006 14:46]
Hein Hoenjet
OlivaNova MDS Modeler
Attachment: SQL.LOG.olivanova (application/octet-stream, text), 43.51 KiB.
[27 Nov 2006 14:47]
Hein Hoenjet
Happy Fish 3
Attachment: SQL.LOG.HF3 (application/octet-stream, text), 27.58 KiB.
[27 Nov 2006 14:48]
Hein Hoenjet
Hi, I have added 3 log files of 3 different tools. By the way, Access, SQL Server and also Access work okay with these tools via ODBC. Best regards, Hein
[27 Nov 2006 15:06]
Tonci Grgin
Hi Hein and thanks for the logs. They may prove usefull. You have refused to use our ERD tool, connector/J and connector/NET with no reason, instead you insist on using *beta* connector. You can not actually expect us to drop our plans and implement fix for some 3rd party ERD tool? What's preventing you from using connector/J and/or connector/NET and our ERD tool (Workbench)?
[27 Nov 2006 15:45]
Hein Hoenjet
Hi Hein and thanks for the logs. They may prove usefull. You have refused to use our ERD tool, connector/J and connector/NET with no reason, instead you insist on using *beta* connector. You can not actually expect us to drop our plans and implement fix for some 3rd party ERD tool? What's preventing you from using connector/J and/or connector/NET and our ERD tool (Workbench)? Hi Tonci Grgin, It is not that I refuse to use your ERD tool, it is only to report that there is a non working feature. There are a lot of tools out there like a Business Intelligence tool with cubes, a Model Driven architecture tool that completely generates EJB or ASP .NET applications based on UML. The ERD tool I mentioned was just an example. All these tools use the ODBC connectivity. No one wants to exclude MySQL for these developents. Greetz, Hein
[28 Nov 2006 10:27]
Tonci Grgin
Hi Hein and thanks for bringing this problem to our attention. Since MySQL connector/ODBC v5 is stil beta we can't get it to work with every 3rd party SW at this stage, sorry. From what I see in logs, happyfish is complying to v2 ODBC standard. So, for a start, please replace USER with UID when building connect string and set "Persist security info" to false. As for listing procedures, happy fish is using "NULL" for wildcard where it should be "%". We are fixing this. I have managed to get only the list of tables and fields in MySQL server 5.0.27BK on Win XP Pro SP2 localhost into HappyFish. Thanks for your interest in MySQL.
[28 Nov 2006 10:29]
Tonci Grgin
HappyFish project reversed from 5.0.27 database
Attachment: New Model 1.zip (application/x-zip-compressed, text), 9.57 KiB.
[28 Nov 2006 12:50]
Hein Hoenjet
Hi Tonci Grgin, Thanks for the hint. I assume some day it will be fixed. Best regards, Hein
[3 Dec 2007 8:29]
Tonci Grgin
Hein, can you repeat this problem with new versions of MyODBC (3.51 & 5.1)?
[4 Jan 2008 11:06]
Tonci Grgin
Testing latest 5.1.2 rev. 986 I found no problems. Tables, views, stored procedures, all imported in Happy Fish 3.3.6. Testing 3.51.23 on the same databases, found no problems. Closing the report now.
[4 Jan 2008 11:07]
Tonci Grgin
HappyFish object inspector SS from one of test DBs
Attachment: Bug24585-3-51.JPG (image/jpeg, text), 28.22 KiB.
