Help wiith SQLDB

[enric]

Hello .

I'm new in lazarus.

I try the SQLdb components with a FIREBIRD2 Super Server in an AMD64 with Debian.

I'put this components on a Form

TIBConnection
TSQLTransaction
TSQLQuery
TDatasource
TDBgris

It work fine when i INSERT and REPLACE but when i try to DELETE i have this errror
Extrenal SIGSEGV
Adress $0000000000653913
BB_TBUFDATASET_$_GETRECNO$$LONGINT

Can you Help Me?

[DougNettleton]

Enric:

I have a similar setup but I'm using a data module.  I do insert / deletes with a separate update Query. The following code works for me with a recent 2.1.5 snapshot   in Windows.

 if MessageDlg('Confirm Purge Of Deleted Records?', mtConfirmation,
    [mbYes, mbNo], 0) = mrYes then
    begin
      UbpsDataMod.SQLUpdateQuery.SQL.Clear;
      UbpsDataMod.SQLUpdateQuery.SQL.Add('delete from provcode');
      UbpsDataMod.SQLUpdateQuery.SQL.Add('where delflag = ''Y''');
      UbpsDataMod.SQLUpdateQuery.ExecSQL;
      UbpsDataMod.SQLTransaction1.CommitRetaining;
      UbpsDataMod.SQLQuery2.Close;
      UbpsDataMod.SQLQuery2.Open;
    end;

If your trying to use the UpdateSQL, InsertSQL and DeleteSQL properties of the TSQLQuery, maybe someone else can jump in and talk about how to use them.  I found some documentation about the corresponding properties in the FIBL package.

Since you're using Firebire 2 maybe you can tell me how to set the "transaction isolation level" presumeably in the params property of the TSQLTransaction.

TIA,

Doug

[enric]

I've set  "concurrency" and "no wait" in params of tibconnection

now I try to delete records in a separate query.

Enric

[enric]

Helo

I've debugged the program an it crash in the dbgrids.pas code

line :1354    aPos := FDataLink.DataSet.RecNo - 1; // RecNo is 1 based

when I've applied the changes in the database Recno don't 'exists;


Enric

[HowardH]

I found some documentation about the corresponding properties in the FIBL package.

Would you mind telling me where to find this documentation? Is this package something that might be more useful than SQLdb? Is it open source, or commercial?

[HowardH]

Enric:If your trying to use the UpdateSQL, InsertSQL and DeleteSQL properties of the TSQLQuery, maybe someone else can jump in and talk about how to use them.

Doesn't appear likely that will happen. I posted a question on these properties about 8 months ago, and I have found some other folks that have posted similar inquiries, all unanswered.

[Jobra]

You have to write a normal SQL query in TSQLQuery.UpdateSQL.Text. As "variables" for the edited values you can use e.g. :fieldname.

Example:
UPDATE books
SET title = :title,
autor = :autor,
description = :description
WHERE id = :id

I just had the same question like you and figured it out by experimenting.

Kind regards
Jonas

[HowardH]

You have to write a normal SQL query in TSQLQuery.UpdateSQL.Text. As "variables" for the edited values you can use e.g. :fieldname.

Example:
UPDATE books
SET title = :title,
autor = :autor,
description = :description
WHERE id = :id

I just had the same question like you and figured it out by experimenting.

Kind regards
Jonas

I switched to PostgreSQL, and the UpdateSQL is generated properly by those components. This turned out to be a LOT less work, and less error-prone. While I like MySQL, the Lazarus components for MySQL don't appear to be quite ready for prime time.

[BlackCat]

SURE.. THIS IS MY EXAMPLE ABOUT MYSQL AND SQLDB...MAYBE YOU NEED CHECK MY PERSONAL POST BECAUSE, I ASK THE SAME QUESTIONS ABOUT MYSQL...
I SEND A PERSONAL EMAIL TO HOWARDH, CHECK YOU GMAIL ACCOUNT please, but is the same information like this is
i try explain you how i made my form, ok, sorry my bad english...
                i 'm using W Vista, and also work in W Xp SP2, using Lazarus 0.9.24 Instalation i don't change anything else...
                in some first examples about mysql i made, i add mysql.dll, if you have a problem when run your project  similar  to "project... mysql.dll... not found" you need add in your Project Folder the mysql.dll ok? but in this example, i check and i don't use anymore mysql.dll i forgot why i need it in my first examples
 
First
1.- I made a form using SQLQUERY, SQLTRANSACTION, SQLCONNECTION and DATASOURCE  with NAVIGATOR and DBGRID and DBEDIT'S  alias ==[7PACK] check the finish this email, this reference
2.-connect all the component except in the SQLQUERY.datasource:=(none) in blank, don't but nothing, ok.
3.-change also in  the Slquery properties this values:
    UpdateMode:=UpWhereChange
    UsePrimaryKeyAsKey:=true
           >>(NOTE: IS VERY IMPORTANT ADD A PRIMARY KEY IN YOUR TABLE FROM MYSQL, IS THE REFERENCES TO CHANGE ON EDIT, UPDATE,ETC.IN MYSQL-SQLQUERY, OK?)
4.- in sqlconnection change:
    Streamedconnected:=true
 
Now, you only need change in the sqlquery EVENTS, i use these events: After(Close,Delete,Edit,Insert,Post)  i dont' know what happend with "before" events in sqlquery, with after events work good in my form.
 
5.-this is the code i use un each events AFTER:
 
procedure TForm1.SQLQuery1AfterDelete(DataSet: TDataSet);
begin
 SQLQuery1.ApplyUpdates;
 SQLTransaction1.Commit;
end;                                                     ;                                                                                    
 
procedure TForm1.SQLQuery1AfterPost(DataSet: TDataSet); //DESPUES DE POST
begin
    SQLQuery1.ApplyUpdates;
    SQLTransaction1.Commit;
end;                                                                    
 
Right???
don't use Navigator.click event, i spend my time, and i can't found the correct solution to use it.. ok? is an advice for you only,
 
add only AfterPost and AfterDelete Events in Slquery and work fine...!! but, i use also dbedit's:
6.- in dbedit's fill in the  next properties:
 
datafield:=[your datafield in your table in mysql e.g. mytable(name var(15), addres var(20),...etc,etc ...... primary key (name) )]
dbedit1.datafield:=name
dbedit1.datasource:=(none)  [why?, i use a button using  this instructions.... SqlQuery.Close, SqlQuery.sql.Text:="select * from mytable"..., SqlQuery.Open dbedit.DataSource:=datasource1;..... ok? an also work if you add the datasource in design time, but I HAVE A PROBLEM HERE, ABOUT DBEDITS AND MULTIPLES DGRID USING PAGECONTROL.. I ASK YOU AT FINISH]
 
and works..!!
 
¿Now, Can you help me about the next problem....?
Now, my problem about BDEDIT's is because i use multiples Sheet's in the page control with diferents tables, i can't re-use the same Sql,Trans, Connect, Datas... with diferents dbgrid and dbedit's, because the program .exe give me and error about..."can't found name fields in you....etc, etc"
i was thinking about that, i think is because the sqlquery don't free the field in the dbedit and don't break the "connection in the ¿buffer?(the relation in execute time)" between the Query-DBedit.. like "persistant connection"... right??
 
try use [7PACK] in a pagecontrol using in the first sheet one table, and in the second sheet another table,
try use the same bdedit's for your tables, put this outside the page control, directly in the form, or in each sheet... ok? how you ever use pagecontrol in delphi?..is  similar...
TO SOLVE MY PROBLEM I USE multiples [7PACK]  for each table in each sheet in the pagecontrol.. but my question is.. what about the Ram?? i am wrong if i use multiples 7PACKS???the  conexion is PERMANENT and PERSISTENT with .exe and Mysq with my multiples 7PACKS i use...??
 
IF YOU KNOW OTHER EXAMPLES, ABOUT MYSQL AN LAZARUS, I FEEL VERY INTERESTING ABOUT THIS, PLEASE CONTACT ME...
because, i feel i feel confuse if my personal conclusions are good or bad.. i check a lot of forums in spanish and english about this, but  i found only pieces of information about this and allow me make my example and the posibility to explain you and other.. check in lazarus Forum in Lazarus Wiki in Database questions, I'm BlackCat User in the forum..
 
is all about your questions and my possible solution...
Please, if you made or checked Mysql components, said me what do you thing about my "solution"  or your opinion. please...
 
Regards...
Mrf from Slp, Mexico

[HowardH]

Thanks Mrf! It appears that you have supplied the missing information I could have used if I were still using MySQL for this application. Unfortunately, when I needed it, the folks here that had that information were too busy chiding me for posting "incomplete" examples, or just repeatedly giving me non-answers. The key clue is the code you added to the "after" events, which now makes sense to me.

This example a couple of months ago would have saved me a LOT of time. If I ever decide to switch back to MySQL, I will refer back to this post. But for now, I was able to make PostgreSQL work the way I wanted it to, with minimal coding, and I'm basically done with the application.

I'm sure that there are others who will stumble across the same problem, and they will benefit from your example.