How get value from out parameter?

[sklim82]

Dear Support Team,

I have the following stored procedure and would like to create parameters on run time.

Code: Select all

TMyStoredProc *sp = new TMyStoredProc(NULL);
        sp->ParamCheck = true;
        sp->StoredProcName = "SP_UpdatePassword";
        sp->Params->Clear();
        TParam *prmUsername = sp->Params->CreateParam(ftString, "Username", ptInput);
        TParam *prmNewPassword = sp->Params->CreateParam(ftString, "NewPassword", ptInput);
        prmUsername->AsString = szUsername;
        prmNewPassword->AsString = szNewPassword;

        if(this->dbConn->ExecSP(sp)){
                iResult = sp->Params->ParamByName("Result")->AsInteger;
        }
        sp->Close();

        SAFE_DEL(sp);
        return iResult;

My questions are:
1. Should I set sp->ParamCheck = false?
2. I need to get Result which is one of the OUT parameters in stored procedure SP_UpdatePassword, is my existing way to get Result is correct?


Simplified Stored procedure:

Code: Select all

CREATE PROCEDURE `SP_UpdatePassword`(IN Username VARCHAR(256), IN NewPassword VARCHAR(256), OUT Result TINYINT(3))
BEGIN
	DECLARE MailboxID INT;                          
    
    SELECT cm_id INTO MailboxID FROM crewmail_mailbox WHERE cm_username = Username;
    
	UPDATE crewmail_mailbox SET cm_password = NewPassword
   	WHERE cm_id = MailboxID;

    SET Result = 3;   
END;

I am using mydac 5.20.0.12 and c++ builder 6.0.

Thanks.

[Dimon]

1. If you need to create parameters manually and without using automatically created parameters, set ParamCheck to False.

2. Your way to get the Result param is correct, but you should create this param before using it like this:

Code: Select all

TParam *prmResult = sp->Params->CreateParam(ftInteger, "Result", ptOutput);

[sklim82]

Thanks Dimon for the clarification.

I have tried to off the param check but it will show error "#42000 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use..."

Take the following sample:

Code: Select all

CREATE DEFINER = 'root'@'%' PROCEDURE `SP_Test`(IN ab TINYINT, OUT cd TINYINT)
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
	SET cd = ab + 1;
END;

Code: Select all

int i;
        TMyStoredProc *sp = new TMyStoredProc(NULL);
        sp->StoredProcName = "SP_TEST";
        sp->ParamCheck = false;
        sp->Params->Clear();

        TDAParam *prmCMInstallationID = sp->Params->CreateParam(ftInteger, "ab", ptInput);
        TDAParam *prmLicenseKey = sp->Params->CreateParam(ftInteger, "cd", ptOutput);

        sp->Connection = MyConnection1;
        MyConnection1->Open();
        sp->ExecProc();
        i =sp->Params->ParamByName("cd")->AsInteger;
        sp->Close();

        delete sp;

By the way, this error will not occur if the TMyStoredProc is created in design time.

Please advise, thanks.

[Dimon]

We have fixed this problem in the latest MyDAC build (5.50.0.34). Please, upgrade to this build to solve the problem.

[sklim82]

Dear Support team,

I have tried this issue with latest build 5.50.0.34 and it solves the problem but at the same time it show a debug error.

It works without error for previous stored procedures but it will show

"EConvertError with message ")' is not a valid integer value'

when I add a string type parameter.

Example:

Code: Select all

CREATE PROCEDURE `SP_Test`(IN a TINYINT, IN c VARCHAR(64), OUT b TINYINT)
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
	SET b = 1;
END;

Code: Select all

void TForm1::Test()
{
        int i;
        TMyStoredProc *sp = new TMyStoredProc(NULL);
        sp->StoredProcName = "SP_TEST";
        sp->ParamCheck = false;
        sp->Params->Clear();

        TDAParam *prmCMInstallationID = sp->Params->CreateParam(ftInteger, "a", ptInput);
        prmCMInstallationID->AsInteger = 5;
        TDAParam *prmC = sp->Params->CreateParam(ftString, "c", ptInput);
        prmC->AsString = "lim";
        sp->Params->CreateParam(ftInteger, "b", ptOutput);

        sp->Connection = MyConnection1;
        MyConnection1->Open();
        sp->ExecProc();
        i = sp->Params->ParamByName("b")->AsInteger;
        sp->Close();

        delete sp;
}

Please advice, thanks.

[Dimon]

This is specificity of executing stored procedure in the new MyDAC version.
You can hide this exception in the IDE using the Tools->Debugger Options...->Language Exceptions menu.
We will change this behaviour in the next MyDAC build.