Re: [Lazarus] Make table with TDataset structure
On Mon, 22 Jun 2015, aradeonas wrote: Vary good. Please let me know when it ready. I committed the example, it's called 'createsql'. It's about 200 lines of code. It should work with all supported database types. Examples: home: ./createsql -h Usage: /home/michael/FPC/trunk/packages/fcl-db/examples/createsql [options] Where options is one or more of: -h --help this help message -c --connection-type=ctype Set connection type (required) -d --database=db database connection name (required) -s --sql=sql SQL to execute (optional) -t --table=tablename tablename to use for statement (required) -y --type=stypeStatement type (required) one of ddl, select, insert, update, delete) -k --keyfields=fields Comma-separated list of key fields (required for delete, update, optional for select,ddl) -u --user=username User name to connect to database -p --password=password Password of user to connect to database with Where ctype is one of : firebird mysql 4.0 mysql 4.1 mysql 5.0 odbc oracle postgresql sqlite3 home: ./createsql -c firebird -d localhost:/home/firebird/timetrack.fb -u NNN -p PPP -t PROJECT -y update -k PJ_ID UPDATE PROJECT SET PJ_ID = :PJ_ID, PJ_NAME = :PJ_NAME WHERE (PJ_ID = :OLDPJ_ID) home: ./createsql -c firebird -d localhost:/home/firebird/timetrack.fb -u NNN -p PPP -t PROJECT -y INSERT -k PJ_ID INSERT INTO PROJECT (PJ_ID, PJ_NAME) VALUES (:PJ_ID, :PJ_NAME) home: ./createsql -c firebird -d localhost:/home/firebird/timetrack.fb -u NNN -p PPP -t PROJECT -y select -k PJ_ID SELECT PJ_ID, PJ_NAME FROM PROJECT WHERE (PJ_ID = :PJ_ID) home: ./createsql -c firebird -d localhost:/home/firebird/timetrack.fb -u NNN -p PPP -t PROJECT -y delete -k PJ_ID DELETE FROM PROJECT WHERE (PJ_ID = :OLDPJ_ID) home: ./createsql -c firebird -d localhost:/home/firebird/timetrack.fb -u NNN -p PPP -t PROJECT -y ddl -k PJ_ID CREATE TABLE PROJECT ( PJ_ID VARCHAR(36) NOT NULL, PJ_NAME VARCHAR(30) NOT NULL, CONSTRAINT PROJECT_PK PRIMARY KEY (PJ_ID) ) The SQL code generator has some more options to control how the SQL is generated, but I didn't implement those. Hope this helps. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
Thank you very much Michael.Now I learned how to do this.hope this help others too.Maybe renaming it to CreateSQLFromTableStructure or something like this help to find easier in the future needs of others. Ara -- http://www.fastmail.com - Does exactly what it says on the tin -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
On Mo, 2015-06-22 at 04:11 -0700, aradeonas wrote:
Thank you Marc,very interesting point but now Im talking about INSERT
SQL COMMAND.
Sorry, I read too fast over your mail.
I want to make insert sql command for a table ,for example :
INSERT INTO table1(Filed1,Filed2) VALUES(:Filed1,:Filed2);
I know its possible because Lazarus data desktop do this but I count
find out how and I thought with my self maybe It's Maker or others who
work with its source can help me in this.
If your table is created and you have got a TSQLQuery or the like
attached and opened, you can iterate over it's FieldDefs property.
It should work similar to this pseudocode:
strinsertsql := 'INSERT INTO table name VALUES (';
for i=0 to Query.Fielddefs.Count do begin
strname := FieldDefs[i].Name;
strtype := map_type_from_laz_to_sqlite( FieldDefs[i].DataType;
strinsertsql := strinsertsql + ', ' + strname;
end;
... and so on.
--
Marc Santhoff m.santh...@web.de
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
Easy solution,Thank you very much Marc. But I was strugling to how use TFPDDSQLEngine as Michael said,it implements all we need but how use it for a current table for making insert,update,generate sql code is a question for me that maybe Michael can tell us. Regards, Ara -- http://www.fastmail.com - A fast, anti-spam email service. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
Vary good. Please let me know when it ready. Regards, Ara -- http://www.fastmail.com - Send your email first class -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
Thank you Marc,very interesting point but now Im talking about INSERT SQL COMMAND. I want to make insert sql command for a table ,for example : INSERT INTO table1(Filed1,Filed2) VALUES(:Filed1,:Filed2); I know its possible because Lazarus data desktop do this but I count find out how and I thought with my self maybe It's Maker or others who work with its source can help me in this. Regards, Ara -- http://www.fastmail.com - IMAP accessible web-mail -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
On Mon, 22 Jun 2015, aradeonas wrote: Easy solution,Thank you very much Marc. But I was strugling to how use TFPDDSQLEngine as Michael said,it implements all we need but how use it for a current table for making insert,update,generate sql code is a question for me that maybe Michael can tell us. I will make a small example and commit it in the examples/demos folder of fcl-db. Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
Yes I went with TSQLQuery but problem is how to get insert query automatically. So for example for Table1 without writing insert code I get insert SQL code. In lazarus database desktop tool,app do that but I can find out how to to do like that. Regards, Ara -- http://www.fastmail.com - Send your email first class -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
No,I explained it badly. I mean working on Lazarus Data Desktop source didnt help to do what I want otherwise its a very good app,if I didnt go wrong you write the code more than 7 years ago and its clean,codes like this show me how to write code professionally. I want to do what this app done in a code but I will lost in it,as I said I have a connection to a sqlite database and want to make create sql for one of the tables. Regards, Ara -- http://www.fastmail.com - Faster than the air-speed velocity of an unladen european swallow -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
On Sat, 20 Jun 2015, aradeonas wrote: Its been hours I'm struggling with this case that I want to make create sql from a sqlite table. I have db and table name and I want to make a create sql. work on Lazarus Data Desktop didnt help me so I have to ask. What exactly did not work ? I opened lazarus database desktop, created a data dictionary, imported (reverse-engineered) a database, and then generated create sql for one of the tables in the data dictionary. It took me less than a minute to do so. So what goes wrong in your case ? Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
Its been hours I'm struggling with this case that I want to make create sql from a sqlite table. I have db and table name and I want to make a create sql. work on Lazarus Data Desktop didnt help me so I have to ask. Regards, Ara -- http://www.fastmail.com - Access all of your messages and folders wherever you are -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
On Sa, 2015-06-20 at 06:36 -0700, aradeonas wrote: No,I explained it badly. I mean working on Lazarus Data Desktop source didnt help to do what I want otherwise its a very good app,if I didnt go wrong you write the code more than 7 years ago and its clean,codes like this show me how to write code professionally. I want to do what this app done in a code but I will lost in it,as I said I have a connection to a sqlite database and want to make create sql for one of the tables. (from my meomry, please check details yourself) I think TDataset is not the best choice for the task. You would have to iterate over all fields to store, create the metadata entries (field type, length, contraint, etc.) and in the end trigger table creation. It would be much easier to use TSQLQuery. Taht way you only have to set the SQL string for table creation to a correct value like 'CREATE TABLE somthing(ID serial, shortname varchar(20), name varchar(255), ...)' and then run it using the ExecSQL method (or similar, lok in the docs). Afterwards you can open it using the TDataset. This is much faster if you have your field definitions ready before starting. You could store the SQL or make it a constant. If you need to have a universal table creation function you can still iterate over the fields an have a mapping between lazarus/fpc types and the storage types for your database, e.g. string - varchar(), integer - int, and so on. More works, but it boils down to simple string concatenation. HTH somehow, Marc -- Marc Santhoff m.santh...@web.de -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
Thank you I found it,its name is fpdddiff.pp . I will work on it. Regards, Ara -- http://www.fastmail.com - Or how I learned to stop worrying and love email again -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
On Wed, 10 Jun 2015, aradeonas wrote: Thank you,I'm working on it but it seems there isn't any alter table option,am I right? There is a differ, in fpddiff.pp I don't think it generates SQL (yet) Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
[Lazarus] Make table with TDataset structure
Hi, I want to make MySQL table from a TDataset structure,is there any ready to use tool? An example case : We have many SQLite DBs and want to convert them to MySQL and other than convert tools we can make TDataset from SQLite and generate fields automatically and then make DDL MySQL from TDataset and then run it and in the end insert all the data. In summery I want to make table create code form Dataset structure. Regards, Ara -- http://www.fastmail.com - Same, same, but different... -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
On Wed, 10 Jun 2015, aradeonas wrote: Hi, I want to make MySQL table from a TDataset structure,is there any ready to use tool? An example case : We have many SQLite DBs and want to convert them to MySQL and other than convert tools we can make TDataset from SQLite and generate fields automatically and then make DDL MySQL from TDataset and then run it and in the end insert all the data. In summery I want to make table create code form Dataset structure. You can use the FPC data dictionary routines in fpdatadict: TDDTableDef has ImportFromDataset and then you can use TFPDDSQLEngine.CreateCreateSQL to create a CREATE TABLE SQL statement. Michael.-- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Make table with TDataset structure
Thank you,I'm working on it but it seems there isn't any alter table option,am I right? Regards, Ara -- http://www.fastmail.com - Does exactly what it says on the tin -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
