TSQLStoredProc and StoredProcName

rshera · Post by **rshera** » Sun 30 May 2010 02:54

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

Dimon · Post by **Dimon** » Mon 31 May 2010 07:03

I can not reproduce the problem. Please specify the exact version of DbxMda you are using.

rshera · Post by **rshera** » Tue 01 Jun 2010 10:30

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

Dimon · Post by **Dimon** » Tue 01 Jun 2010 14:21

Please check if the problem can be reproduced when using the TMyStoredProc component of MyDAC.

rshera · Post by **rshera** » Tue 01 Jun 2010 18:34

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.

Dimon · Post by **Dimon** » Wed 02 Jun 2010 09:23

Thank you for information. We have fixed this problem. This fix will be included in the next DbxMda build.

rshera · Post by **rshera** » Thu 03 Jun 2010 04:35

Excellent!

Is there any way I can get the updated version sooner? I'm trying to convert a large number of screens so getting the fix would be appreciated.

Rob

Dimon · Post by **Dimon** » Thu 03 Jun 2010 09:38

The next DbxMda build will be released in the course of the next week.