Database Rebuild for Firebird 1.5

[MarkusD]

Database Rebuild doesn't work for Test Projects with Firebird 1.5. The script create_interbase.sql creates a procedure with

FOR SELECT eq_name FROM equipment ORDER BY eq_name INTO :R1

ParamCheck should be disabled in

procedure TZSupplementarySQLTestCase.ExecuteScripts
.
.
.
for I := 0 to High(FileNames) do
begin
FSQLProcessor.Script.Clear;
FSQLProcessor.ParamCheck := false;

IMHO The better solution would be to ignore ':' and also the command seperator between begin and end, but if i understood the code correct that's not possible at the moment.

[Stevie]

Hello,

because the SQLProcessor now has support for Parameters, the ':' cannot be ignored. To solve your problem please set CleanupStatements to True!
Otherwise there must not be any comment before SET TERM ^ ; because parsing this statement does not recognize it if there is one.

Regards
Stevie

[MarkusD]

Just take a look at the Interbase Script and you will see that the ':' is used there, but not for defining a script parameter. Because there are no (script) parameters used nor filled in the scripts generating the test DB's, i think ParamCheck could and should be disabled there.

CREATE PROCEDURE PROCEDURE2
RETURNS(R1 VARCHAR(30))
AS
BEGIN
FOR SELECT eq_name FROM equipment ORDER BY eq_name INTO :R1
DO
SUSPEND;
END
^

[Stevie]

Because there are no (script) parameters used nor filled in the scripts generating the test DB's, i think ParamCheck could and should be disabled there.

Yes, of course! Cause you mentioned this already above I have not repeated this.

[MarkusD]

Sorry I thought it was an answer to my complete post, not only to the last sentence.

Does it really make sense to create procedures with dynamic code? That's the only use of parameters there i think. And we saw already that the actual solution has some problems. I have written a new protocol for Sybase ASA DBLib. ( I'm testing it at the moment and it's nearly complete.) To change the delimiter there you have to use the command:

SET OPTION COMMAND_DELIMITER='^'

If i want to use the same script in the Sybase Utilities as well as in Zeos, I have to rewrite the SQLProcessor to support this command instead of Set Term. Shouldn't the SQLProcessor be database independent as most as it can get? IMHO c. delimiters inside blocks ( begin ... end) should be ignored, that's also the way the programming languages i know of, handle this type of problem.

And perhaps an option to switch parameter parsing inside blocks ( begin ... end) would be a solution for ':'.

[Stevie]

Does it really make sense to create procedures with dynamic code?

Erm, dynamic code??? :R1 is the result-param of the stored proc.

That's the only use of parameters there i think. And we saw already that the actual solution has some problems. I have written a new protocol for Sybase ASA DBLib. ( I'm testing it at the moment and it's nearly complete.) To change the delimiter there you have to use the command:

SET OPTION COMMAND_DELIMITER='^'

If i want to use the same script in the Sybase Utilities as well as in Zeos, I have to rewrite the SQLProcessor to support this command instead of Set Term. Shouldn't the SQLProcessor be database independent as most as it can get? IMHO c. delimiters inside blocks ( begin ... end) should be ignored, that's also the way the programming languages i know of, handle this type of problem.

And perhaps an option to switch parameter parsing inside blocks ( begin ... end) would be a solution for ':'.

If you have SQL-Script using Set Term you should set dtSetTerm. If you are implementing new Term-Types you should add it.

[MarkusD]

Erm, dynamic code??? :R1 is the result-param of the stored proc.

Exactly, if it was a script parameter the only sense for it would be to create the procedure with "dynamic" code. Means exchanging the parameter through a code piece before executing the command on the database. If i'm correct SQL only supports ( for Zeos SQL Parser usefull) parameters in DML not in DDL statements, so checking for params should treat them not as a parameter but as a part of the ( DDL) statement ( and skip the ':').
So parameters can be used even in scripts where such DDL statements are part of.

If you have SQL-Script using Set Term you should set dtSetTerm. If you are implementing new Term-Types you should add it.

I also know this, but would'nt it be "better" to add support for blocks ( begin .. end)? Then it would be possible to skip code delimiters inside those blocks and one wouldn't have to change the delimiter at least in database "languages" where this is possible. For expamle In Sybase ASA it is possible to use the same code delimiter inside and outside of blocks. To separate the commands you have to parse the block begin (begin, then) and end ( end, endif) statements.

The following correct part of a Sybase ASA script is just one statement and ends also with a ';' as well as the commands between 'then' and 'end if':

if CheckProcedure('CheckIndex')=1 then
create function
FCT.CheckIndex(in tbln varchar(128),in indn varchar(128))
returns integer
begin
declare res integer;
if exists(select 1 from sys.sysindex si join sys.systable st on st.table_id=si.table_id where table_name=tbln and index_name=indn) then
set res=0
else
set res=1
end if
;
return(res)
end;
grant execute on FCT.CheckIndex to FCT;
end if;

I hope i could make my point understandable. I only wanted to discuss the actual behaviour of parameter and command parsing.
Im also willing to make these changes, but i don't have to much knowledge of other DB's outside of Sybase ASA, so i wanted to share my opinion about these two "problems" with others in order to improve the value of the ZEOS Library. It was surely not meant as a support request.

[Stevie]

I also know this, but would'nt it be "better" to add support for blocks ( begin .. end)? Then it would be possible to skip code delimiters inside those blocks and one wouldn't have to change the delimiter at least in database "languages" where this is possible. For expamle In Sybase ASA it is possible to use the same code delimiter inside and outside of blocks. To separate the commands you have to parse the block begin (begin, then) and end ( end, endif) statements.

I know what you mean, but in Oracle for example you can write following script:

Code: Select all

begin
  select count(*) into :x from dual;
end;

I know, this may be not exactly correct but it is possible to return values from your script. If parameter parsing would be disabled within begin..end this would not work.
Do not know if this feature of oracle works with zeos, cause i do not have oracle available at the moment.

[MarkusD]

Code: Select all

begin
  select count(*) into :x from dual;
end;

Shouldn't this example be parsed as one statement? Sybase ASA calls this a compound statement and begin ... end is used to group statements together as one statement. But if that's correct Oracle syntax i understand the problems. Sybase ASA uses curly brackets for script (input) parameters.

Just for my own curiousness, do you know what purpose "begin" and "end" fulfill in this example?
Is it for transaction handling ( atomic statement)?

And how would you send this to the DB?
It does only make sense imho if it's sent as one statement, so block parsing for statement delimiter should be ok?
I know that this is how it's done in the Sybase ISQL Tools, and i've written such a parser a while ago for my own alternative for the sh.... Java Administration Tools that are delivered with Sybase ASA.

[Stevie]

I do not know exactly what purpose "begin" and "end" fulfill in this example.
It could be executed without them but it could also be (PL/SQL):

Code: Select all

declare
  y number;
begin
  for y in 1..10 loop
    select count(*) into :x from dual;
  end loop;
end;

There are a lot of amazing things you can do with scripts in oracle but I do not know already if this works with zeos.

[MarkusD]

Can you create a cursor on this statement?

Code: Select all

declare
  y number;
begin
  for y in 1..10 loop
    select count(*) into :x from dual;
  end loop;
end;

If not then it can't work in ZEOS. How would you access the different values of x otherwise?
If you can then your example is something like a select statement. That is possible in Sybase too, but there this has to be the content of a procedure and you have to call/select it to get the result(s).

But back to the command delimiter, do you think "block parsing" would be an improvement? IMHO It would be not too complicated to implement and sript writing would be easier and more compatible. As i already mentioned i would be willing to make the appropriate changes after i fully completed the Sybase ASA DBLib protocol.

[Stevie]

If not then it can't work in ZEOS. How would you access the different values of x otherwise?
If you can then your example is something like a select statement. That is possible in Sybase too, but there this has to be the content of a procedure and you have to call/select it to get the result(s).

No, I think this is something like this:

Code: Select all

procedure Foo(var x: Integer);
var
  y: Integer;
begin
  for y := 1 to 10 do
    x := Something;
end;

But back to the command delimiter, do you think "block parsing" would be an improvement? IMHO It would be not too complicated to implement and sript writing would be easier and more compatible. As i already mentioned i would be willing to make the appropriate changes after i fully completed the Sybase ASA DBLib protocol.

This would be great!