Use of TMyParser

[GEswin]

Hi, I see internally that mydac components have a TMyParser, I'm looking at documentation but can't find any references. I would like to use it to extract parts of a SQL Script, for two purposes, first extract different single SQL sentences from a script, second from each part extract parameters. How can this be done ? Or will this be integrated in TMyScript ?

Many thanks,

[swierzbicki]

Hello,

I second that. This will realy help us.

Some times ago, you told me that this will be implement in the next MyDAC release. Is it still in the To do list ?

Thank you

[Ikar]

Do you need this task to execute large scripts? Or for some another purpose?

[GEswin]

Well I want to write some SQL Scripts, parse them and put each part in a TMyQuery, fill in params and execute, sequentially.. For example:

Code: Select all

  drop table if exists temp_traumas;
  create temporary table temp_traumas select codigo_trauma,count(id_numero) as suma from pacients_fitxa
  group by codigo_trauma;
  update traumas a, temp_traumas b set a.enfermos = b.suma where a.codigo = b.codigo_trauma;
  drop table if exists temp_traumas;
  select * from traumas where codigo = :codigo;

I would like to parse each SQL sentence, execute it in a TMyQuery (this case would be 5 separate sentences: drop, create, update,drop & select). And parse if there's any parameter then fill it in with a list of params i have.

I hope this explains it a little bit more.

Regards

[Ikar]

Try to use MyScript.DataSet to solve this task.

[GEswin]

Hi, the DataSet propertie of TMyScript is allright to obtain the result and i'm already using it in some projects, but this is not what i was looking for.

>> And parse if there's any parameter then fill it in with a list of params i have.

This is the important thing about it, parse and fill in parameters, that's why i would like to separate it and run it in a TMyQuery for using parameters.

[swierzbicki]

Dear Ikar,

I guess you missunderstood Geswin's request.
He just want to parse through existing SQL statements :

Let's use his example :

drop table if exists temp_traumas;
create temporary table temp_traumas select codigo_trauma,count(id_numero) as suma from pacients_fitxa
group by codigo_trauma;
update traumas a, temp_traumas b set a.enfermos = b.suma where a.codigo = b.codigo_trauma;
drop table if exists temp_traumas;
select * from traumas where codigo = :codigo;

So, let's assume that your Dev. Team added this method / properties to the TMyScript component :

Code: Select all

//We add the SQL Scripts composed of several sql statements
MyScript.SQL.clear;
MyScript.SQL.Add( 'drop table if exists temp_traumas;');
MyScript.SQL.Add( 'create temporary table temp_traumas select MyScript.SQL.Add( 'codigo_trauma,count(id_numero) as suma from pacients_fitxa
  group by codigo_trauma;')
MyScript.SQL.Add( ' update traumas a, temp_traumas b set a.enfermos = b.suma where a.codigo = b.codigo_trauma;');
MyScript.SQL.Add( 'drop table if exists temp_traumas;');
MyScript.SQL.Add( 'select * from traumas where codigo = :codigo;');

//Now, MyDAC Parse engine will check for available statements;
MyScript.Parse;

//Count will Return 5
MyScript.Statements.Count; 

//This will display the 1st Sql Statment : drop table if exists temp_traumas
MyScript.STatements[0].SQL;

//This will display the param of the 5st statement : codigo
MyScript.STatements[4].Param[0].Astring:='myvalue';

//This will execute the 4th Statement
MyScript.Statement[3].Execute;

//This will execute all statement;
MyScript.execute;

Hope you see clearer now !

Thanks

[Ikar]

That you suggested assumes serious ideological alterations in TMyScript. Is it possible to know why do you need it in the end.

For example, using parameters is quite clear task. But why do you need to know a number of statements and also can execute them in random order?

[swierzbicki]

Hi Ikar,

That you suggested assumes serious ideological alterations in TMyScript

I just see this as an extended feature of the TMyScript component.
I'm aware that will involve some hours of code ... but it really worth it !

Is it possible to know why do you need it in the end.

Well, I used to works with Zeoslib comps. There was an Zprocessor component which behave similary like thaht. I have migrated all my project know and fight hard to resolve this issue.

why do you need to know a number of statements

Because this open (us) new functionnality, for example :
- the possibility to see the script progress (executing Script 1 of 3),
- the possibility to see Which Statement wasn't succefully achieved (and displaying it if needed)
- the possibility to add parameters for each statements (really usefull).

and also can execute them in random order?

It's not to execute them in random order !. This is non logic. I see it when a SQL statement failed. We can add something different (as the order must be unchanged) and add too properties :
- ExecuteFromStatement (integer)
- ExecuteToStatement (integer)

[GEswin]

The parameter is for me a must (In some projects i want to ease the end user generation of listings and reports, or for myself for a lot of listings & reports just make a general engine and just write sql's for this).

The SQl Sentence count/execute etc is also very usefull for debugging and for some cases also for running together with some scripting engine (Ask user for some conditiosn and run only part of script).

Added:

If you don't want to implement this in near future, just an explanation on how to perform this with mydac's parser would be great.

[Ikar]

At the nearest MyDAC release we don't include these improvements. We are planning to improve TMyScript but for later time - now we are busy with a lot of urgent work by another directions.

[Ikar]

> If you don't want to implement this in near future, just an explanation on how
> to perform this with mydac's parser would be great.

Documenting a parser may take comparable time. If to write its working in two words then it consecutively parse given script on lexemes, determine type of lexeme (string, keyword, number etc.). The most difficult part is processing comments and strings.

[GEswin]

Hi Ikar, i'm not speaking about documenting it, but just an example to process the two tasks. Anyway i will have a look at sourcecode to see if i can get how it works. One important question, this week i'm going to upgrade to v4 (buy it and install on a test machine until i'm sure i can switch it to production), i would like to know if it will be compatible the tmyparser between these two versions, or better wait and work with v4 of mydac ?

Many thanks

[Ikar]

TMyParser in these both versions is fully compatible.

[swierzbicki]

now we are busy with a lot of urgent work by another directions

Good news.... can you share a little bit more with us ?