TSQLStoredProc and StoredProcName
TSQLStoredProc and StoredProcName
If I use the standard MySQL driver that comes with Delphi 2010 dbExpress, then I can look up the storedprocname from the drop-down list - ie I can see all the procedures. (This is at design time.)
With the DevArt Driver (I've tried DevArtMySQL and DevArtMySQLDirect in the TSQLConnection) I cannot retrieve the procedure name in this way. In fact, clicking on the down arrow makes Delphi hang, and I have to shut it down and restart it.
The TSQLConnection is using root as the user.
Should I be able to retrieve stored procedures and functions using this component at design time?
Thanks
Rob
With the DevArt Driver (I've tried DevArtMySQL and DevArtMySQLDirect in the TSQLConnection) I cannot retrieve the procedure name in this way. In fact, clicking on the down arrow makes Delphi hang, and I have to shut it down and restart it.
The TSQLConnection is using root as the user.
Should I be able to retrieve stored procedures and functions using this component at design time?
Thanks
Rob
Hi Dimon
I think I can see what is happening. I took a Wireshark packet capture, and the component is actually querying the MySQL database. The database has about 210 stored procedures and about 40 stored functions, and the query is taking about 3-4 minutes to complete, so it appears to be hanging.
Running the component against a smaller database with say 10 stored procedures and the drop-down works as expected.
Interestingly, running the same query (SELECT * FROM information_schema.ROUTINES WHERE (ROUTINE_SCHEMA = '') from MySQL Browser took less than a second.
Looking up a table or view is very quick, but it seems to be doing a show tables rather than querying the information_schema.
I am running Delphi Pro 2010 with update 4 and datapack update 5 applied. The devart dbExpress component is version 4.5.0.22 (standard). I've also got MyDAC 5.90.0.58, dbMonitor 3.0.2.0, and dbForge Fusion 3.60 loaded.
My workstation is Windows 7 64 bit fully patched.
The MySQL database is 5.1.47 community running running on a SUSE Linux 10.3 server attached via a local network.
Not sure if there is anything that can be done about the speed issue, but I guess I can just type the procedure names in manually!
Rob
I think I can see what is happening. I took a Wireshark packet capture, and the component is actually querying the MySQL database. The database has about 210 stored procedures and about 40 stored functions, and the query is taking about 3-4 minutes to complete, so it appears to be hanging.
Running the component against a smaller database with say 10 stored procedures and the drop-down works as expected.
Interestingly, running the same query (SELECT * FROM information_schema.ROUTINES WHERE (ROUTINE_SCHEMA = '') from MySQL Browser took less than a second.
Looking up a table or view is very quick, but it seems to be doing a show tables rather than querying the information_schema.
I am running Delphi Pro 2010 with update 4 and datapack update 5 applied. The devart dbExpress component is version 4.5.0.22 (standard). I've also got MyDAC 5.90.0.58, dbMonitor 3.0.2.0, and dbForge Fusion 3.60 loaded.
My workstation is Windows 7 64 bit fully patched.
The MySQL database is 5.1.47 community running running on a SUSE Linux 10.3 server attached via a local network.
Not sure if there is anything that can be done about the speed issue, but I guess I can just type the procedure names in manually!
Rob
No, MyDAC works fine.
I notice from the captures that the MyDAC component uses a different query:
SELECT ROUTINE_NAME FROM information_schema.routines WHERE LOWER(routine_schema) = '' ORDER BY ROUTINE_NAME
rather than the dbExpress component which uses:
SELECT * FROM information_schema.ROUTINES WHERE (ROUTINE_SCHEMA = ''
The dbExpress version is retuning a lot more data since it has to get all the details of each stored procedure.
I notice from the captures that the MyDAC component uses a different query:
SELECT ROUTINE_NAME FROM information_schema.routines WHERE LOWER(routine_schema) = '' ORDER BY ROUTINE_NAME
rather than the dbExpress component which uses:
SELECT * FROM information_schema.ROUTINES WHERE (ROUTINE_SCHEMA = ''
The dbExpress version is retuning a lot more data since it has to get all the details of each stored procedure.