[bug_fixed] TZStoredProc problem

[ochnap2]

Hi, I think I found two little errors in ZStoredProcedure.pas, in function TZStoredProc.SetStatementParams:

- The parameter initialization when the param value is null has a wrong index. It says:

Code: Select all

    [...]
    if Param.IsNull then 
      Statement.SetNull(I, ConvertDatasetToDbcType(Param.DataType)) 
    [...]

but it should be:

Code: Select all

    [...]
    if Param.IsNull then 
      Statement.SetNull(I+1, ConvertDatasetToDbcType(Param.DataType)) 
    [...]

- The ftFixedChar field type is not handled. Due to this wrong SQL statements are generated for certain s-p calls (missing parameters). This type should be added to the ftString case. The lines:

Code: Select all

        [...]
        ftString: 
          Statement.SetString(I+1, Param.AsString); 
        [...]

should be rewriten as:

Code: Select all

        [...]
        ftString, ftFixedChar: 
          Statement.SetString(I+1, Param.AsString); 
        [...]

Added to this, I think that the case statement should have an else case firing an exception for unhandled field types. That would make obvious where the problem lies, instead of delaying the problem to the execution of the s-p.

I'm using the trunk version, but the file is identical in the testing branch.

Thanks,
Och

[ochnap2]

Hi, there was no comment regarding my previous post. I'm new to this community: Is enough to post the bug here? Should I also post a bug in the bugzilla?

Moreover: I found another problem with the stored procs parameters, this time in TZStoredProc.RetrieveParamValues. The code in SVN generates an index-out-of-bounds exception when testing for null values.

This is the code in SVN:

Code: Select all

    [...]
    if FCallableStatement.IsNull(I) then
      Param.Clear
    [...]

This code works for me:

Code: Select all

    [...]
    if FCallableStatement.IsNull(I + 1) then

      Param.Clear

    [...]

Thanks,
Och

[fduenas]

Hi it should be enought to post this here, but maybe beacuse all these party days the forum could be a little alone . just give a time to evalute your posting.

Thanks

[mdaems]

Hi Och,

Party Time indeed.

I've found your first two problems in Testing Branch. I did fix the obvious part. Can you make me a good (and tested) else clause? I'm not using stored procs yet.

I've not found the problem in your second post however. My fault? May be. Just check the attached version.

Mark

[ochnap2]

Hi, you are right, I've been following the code a little and I believe there's no problem in RetrieveParamValues... but I still get the null-pointer-exception.

The exception is fired in TZAbstractCallableStatement.GetOutParam when excuting line 1526: Result := OutParamValues[ParameterIndex - 1];

The exceptions is fired from inside a procedure named _IntfCopy from the System unit. The exact message is:

---------------------------

Debugger Exception Notification

---------------------------

Project OpVideo.exe raised exception class EAccessViolation with message 'Access violation at address 004070FA in module 'x.exe'. Read of address 0000002C'. Process stopped. Use Step or Run to continue.

---------------------------

OK Help

---------------------------


Many calls to ExecProc work ok, but there's one that always fires the exception (a s-p with two input and two output parameters, I don't know if this is relevant yet).

I believe is a compiler or RTL error (I'm using Delphi 7). What do you think?

[mdaems]

I really have no clue... and use no databases with stored procs supported by Zeoslib. (Somebody really has to write it for mysql.) Can you do a display of the value in OutParamValues[ParameterIndex - 1] in an other way (eg using debugger watches) without crashing? Is the right number of parameters initialized in TZAbstractCallableStatement.SetOutParamCount? Just some wild guesses...

Are the fixes OK? If so I can keep them like they are.

Can you provide me the 'else' clause you asked for? You know a place to test it... (remove the ftFixedChar change...)

Mark

[ochnap2]

Hi. Yes the other fixes are OK (The null case in SetStatementParams and the ftFixedChar handing).

A poor man implementation of the else clause is included in the attached ZStoredProcedure.pas replacement: It simply fires an EZDatabaseError with the SInternalError string whenever a param data type is not explicitly handled in TZStoredProc.SetStatementParams and ZStoredProc.RetrieveParamValues.

A better implementation should generate a more meaningfull error message. For example: "unhandled parameter type X" would be a lot more helpfull than "internal error".

RetrieveParamValues already had an else clause that managed everything as strings but I replaced it by the exception statement, because it's not safe to assume that every unknown type will be convertible to a string.

Regarding the exception in RetrieveParamValues: Yes: the number of parameters is OK, and yes: the content of OutParamValues[ParameterIndex - 1] is viewable with the debugger. That's why I say is a Delphi problem. I'll try other options and tell you what happens...

Thanks,
Och

[mdaems]

Commited in Testing Branch revision 194.

I have taken the freedom to add a new more clear message .
Can you please test the new behaviour and confirm? So I can close this topic. (Check, doublecheck, boring... I know)

Mark

BTW if you don't really care finding a small incompatibility you can quite safely change to testing branch. It doesn't change a lot and it would help me much to keep stupidities out of the coding.

[ochnap2]

Hi, Mark. I downloaded the SVN version today and rerun my tests. I saw no problem, so I think it's ok to close the topic.

On the other hand, I got the software tested with delphi 10 to check for the problem in RetrieveParamValues. The exceptions I had with D7 are gone in D10, so I think this confirms that is a D7 problem and can also be dismissed.

Thanks,
Och