Oracle parameter in function

[aehimself]

I have a really strange issue here and I can not seem to reproduce it in a oneliner. I have the following SQL query:

SELECT
ROUND(MYTABLE.ID - :pIntMinID),
MYTABLE.STRINGFIELD1,
MYTABLE.STRINGFIELD2
FROM MYTABLE
WHERE
MYTABLE.NUMERICFIELD1 = 1 AND
MYTABLE.ID > :pIntMinID AND
MYTABLE.ID < :pIntMaxID
ORDER BY MYTABLE.ID ASC

Opening this query throws an error:
Fetch FETCH ROW ORA-01008: not all variables bound (#1008)

Now, remove one STRINGFIELD from the query (OR the ROUND function...?!). This time there are no errors, but the query has 0 results; however in the database there are records matching this criteria!

I remember the exact same query worked before the new TZParams but I don't feel like rolling back half of my code so I can test it with the old one...

Both :pIntMinID and :pIntMaxID are set, while debugging TZQuery.Params.Count returns 2, both are .Bound and are not .IsNull:

Code: Select all

[2021.01.26 22:03:46.299] Bind prepared Statement 25 : 0,1000
[2021.01.26 22:03:46.339] Fetch FETCH ROW ORA-01008: not all variables bound  (#1008)

[2021.01.26 22:04:09.463] Bind prepared Statement 27 : 0,1000
[2021.01.26 22:04:09.504] Fetch Statement 27, affected 0 row(s) in 39 ms

There is something sketchy here.
ID is NUMBER, STRINGFIELD1 is VARCHAR2(200), STRINGFIELD2 is VARCHAR2(200), NUMERICFIELD1 is NUMBER

Based on the fact that removing one string field from the query yields a different result I'd really appreciate some hints in where to look to find the issue. I'll try to do my best to recreate a minimalistic test case in the mean time.

P.s.:

SELECT ROUND(100 - :pIntNumber) FROM DUAL

works just fine.

[aehimself]

Update: Splitting :pIntMinID to two separate parameters and assigning the same value works and returns the desired results:

SELECT
(ID - :pMinID1)
[...]
WHERE
[...]
ID > :pMinID2

[aehimself]

Smaller repro:

SELECT
ID - :pNumber,
STRINGFIELD
FROM MYTABLE
WHERE
ID > :pNumber AND
ID < :pNumber2

If you remove StringField from the select, query just shows no results.

[aehimself]

Maybe the issue is that the statement needs 3 parameters, but the query supplies only two...?

ZAbstractRODataSet.pas : 2358

Code: Select all

      for I := Low(ParamNames) to High(ParamNames) do begin
        if Assigned(Dataset)
        then Field := Dataset.FindField(ParamNames[I])
        else Field := nil;
        if Assigned(Field) then begin
          TempParam.AssignField(Field);
          Param := TempParam;
        end else begin
          Param := Params.FindParam(ParamNames[I]);
          if not Assigned(Param) or (Param.ParamType in [ptOutput, ptResult]) then
            Continue;
        end;
        SetStatementParam(I{$IFNDEF GENERIC_INDEX}+1{$ENDIF}, Statement, Param);
      end;

There are two param names, so SetStatementParam will be called once with the first parameter value and once more with the second parameter value, while those two should be the same (by name)...?

[aehimself]

Yes, that is EXACTLY the issue. If I change the the whole previous cycle with

Code: Select all

        Case I Of
         0: Begin
            SetStatementParam(0{$IFNDEF GENERIC_INDEX}+1{$ENDIF}, Statement, Param);
            SetStatementParam(1{$IFNDEF GENERIC_INDEX}+1{$ENDIF}, Statement, Param);
            End;
         1: SetStatementParam(2{$IFNDEF GENERIC_INDEX}+1{$ENDIF}, Statement, Param);
        End;

...everything works as it should. Now, the issue is, ParamNames only contains each parameter name once. We should put all parameter names in order, so we can just insert

Code: Select all

For J := I + 1 To High(ParamNames) Do
  If ParamNames[j] = ParamNames[I] Then
    SetStatementParam(J{$IFNDEF GENERIC_INDEX}+1{$ENDIF}, Statement, Param);

...after the original SetStatementParam call. Where this array is set? Since Delphi is not good with debugging interfaces, I simply don't know where to put my breakpoint :(

[aehimself]

The parameter list is being created in TZOracleTokenizer.NormalizeParamtoken, but commenting out to skip the duplicates will cause the ZQuery to have 3 parameters: pNumber, pNumber and pNumber2. Setting these to 1-1-2000000 returns rows without errors.

We can filter duplicate parameter names in TZAbstractRODataset.UpdateSQLStrings but in this case we should re-write ZTokenizer.NormalizeParamToken and TZPostgreSQLTokenizer.NormalizeParamToken too to allow and return all (including duplicated) parameter names.
With that in place TZAbstractRODataset.SetStatementParams could have the inner loop, setting the same value for all parameters with the same name.

Any objections? Better solutions?

[aehimself]

Well, I created a pull request on GitHub which seems to solve this for me.
Check if it's suitable to be accepted.

[marsupilami]

I applied the patch and had to revert it because it created test fails for orher drivers like dblib and firebird. This is an example for one freetds-build:

freetds-mssql2014-csas.component.TZTestSQLStringsCase.TestStatements
freetds-mssql2014-csas.component.TZTestSQLStringsCase.TestParams
freetds-mssql2014-csas.component.TZTestSQLStringsCase.TestParamChar

[aehimself]

Jan,

Is it possible to install MadExcept so to have stack traces on the failures?

basic.core.TZTestFramework.TestCheckEqual

Failing for the past 52 builds (Since #1 )
Took 0 ms.
Error Message
Exception: [EAccessViolation] Access violation at address 000000000042C388 in module 'ZTestAll.exe'. Read of address 0000000000000000
at: $000000000042C388

does not really tell too much on where should I look.
Is there a guide somewhere on how exactly I can run the tests myself from the IDE? Especially with access to MySQL and MSSQL only here at home.

[marsupilami]

Is it possible to install MadExcept so to have stack traces on the failures?

Unfortunately it is not that easy. We would have to include the MadExcept units in the dpr file of ZTestAll. But then it would not compile if MadExcept isn't installed. We could use ifdefs to get around that. But unfortunately it isn't easy to add defines when building without erasing other defines from the build. Also Delphi reacts bad on ifdefs in dpr files. Maybe we could have a patch that could be applied before doing the actual build...

[EgonHugeist]

@aehimself,

sorry for the delay. I'm bussy with Marks timestamps and other work. Just some tests:

Code: Select all

 
procedure TZTestDbcOracleBugReport.TestDuplicateParamNames;
var Statement: IZPreparedStatement;
  ResultSet: IZResultSet;
begin
  Check(Connection <> nil);
  try
    Statement := Connection.PrepareStatement('SELECT :SID, :SID, :SID FROM DUAL Where 1 = :SID or 2 = :SID');
    Statement.SetInt(FirstDbcIndex, 1);
    ResultSet := Statement.ExecuteQueryPrepared;
    Check(ResultSet.Next);
    CheckEquals(3, ResultSet.GetColumnCount);
    CheckFalse(ResultSet.IsNull(FirstDbcIndex));
    CheckFalse(ResultSet.IsNull(FirstDbcIndex+1));
    CheckFalse(ResultSet.IsNull(FirstDbcIndex+2));
  finally

  end;

Runs as expected on my 11g Client+Server.
This one:

Code: Select all

procedure TZTestDbcOracleBugReport.TestDuplicateParamNames;
var Statement: IZPreparedStatement;
  ResultSet: IZResultSet;
begin
  Check(Connection <> nil);
  try
    Statement := Connection.PrepareStatement('SELECT :SID, :SID, :SID+:AID FROM DUAL Where 1 = :SID or 2 = :SID');
    Statement.SetInt(FirstDbcIndex, 1);
    Statement.SetInt(FirstDbcIndex+1, 1);
    Statement.SetInt(FirstDbcIndex+2, 1);
    Statement.SetInt(FirstDbcIndex+3, 1);
    Statement.SetInt(FirstDbcIndex+4, 1);
    Statement.SetInt(FirstDbcIndex+5, 1);
    ResultSet := Statement.ExecuteQueryPrepared;
    Check(ResultSet.Next);
    CheckEquals(3, ResultSet.GetColumnCount);
    CheckFalse(ResultSet.IsNull(FirstDbcIndex));
    CheckFalse(ResultSet.IsNull(FirstDbcIndex+1));
    CheckFalse(ResultSet.IsNull(FirstDbcIndex+2));
  finally

  end;

passes too.But if i bind two params only the last IsNull test fails. I can't confirm the Exception you get. Propably again an issue with the newer client lib you are usung, i guess.
Your patch did change the behavior for Postgres. The more the non normalized params did disturb postgest because the server sends and expects just one value/ParamNumber (such as ($1, $2). They wade that to have better type determination for the prepared statements including minior data to send over the wire. So it's worth it to keep the Noramized logic i implemented.

So i'll remove the duplicate check for Oracle again. Be patient. It would be nice if you use the testsuite ae.

[aehimself]

Michael,

For a while I have 12.1 on my PC as well. Issue is present with 12.1 and 19.8 consistently. My fix was not correct after all. I did my homework now and found this:

If you are using the OCIBindByPos() call, however, you have increased flexibility in terms of binding duplicate bind-parameters separately, if you need it. You have the option of binding any of the duplicate occurrences of a bind parameter separately. Any unbound duplicate occurrences of a parameter inherit the value from the first occurrence of the bind parameter with the same name. The first occurrence must be explicitly bound.

So 1, the issue is not general but Oracle related and 2, according to the Oracle manual Zeos's logic is correct to bind the first occurrence of each parameter only.

So I started to think. Is it possible that this is a parsing issue...? Zeos binds the second :pMinID as "first"?
I updated my query, this is reproducing the issue every time. Leave the field name, table names before fields and even the bracket:

SELECT
(TABLENAME.ID - :pMinID) "SUBSTRACTEDID",
TABLENAME.STRINGFIELD
FROM TABLENAME
WHERE
TABLENAME.ID > :pMinID AND
TABLENAME.ID < :pMaxID

[marsupilami]

Is it possible to install MadExcept so to have stack traces on the failures?

basic.core.TZTestFramework.TestCheckEqual

Failing for the past 52 builds (Since #1 )
Took 0 ms.
Error Message
Exception: [EAccessViolation] Access violation at address 000000000042C388 in module 'ZTestAll.exe'. Read of address 0000000000000000
at: $000000000042C388

does not really tell too much on where should I look.

Using MadExcept is not easy. Regarding databases at home: Would it help if there was a computer that you could RDP into to do some things?

Is there a guide somewhere on how exactly I can run the tests myself from the IDE? Especially with access to MySQL and MSSQL only here at home.

Running the test suites can be done this way:

• Create a test.properties file. Usually you do that in the database directory of Zeos. If you create it somewhere else, you will ahve to copy the sql files from the database directory there. Otherwise you will have to specify the script file location using the "common.scriptpath" setting in the [common] section. The test.properties is quite self explanatory. There is a sample file called "test_template.properties". Also I could post some sample files or sample file sections here.
• Open the ZTestAll project for your version of Delphi and compile it. By default it uses the preinstalled Zeos packages in Delphi. I usually add the "..\..\src\core" directory and the others to the projects file search path.
• For a full run of the test suite, run it outside of the IDE because some tests generate Exceptions on purpose. If you want to debug a specific test, use the "-s" Paramater to select the test. On Delphi it is enough to just give the test name, like "TestDuplicateParamNames". On FPC you might have to give the full test name. I am not sure there. Also you can select only apart of the test suite like "-s dbc" or "-s dbc.TZTestDbcOracleBugReport".

For a better description of the options look at https://sourceforge.net/p/zeoslib/wiki/Running the TestSuites/.

Best regards,

Jan

[aehimself]

Regarding databases at home: Would it help if there was a computer that you could RDP into to do some things?

RDP - unfortunately no. Plus, I wouldn't accept even a guest account on someone else's server as I wouldn't give any out... sorry, security is my paranoia :)
I'd need access to databases directly to be able to run tests against different protocols.

Running the test suites can be done this way:

Well, I got the test suite to work, I successfully scored ~50% on my home MySQL instance. Good news is - it has stack traces. Bad news is: I have no goddamn clue how to interpret the results yet. I got used to the natural way of debugging... you get an exception and you start checking why the code ended up in that state. Tests are a little bit different though...

Being a Microsoft partner at work I can create an Azure machine for free of charge (they give ~$150 to "play"). I'm thinking on using this to install some RDBMS on it and protect it with a VPN. If I can get it to work I am more than happy to share that with you. That way we could have a couple more instances to test.
What do you guys think, would it benefit the development of Zeos?

[aehimself]

@ Michael,
I can confirm that your fix works, the test and the original query runs fine now. With 2 parameters I can see 3 binds - which was my original idea.
There are two thing what bugs me:
- Checking the code change I still have no damn clue what you did. Well, you know the code and I don't... anyway, it bugs me.
- The small note I found on OCIBindByPos. It should work as Zeos did it the first place.