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

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.

Thank you,I'm working on it but it seems there isn't any alter table option,am I right?

Regards,
Ara

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.

Thank you I found it,its name is fpdddiff.pp .
I will work on it.

Regards,
Ara

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

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.

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

(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

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

You normally can do that using SQL, too. But the syntax varies, for SQLite this should be:

"CREATE TABLE <new table name> AS SELECT * from <template table name> WHERE 0=1"

The where clause may be rejected, choose a clause that does not return any columns or, if that doesn't create anything, one that returns only one or a few rows and delete them in the new table after creation ("DELETE * FROM <new table name>").

See:

http://www.sqlite.org/lang_createtable.html

Paragraph "CREATE TABLE ... AS SELECT Statements"

Cheers,
Marc

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

aradeonas wrote

Thank you Marc,very interesting point but now Im talking about INSERT SQL COMMAND.

Sorry, I read too fast over your mail.

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.

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

I will make a small example and commit it in the examples/demos folder of fcl-db.

Michael.

Vary good.
Please let me know when it ready.

Regards,
Ara

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=stype Statement 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.

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