Stored Procedure Parameter Problem

sashki · Post by **sashki** » Mon 23 Jan 2012 08:45

The previous releases of the UniDAC components, added the stored procedure parameters when linking to the stored procedure by clicking on the "Generate" button, as follow:

i.e.
pStaffID
pContactNo
pAddress

Since version 4.1.3, which is my current version, when adding parameters to the stored procedure component by linking to the stored procedure and clicking on the "Generate" button, it now adds parameters as follow:

i.e.
`pStaffID`
`pContactNo`
`pAddress`

Note the difference. Originally parameters were added without the "`" character. Since version 4.1.3, the parameters are now pre-and post fixed with the "`" character.

This causes a big problem when you already have code written that references the parameters without this "`" character, and you make a change to the stored procedure's parameters and need to re-add them to the UniDAC stored procedure component, as the code needs to be changed to now reference the parameters as follow:

Initial method to reference parameters in stored procedure component:

Code: Select all

UniStoredProc1.ParamByName('pStaffID').AsInteger := '1234';
UniStoredProc1.ParamByName('pContactNo').AsString := '555-5555';
UniStoredProc1.ParamByName('pAddress').AsString := '123 West Road';

Code now needs to be changed to reference parameters as follow:

Code: Select all

UniStoredProc1.ParamByName('`pStaffID`').AsInteger := '1234';
UniStoredProc1.ParamByName('`pContactNo`').AsString := '555-5555';
UniStoredProc1.ParamByName('`pAddress`').AsString := '123 West Road';

Note in the second reference the new characters that need to be added in order for the code to work. If you do not add the "`" characters, you will encounter an error that says "parameter not found".

This is a major headache for an existing code base.

Please can you look at this and get back to me regarding this.

Thanks.

AndreyZ · Post by **AndreyZ** » Mon 23 Jan 2012 16:52

Hello,

I cannot reproduce the problem. UniDAC quotes InputOutput and Output stored procedure's parameters to create the SQL code that will be sent to the server if the TUniStoredProc.Options.QuoteNames property is set to True. But parameter names do not contain quotation, and you still have to use parameter names without quotation in your code.
If UniDAC creates parameters with names that contain quotation (you can check it from the Parameters sheet of TUniStoredProc Editor), please specify the following:
- the exact code of your stored procedure;
- the exact version of your IDE;
- the exact version of UniDAC. You can learn it from the About sheet of TUniConnection Editor.

sashki · Post by **sashki** » Mon 23 Jan 2012 22:54

I checked the property you mentioned, and it is indeed "false".

The UniDAC parameters are indeed generating names with quotations under the parameter sheet.

I'm running the following versions of software:

- Delphi XE2 Update 2 (Version 16.0.4316.44803).
- UniDAC Version 4.1.3

I checked against other stored procedures, and it is not happening with them, but with this specific stored procedure, this problem occurs. I don't see anything out of the ordinary with this routine.

This is the stored procedure that is generating parameter name values with quotes as outlined in my initial post. Can you see what's causing the parameter name values to be added with quotes on the parameter sheet?:

Code: Select all

CREATE DEFINER = 'root'@'localhost' PROCEDURE `GetStaffInfoFingerprintMatch`(
        IN `pStaffNo` VARCHAR(38),
        OUT `pStaffID` INTEGER(11),
        OUT `pFirstName` VARCHAR(50),
        OUT `pSurname` VARCHAR(50),
        OUT `pIDNo` VARCHAR(13),
        OUT `pUserLevelNo` INTEGER(11),
        OUT `pStaffFound` BOOLEAN
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN	
    
  SELECT StaffID, StaffFirstName, StaffSurname, StaffIDNo, StaffUserLevelNo
  INTO pStaffID, pFirstName, pSurname, pIDNo, pUserLevelNo
  FROM Staff
  WHERE StaffNo = pStaffNo;
  
  SET pStaffFound = pFirstName  '';
  
END;

AndreyZ · Post by **AndreyZ** » Tue 24 Jan 2012 10:52

Thank you for the information. We will fix this problem in the next UniDAC build.

sashki · Post by **sashki** » Fri 27 Jan 2012 00:47

So I assume you now encounter the same problem??

AndreyZ · Post by **AndreyZ** » Fri 27 Jan 2012 09:30

Yes, we reproduced this problem. We will fix it in the next UniDAC build.