Modifying (or adding to) a dataset returned by a stored proc

[MerijnB]

With the risk of asking something very stupid :p

I'm trying to modify or add a record to a dataset returned by a stored procedure. I don't any errors, but the changes are just not stored in the database.

Can anyone tell me what I'm doing wrong?

[trupka]

It doesn't seem like database (mysql) specific problem so maybe I can help. What components are you using to achieve this? Can you post a little sample?

[MerijnB]

It doesn't seem like database (mysql) specific problem so maybe I can help. What components are you using to achieve this? Can you post a little sample?

Hi trupka, thanks for your reply. The setup is very simple, I have a global TZConnection:

fZConnection := TZConnection.Create(Self);
fZConnection.HostName := 'localhost';
fZConnection.User := 'root';
fZConnection.Password := 'password';
fZConnection.Database := 'database';
fZConnection.Protocol := 'mysql';
// line below is needed for the stored procedures which result a dataset
fZConnection.Properties.Add('CLIENT_MULTI_STATEMENTS=1');
fZConnection.Connect();

And a global TZQuery:
fQuery := TZQuery.Create(Self);
fQuery.Connection := fZConnection;

When calling the stored procedure, I do this:
fQuery.SQL.Text := 'call my_stored_procedure()';
fQuery.Open();

It all seems to work ok, untill I try to edit a record in the dataset or try to append to it. No errors, but the changes just aren't written in the database.

[seawolf]

I think it's the right behaviour .. I mean How to know which tables are involved and their relationship?

I think the problem could be is not permit of the query to go on edit mode

[MerijnB]

I think it's the right behaviour .. I mean How to know which tables are involved and their relationship?
mode

I was starting to expect exactly this and was hoping somebody here can acknowledge it, since I'd except to find more posts in the form about this.

[trupka]

I think it's the right behaviour .. I mean How to know which tables are involved and their relationship?

There is no way TZQuery can now that - that information is "hidden" inside stored procedure code and that's "by design". IMO, solution is TZUpdateSQL.

I was starting to expect exactly this and was hoping somebody here can acknowledge it, since I'd except to find more posts in the form about this.

Try this:

Code: Select all

And a global TZQuery:
fQuery := TZQuery.Create(Self);
uQuery := TZUpdateSQL.Create(self);
uQuery.ModifySQL.Text := '-- write you own, only you know what tables need modification';
uQuery.InsertSQL.Text := '-- write you own, only you know where to insert data';
uQuery.DeleteSQL.Text := '-- write you own, TZQuery and stored proc doesn''t know what to delete';
fQuery.UpdateObject := uQuery;
fQuery.Connection := fZConnection;

When calling the stored procedure, I do this:
fQuery.SQL.Text := 'call my_stored_procedure()';
fQuery.Open();