Re: [Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection
You are correct, I missed that one completely. On Sat, Aug 30, 2008 at 12:06 AM, Jesus Reyes [EMAIL PROTECTED] wrote: --- El jue 28-ago-08, Howard Lee Harkness [EMAIL PROTECTED] escribió: De:: Howard Lee Harkness [EMAIL PROTECTED] Asunto: Re: [Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection A: General mailing list lazarus@lazarus.freepascal.org Fecha: jueves, 28 agosto, 2008, 3:21 pm After several rounds of non-answers here (seems the developers just assume everybody else should know the internals of everything, which is not realistic), a member of the Lazarus forum posted the answer to this problem. The ApplyUpdates call can be placed in the after event handlers. Fairly simple, and in retrospect, fairly obvious. Knowing that two months ago would have saved me a lot of time. Meanwhile, I switched to PostgreSQL, and the components for that DB don't appear to have the same problem. Two months ago? it means you didn't read it: http://article.gmane.org/gmane.comp.ide.lazarus.general/25599/match=advice+creating+database+aware+application+lazarus Jesus Reyes A. __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! Regístrate ya - http://correo.yahoo.com.mx/ ___ Lazarus mailing list Lazarus@lazarus.freepascal.org http://www.lazarus.freepascal.org/mailman/listinfo/lazarus -- Howard Lee Harkness ___ Lazarus mailing list Lazarus@lazarus.freepascal.org http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection
On Thu, Aug 28, 2008 at 4:37 PM, Joost van der Sluis [EMAIL PROTECTED] wrote: So now I understand what your problem is: actually it's not a problem with applyUpdates, but you want that it's called 'automagically'. I think you'd better place the ApplyUpdates in the after-scroll event, in that case. That's much easier. But your approach will also work. Actually, I asked more than once (trying to phrase it differently each time) Where does the call to ApplyUpdates need to be? and got similar non-answers each time. But life goes on, and the PostgreSQL components appear to do mostly what I had in mind. If I should ever find time, I might look into why the MySQL components didn't. However, time is at a premium these days, and probably always will be. Once I finish this little side project, I will probably lose all interest in Lazarus/FPC/PostgreSQL/MySQL anyway, and concentrate on my day job as a high-performance graphics tools programmer using C++. The only reason I was ever interested in Lazarus is because Delphi was the fastest, easiest way I knew of to slap a GUI onto a trivial DB program (and the program I was trying to write should have been REALLY trivial), and I didn't have access to a copy of Delphi (too expensive for a one-off side project, anyway). -- Howard Lee Harkness www.celtic-fiddler.com ___ Lazarus mailing list Lazarus@lazarus.freepascal.org http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection
--- El jue 28-ago-08, Howard Lee Harkness [EMAIL PROTECTED] escribió: De:: Howard Lee Harkness [EMAIL PROTECTED] Asunto: Re: [Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection A: General mailing list lazarus@lazarus.freepascal.org Fecha: jueves, 28 agosto, 2008, 3:21 pm After several rounds of non-answers here (seems the developers just assume everybody else should know the internals of everything, which is not realistic), a member of the Lazarus forum posted the answer to this problem. The ApplyUpdates call can be placed in the after event handlers. Fairly simple, and in retrospect, fairly obvious. Knowing that two months ago would have saved me a lot of time. Meanwhile, I switched to PostgreSQL, and the components for that DB don't appear to have the same problem. Two months ago? it means you didn't read it: http://article.gmane.org/gmane.comp.ide.lazarus.general/25599/match=advice+creating+database+aware+application+lazarus Jesus Reyes A. __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! Regístrate ya - http://correo.yahoo.com.mx/ ___ Lazarus mailing list Lazarus@lazarus.freepascal.org http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection
After several rounds of non-answers here (seems the developers just assume everybody else should know the internals of everything, which is not realistic), a member of the Lazarus forum posted the answer to this problem. The ApplyUpdates call can be placed in the after event handlers. Fairly simple, and in retrospect, fairly obvious. Knowing that two months ago would have saved me a lot of time. Meanwhile, I switched to PostgreSQL, and the components for that DB don't appear to have the same problem. On Sun, Jun 22, 2008 at 8:48 AM, Joost van der Sluis [EMAIL PROTECTED] wrote: Op zaterdag 21-06-2008 om 14:04 uur [tijdzone -0500], schreef Howard Lee Harkness: On Thu, Jun 19, 2008 at 8:22 AM, Joost van der Sluis [EMAIL PROTECTED] wrote: Op zaterdag 14-06-2008 om 16:06 uur [tijdzone -0500], schreef Howard Lee Harkness: However, it doesn't seem to matter what I put in for nbDelete. I always get the error There are no fields found to generate the where-clause if I do ApplyUpdates for nbDelete. If I put nothing in there, the field appears to be deleted, but it is still in the table, and comes back to the grid when I press refresh. Yes, and yes. See the code that I posted at the beginning of the thread -- if you see a problem with the code , please tell me what I'm doing wrong, or not doing right. There is definitely something wrong with it, because it doesn't work. If I left something out, tell me and I can just zip up the whole project and email it to you. Sorry, but with the code you've sent, it's truely impossible to get the error-message you claim that you got. There even is no 'ApplyUpdates' in your code. That code is completely irrelevent - it's the code of an empy program. Please, don't send puzzles to us, we can't help you if you do. For an example look at the fpc-code: packages/fcl-db/sqldb/examples Joost. ___ Lazarus mailing list Lazarus@lazarus.freepascal.org http://www.lazarus.freepascal.org/mailman/listinfo/lazarus -- Howard Lee Harkness ___ Lazarus mailing list Lazarus@lazarus.freepascal.org http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection
Op donderdag 28-08-2008 om 15:21 uur [tijdzone -0500], schreef Howard Lee Harkness: After several rounds of non-answers here (seems the developers just assume everybody else should know the internals of everything, which is not realistic), Assuming that we can understand your problem without knowing the internals of what you're doing is also not realistic. As I tried to explain to you, I coudn't figure out what you were trying to do, and how you tried it. In that case: we can't help you. a member of the Lazarus forum posted the answer to this problem. The ApplyUpdates call can be placed in the after event handlers. Fairly simple, and in retrospect, fairly obvious. So now I understand what your problem is: actually it's not a problem with applyUpdates, but you want that it's called 'automagically'. I think you'd better place the ApplyUpdates in the after-scroll event, in that case. That's much easier. But your approach will also work. Knowing that two months ago would have saved me a lot of time. This could also help: http://www.catb.org/~esr/faqs/smart-questions.html Meanwhile, I switched to PostgreSQL, and the components for that DB don't appear to have the same problem. That's always a good idea. ;) Joost. ___ Lazarus mailing list Lazarus@lazarus.freepascal.org http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection
Op zaterdag 21-06-2008 om 14:04 uur [tijdzone -0500], schreef Howard Lee Harkness: On Thu, Jun 19, 2008 at 8:22 AM, Joost van der Sluis [EMAIL PROTECTED] wrote: Op zaterdag 14-06-2008 om 16:06 uur [tijdzone -0500], schreef Howard Lee Harkness: However, it doesn't seem to matter what I put in for nbDelete. I always get the error There are no fields found to generate the where-clause if I do ApplyUpdates for nbDelete. If I put nothing in there, the field appears to be deleted, but it is still in the table, and comes back to the grid when I press refresh. Yes, and yes. See the code that I posted at the beginning of the thread -- if you see a problem with the code , please tell me what I'm doing wrong, or not doing right. There is definitely something wrong with it, because it doesn't work. If I left something out, tell me and I can just zip up the whole project and email it to you. Sorry, but with the code you've sent, it's truely impossible to get the error-message you claim that you got. There even is no 'ApplyUpdates' in your code. That code is completely irrelevent - it's the code of an empy program. Please, don't send puzzles to us, we can't help you if you do. For an example look at the fpc-code: packages/fcl-db/sqldb/examples Joost. ___ Lazarus mailing list Lazarus@lazarus.freepascal.org http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection
You can disregard this mail, in the mean time most issues are cleared out. ;) Joost. Op zondag 22-06-2008 om 15:48 uur [tijdzone +0200], schreef Joost van der Sluis: Op zaterdag 21-06-2008 om 14:04 uur [tijdzone -0500], schreef Howard Lee Harkness: On Thu, Jun 19, 2008 at 8:22 AM, Joost van der Sluis [EMAIL PROTECTED] wrote: Op zaterdag 14-06-2008 om 16:06 uur [tijdzone -0500], schreef Howard Lee Harkness: However, it doesn't seem to matter what I put in for nbDelete. I always get the error There are no fields found to generate the where-clause if I do ApplyUpdates for nbDelete. If I put nothing in there, the field appears to be deleted, but it is still in the table, and comes back to the grid when I press refresh. Yes, and yes. See the code that I posted at the beginning of the thread -- if you see a problem with the code , please tell me what I'm doing wrong, or not doing right. There is definitely something wrong with it, because it doesn't work. If I left something out, tell me and I can just zip up the whole project and email it to you. Sorry, but with the code you've sent, it's truely impossible to get the error-message you claim that you got. There even is no 'ApplyUpdates' in your code. That code is completely irrelevent - it's the code of an empy program. Please, don't send puzzles to us, we can't help you if you do. For an example look at the fpc-code: packages/fcl-db/sqldb/examples Joost. ___ Lazarus mailing list Lazarus@lazarus.freepascal.org http://www.lazarus.freepascal.org/mailman/listinfo/lazarus -- ___ Lazarus mailing list Lazarus@lazarus.freepascal.org http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection
On Thu, Jun 19, 2008 at 8:22 AM, Joost van der Sluis [EMAIL PROTECTED] wrote:
Op zaterdag 14-06-2008 om 16:06 uur [tijdzone -0500], schreef Howard Lee
Harkness:
However, it doesn't seem to matter what I put in for nbDelete. I
always get the error There are no fields found to generate the
where-clause if I do ApplyUpdates for nbDelete. If I put nothing in
there, the field appears to be deleted, but it is still in the table,
and comes back to the grid when I press refresh.
Does your table has a primary key? And do you have set the
'UsePrimaryKeyAsKey' property to true?
Yes, and yes. See the code that I posted at the beginning of the thread --
if you see a problem with the code , please tell me what I'm doing wrong, or
not doing right. There is definitely something wrong with it, because it
doesn't work. If I left something out, tell me and I can just zip up the
whole project and email it to you.
sqldb tries to create an delete-query for you, but to do that it has to
indentify the record that should be deleted. Normally it uses the
primary key for that. If there is no primary key, you have to tell it
which fields should be included as a 'key'. (ie: be a part of the
where-statement in the delete query: 'delect from table where ...)
Things would be a lot easier for me if only I could find a working example
of a small database program using MySQL and Lazarus. I've found a few
examples, but I have been unable to make any of them work. Currently, I'm
about to download the latest Lazarus/FPC versions to see if something
magically works, but it seems to me that I really ought be able to get a
trivial program like the one I posted at the beginning of the thread to work
with minimal effort.
Here again is the code that I posted in the first part of the thread:
To start with, here's a sample database setup:
CREATE TABLE IF NOT EXISTS ZIPCODE
(
ZIPID INT NOT NULL AUTO_INCREMENT,
ZIP VARCHAR(5) NOT NULL,
ZIPEXT VARCHAR(4),
CITY VARCHAR(40),
STATE VARCHAR(2),
PRIMARY KEY(ZIPID) -- YES! there is a primary key!
);
INSERT INTO ZIPCODE (ZIP,ZIPEXT,CITY,STATE) VALUES
('01814',NULL,'Lawrence','MA')
,('13502',NULL,'Utica','NY')
,('22030',NULL,'Fairfax','VA')
,('27030',NULL,'Mt. Airy','NC')
,('27103',NULL,'Winston-Salem','NC')
,('27104',NULL,'Winston-salem','NC')
,('27106',NULL,'Winston-Salem','NC')
I started up Lazarus, created an application, and put a DBGrid,
DBNavigator, a MySQL50Connection, an SQLTransaction, an SQLQuery, and
a DataSource components on it. Here is the Form File:
object Form1: TForm1
Left = 307
Height = 300
Top = 180
Width = 602
HorzScrollBar.Page = 601
VertScrollBar.Page = 299
ActiveControl = DBGrid1
Caption = 'Form1'
ClientHeight = 300
ClientWidth = 602
Position = poDesktopCenter
object DBGrid1: TDBGrid
Left = 24
Height = 201
Top = 32
Width = 560
DataSource = Datasource1
FixedColor = clBtnFace
FixedHotColor = cl3DLight
Options = [dgEditing, dgTitles, dgIndicator, dgColumnResize,
dgColumnMove, dgColLines, dgRowLines, dgTabs, dgAlwaysShowSelection,
dgConfirmDelete, dgCancelOnExit]
OptionsExtra = [dgeAutoColumns, dgeCheckboxColumn]
ParentColor = False
TabOrder = 0
TabStop = True
end
object DBNavigator1: TDBNavigator
Left = 152
Height = 25
Top = 248
Width = 241
BevelOuter = bvNone
ClientHeight = 25
ClientWidth = 241
DataSource = Datasource1
end
object MySQL50Connection1: TMySQL50Connection
Connected = True
Streamedconnected = True
DatabaseName = 'test'
Password = 'test'
Transaction = SQLTransaction1
UserName = 'root'
left = 24
top = 9
end
object SQLQuery1: TSQLQuery
Active = True
Database = MySQL50Connection1
Transaction = SQLTransaction1
SQL.Strings = (
'select * from zipcode'
'order by zip'
)
Params =
UpdateMode = upWhereChanged
UsePrimaryKeyAsKey = True// YES! UsePrimaryKeyAsKey is set to
True!
ParseSQL = True
left = 24
top = 44
end
object SQLTransaction1: TSQLTransaction
Database = MySQL50Connection1
left = 59
top = 9
end
object Datasource1: TDatasource
DataSet = SQLQuery1
left = 59
top = 44
end
end
I did not add any code at all to the main PAS file, so the project and
source are as follows:
program zipcode;
{$mode objfpc}{$H+}
uses
{$IFDEF UNIX}{$IFDEF UseCThreads}
cthreads,
{$ENDIF}{$ENDIF}
Interfaces, // this includes the LCL widgetset
Forms
{ you can add units after this }, zipcode, SQLDBLaz;
begin
Application.Initialize;
Application.CreateForm(TForm1, Form1);
Application.Run;
end.
+
unit Zipcode;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs,
mysql50conn, sqldb, db, DBGrids, DbCtrls;
type
{ TForm1 }
TForm1 = class(TForm)
Datasource1: TDatasource;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
MySQL50Connection1: TMySQL50Connection;
SQLQuery1: TSQLQuery;
SQLTransaction1: TSQLTransaction;
Re: [Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection
Op zaterdag 14-06-2008 om 16:06 uur [tijdzone -0500], schreef Howard Lee Harkness: However, it doesn't seem to matter what I put in for nbDelete. I always get the error There are no fields found to generate the where-clause if I do ApplyUpdates for nbDelete. If I put nothing in there, the field appears to be deleted, but it is still in the table, and comes back to the grid when I press refresh. Does your table has a primary key? And do you have set the 'UsePrimaryKeyAsKey' property to true? sqldb tries to create an delete-query for you, but to do that it has to indentify the record that should be deleted. Normally it uses the primary key for that. If there is no primary key, you have to tell it which fields should be included as a 'key'. (ie: be a part of the where-statement in the delete query: 'delect from table where ...) Joost. ___ Lazarus mailing list Lazarus@lazarus.freepascal.org http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection
Ok, I tried calling ApplyUpdates, and I did not detect any difference. Could
you perhaps show me in the code below exactly WHERE I need to call
ApplyUpdates?
On Tue, May 27, 2008 at 5:00 AM, Joost van der Sluis [EMAIL PROTECTED] wrote:
That's true. You have to call 'TSQLQuery.ApplyUpdates' Then all changes
in the local, in-memory changes-buffer are converted into
update/insert/delete statements and executed on the underlying database.
For those connections that do support transactions, you also have to do
TSQLTransaction.Commit(Retaining).
unit Zipcode;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs,
mysql50conn, sqldb, db, DBGrids, DbCtrls;
type
{ TForm1 }
TForm1 = class(TForm)
Datasource1: TDatasource;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
MySQL50Connection1: TMySQL50Connection;
SQLQuery1: TSQLQuery;
SQLTransaction1: TSQLTransaction;
private
{ private declarations }
public
{ public declarations }
end;
var
Form1: TForm1;
implementation
initialization
{$I zipcode.lrs}
end.
--
Howard Lee Harkness
___
Lazarus mailing list
Lazarus@lazarus.freepascal.org
http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Need some help with UpdateSQL in TSQLQuery and TMySQL50Connection
I was able to get the Insert and Refresh buttons to work with code like
this:
case Button of
nbPost: SQLQuery1.Applyupdates;
nbRefresh: begin SQLQuery1.Close; SQLQuery1.Open; end;
...
end;
However, it doesn't seem to matter what I put in for nbDelete. I always get
the error There are no fields found to generate the where-clause if I do
ApplyUpdates for nbDelete. If I put nothing in there, the field appears to
be deleted, but it is still in the table, and comes back to the grid when I
press refresh.
On Sat, Jun 14, 2008 at 3:22 PM, Howard Lee Harkness
[EMAIL PROTECTED] wrote:
Ok, I tried calling ApplyUpdates, and I did not detect any difference.
Could you perhaps show me in the code below exactly WHERE I need to call
ApplyUpdates?
On Tue, May 27, 2008 at 5:00 AM, Joost van der Sluis [EMAIL PROTECTED]
wrote:
That's true. You have to call 'TSQLQuery.ApplyUpdates' Then all changes
in the local, in-memory changes-buffer are converted into
update/insert/delete statements and executed on the underlying database.
For those connections that do support transactions, you also have to do
TSQLTransaction.Commit(Retaining).
unit Zipcode;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs,
mysql50conn, sqldb, db, DBGrids, DbCtrls;
type
{ TForm1 }
TForm1 = class(TForm)
Datasource1: TDatasource;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
MySQL50Connection1: TMySQL50Connection;
SQLQuery1: TSQLQuery;
SQLTransaction1: TSQLTransaction;
private
{ private declarations }
public
{ public declarations }
end;
var
Form1: TForm1;
implementation
initialization
{$I zipcode.lrs}
end.
--
Howard Lee Harkness
--
Howard Lee Harkness
___
Lazarus mailing list
Lazarus@lazarus.freepascal.org
http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
