[bug_fixed] Fix for Bug in ZInterbase6Sequence.GetNextValue

[CharlesMcAllister]

GetNextValue does a case-sensitive comparison to determine the generator value...

[syntax="delphi"]
function TZInterbase6Sequence.GetNextValue: Int64;
begin
...
ResultSet := Statement.ExecuteQuery(
'SELECT GEN_ID(' + Name + ', ' + IntToStr(BlockSize) + ') ' +
'FROM rdb$generators ' +
'WHERE rdb$generators.rdb$generator_name = ''' + Name + '''');
[/syntax]
My proposed solution is as follows...
[syntax="delphi"]
function TZInterbase6Sequence.GetNextValue: Int64;
begin
...
ResultSet := Statement.ExecuteQuery(
'SELECT GEN_ID(' + Name + ', ' + IntToStr(BlockSize) + ') ' +
'FROM rdb$generators ' +
'WHERE UPPER(rdb$generators.rdb$generator_name) = ''' + UpperCase(Name) + '''');
[/syntax]

Please consider merging this into ZeosLib!
I'd do it myself but I dont think I have write access to the SVN archive.
Thanks!

[mdaems]

Hi,

I don't know interbase but maybe you can answer this question :
is a generator name case insensitive in interbase? Or easier put : can you create 2 different generators just changing the case? Can you test this before you answer?
If the answer is "No, it's not possible to create 'testgen' and 'Testgen' together" your change is in.
If it is possible to create them both, you change is not a good thing. (a dactylo course is the only solution then )

Mark

BTW: you don't have commit rights to SVN,but you can make SVN patches and attach them if your change is bigger than this one. That's easier to integrate. Also : please use '[syntax="delphi"]' or similar tags when you include coding.

[CharlesMcAllister]

Mark you're right, that was my mistake.
The solution is to pass a upper-cased name to the TZSequence object.

When creating my objects i wasn't paying attention to case, and i wasn't double-quoting object names either. In other words i was relying on the db to be case insensitive. so it surprised me that when creating a TZSequence object, that i had to supply a name which was uppercased! because it turns out that the generator name is stored in rdb$generators in uppercase.

If I had been double-quoting my object names, then it would be stored in rdb$generators as mixed case. Which could be a bug in TZSequence since the reference to Name in GEN_ID below is not double-quoted.

[syntax="delphi"]
function TZInterbase6Sequence.GetNextValue: Int64;
begin
...
ResultSet := Statement.ExecuteQuery(
'SELECT GEN_ID(' + Name + ', ' + IntToStr(BlockSize) + ') ' +
...
end;
[/syntax]

..but since i've given up on double-quoting my object names, this doesn't concern me for now.
Thanks for your attention!
So far really liking ZeosLib!

[mdaems]

So we should change this by putting quotes around the generator name to allow for case-sensitive sequences?

[syntax="delphi"]
function TZInterbase6Sequence.GetNextValue: Int64;
begin
...
ResultSet := Statement.ExecuteQuery('SELECT GEN_ID("' + Name + '", ' + IntToStr(BlockSize) + ') ' +
...
end;
[/syntax]

Can you test that for me? Create a case-sensitive sequence and try to use it. If it fails add this change and look if it has been fixed by thos 2 double quotes.

Mark

[CharlesMcAllister]

Here is my simple test case that shows failure when generators are created as a double quoted mixed case name...

[syntax="delphi"]
procedure TForm1.Button3Click(Sender: TObject);
var
ASequence: IZSequence;
AStatement: IZStatement;
begin
ZeosLibDM.MainConnection.Connect;

AStatement := ZeosLibDM.MainConnection.DbcConnection.CreateStatement;
AStatement.ExecuteUpdate('CREATE GENERATOR UPPERCASEGENERATOR');

ASequence := ZeosLibDM.MainConnection.DbcConnection.CreateSequence('UPPERCASEGENERATOR', 1);
ASequence.GetNextValue;
Assert(ASequence.GetNextValue > 0);

AStatement := ZeosLibDM.MainConnection.DbcConnection.CreateStatement;
AStatement.ExecuteUpdate('CREATE GENERATOR "MixedCaseGenerator"');

ASequence := ZeosLibDM.MainConnection.DbcConnection.CreateSequence('MixedCaseGenerator', 1);
ASequence.GetNextValue; // error happens here when sequence is queried in TZInterbase6Sequence.GetNextValue
{

---------------------------
Debugger Exception Notification
---------------------------
Project Project1.exe raised exception class EZSQLException with message
'SQL Error: invalid request BLR at offset 108 generator MIXEDCASEGENERATOR is not defined.
Error Code: -104. Invalid token The SQL: SELECT GEN_ID(MixedCaseGenerator, 1) FROM rdb$generators
WHERE rdb$generators.rdb$generator_name = 'MixedCaseGenerator'; '. Process stopped.
Use Step or Run to continue.
---------------------------
OK Help
---------------------------

}


Assert(ASequence.GetNextValue > 0);
end;
[/syntax="delphi"]

I've attached a patch for ZDbcInterbase6.pas
Hope you don't mind that I used the SysUtils.Format method to form the SQL code.

Thanks again!

[mdaems]

Committed your patch to SVN Testing branch (Rev 161).

Please check if everything is OK now and confirm we can close this bug report.

Thanks a lot...

Mark

[CharlesMcAllister]

works now thanks.