Update Database via SQL

[nanix]

Hello friends,

My problem is that how to update database with SQL?

First for example i create items in DB but that just keeps adding them becouse the code is launched from timer.


Possible?

[hoika]

Hm ?

More info please.
Show us some code.

Update Table TabXXX Set Name='Hoika' Where Id=10


Heiko

[nanix]

Well this is concept code but it should do the trick...but how should it look for SQL?

Delphi-Quellcode:

if item then begin
           
           for i:=0 to 9 do begin
           ABSTable1.Open;
           ABSTable1.Insert; // adds new record into table

           ABSTable1.Fields.FieldByName('FirstName').AsString:='John';
           ABSTable1.Fields.FieldByName('LastName').AsString:='Heyho';
           ABSTable1.Fields.FieldByName('Sallary').AsString:='20.200';
           ABSTable1.Post; // saves newly created record to the table
           ABSTable1.Next;
           end;
           Item:=false;
end;
end
else begin
           for i:=0 to 9 do begin
          
           ABSTable1.Locate('ID',i,[]);
           ABSTable1.Edit; // starts updating of current record

           ABSTable1.Fields.FieldByName('FirstName').AsString:='John';
           ABSTable1.Fields.FieldByName('LastName').AsString:='Heyho';
           ABSTable1.Fields.FieldByName('Sallary').AsString:='20.200';
        
           ABSTable1.Post; // saves newly created record to the table
end;

[mkinzler]

You shouldn't use TABSTable but TABSQuery
http://www.componentace.com/sql-query-delphi.htm

[nanix]

Exactly but how should this look in SQL with Query.I am not very familiar with SQL..

[mkinzler]

SQL-Code:

insert into <Tablename> (FirstName, LastName, Sallary) values ( 'John', 'HeyHo', 20100);

But you can also use paramters:

SQL-Code:

insert into <Tablename> (FirstName, LastName, Sallary) values ( :firstname, :lastname, :sallary );

Delphi-Quellcode:

ABSQuery1.ParamByName( 'firstname').Value := 'John';
...

[nanix]

Auu okay thank you.

Can i use update also with autoinc?

[mkinzler]

This is an insert and in this case don't set the specific field

[DeddyH]

And updating an existing record looks like this:

SQL-Code:

UPDATE <tablename> SET FirstName = 'John', LastName = 'HeyHo', Sallary = 20100 WHERE <conditions>

Parameters can be used here as well.

[nanix]

Thanks again what about if we say table has ID property for example

So that it updates based on EmployeeID Autoinc

EmployeeID Firstname
0 John
1 Lisa
2 Kimberly
3 Stephanie


Also could Autoinc start from 0? Like in the above example with Absolute Database.



Thanks

[mkinzler]

It's the same for SQL as without. Just leave the key field unset:

SQL-Code:

insert into <Tablename> (FirstName) values ( :firstname);

[DeddyH]

I am not familiar with Absolute Database, so I cannot say if an autoinc-key can start with 0 there, but you can use this key as condition in the above example:

SQL-Code:

UPDATE <tablename> SET FirstName = 'John', LastName = 'HeyHo', Sallary = 20100 WHERE EmployeeID = 1

[nanix]

You mean like this right,

Delphi-Quellcode:

ABSQuery1.SQL.Clear;
ABSQuery1.SQL.Add('UPDATE <tablename> SET FirstName = 'John', LastName = 'HeyHo', Sallary = 20100 WHERE EmployeeID = 1');
ABSQuery1.SQL.Open;

[mkinzler]

This updates Lisa with John.

[nanix]

I ment the string it has to be like this,

Delphi-Quellcode:

ABSQuery1.SQL.Add('UPDATE <tablename> SET FirstName = John, LastName = HeyHo, Sallary = 20100 WHERE EmployeeID = 1');

Otherwise not compile?

[DeddyH]

Use parameters.

[nanix]

Thanks i will.

You all probably asked why does he need this.

Well to tell you the truth i need it becouse i have to load DB items to listview.Becouse there isn't a DB listview that also supports groups.

[mkinzler]

It compiles even with wrong statements. At runtime an error is then reported

[nanix]

Is this good idea for creating&updating items from DB to non data aware listview

[nanix]

I ment for creating and updating DB efficiently.Anyways this is my full code.If someone can peak to it and look if something could be improved.Mainly the procedure CreateUpdateSensors

http://www.mediafire.com/?tzeiu44mmdn

[ToFaceTeKilla]

Please attach your code to your posting, so it won't be lost, if mediafire deletes it someday!

[nanix]

Here it is..

[nanix]

Hello can someone show me how can i save a BLOB via MYSQL and load..