C# & DB2 Error: An unexpected token “END-OF-STATEMENT” was found following “get_profile_internet”"

Question

I'm getting the following error message when I try to call a DB2 stored procedure from my C# Code:

ERROR [42601] [IBM][CLI Driver][DB2/NT64] SQL0104N An unexpected token "END-OF-STATEMENT" was found following "get_profile_internet". Expected tokens may include: "JOIN ". SQLSTATE=42601

What am I doing wrong?

---

Here's the DB2 - Stored procedure code

create procedure db2admin.pr_get_profile_internet (
    IN p_profile_internet_id INT
)
BEGIN
    DELCARE c_profile_internet CURSOR WITH RETURN TO CLIENT FOR
        SELECT * 
        FROM profile_internet 
        WHERE profile_internet_id = p_profile_internet_id;

    OPEN c_profile_internet;
END;

---

Here's my C# Code

    public CDataResult<DataSet> GetProfileInternet() {
        string v_connection_string = General.GetDBConnection();
        CDataResult<DataSet> v_result = new CDataResult<DataSet>();
        OdbcConnection v_connection = new OdbcConnection(v_connection_string);
        try {
            // Open Connection
            v_connection.Open();
            OdbcCommand cmd = new OdbcCommand("db2admin.pr_get_profile_internet", v_connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("p_profile_internet_id", OdbcType.Int);
            cmd.Parameters["p_profile_internet_id"].Value = this.ProfileInternetId;
            OdbcDataAdapter da = new OdbcDataAdapter(cmd);
            DataSet ds = new DataSet("GetProfileInternet");
            da.Fill(ds);
            if (ds != null && ds.Tables[0].Rows.Count > 0) {
                v_result.ErrorOccured = false;
                v_result.ErrorMessageEn = "";
            } else {
                v_result.ErrorOccured = true;
                v_result.ErrorMessageEn = "Error! User code is not valid.";
            }
        } catch (Exception ex) {
            v_result.ErrorOccured = true;
            v_result.ErrorMessageEn = "DB error: CProfileInternet->GetProfileInternet()";
            v_result.ErrorException = ex.Message + '\n' + ex.InnerException;
        } finally {
            if (v_connection != null) {
                v_connection.Close();
            }
        }
        return v_result;
    }

---

here's the latest C# code version:

    public CDataResult<DataSet> GetProfileInternet()
    {
        string v_connection_string = General.GetDBConnection();
        CDataResult<DataSet> v_result = new CDataResult<DataSet>();
        OdbcConnection v_connection = new OdbcConnection(v_connection_string);

        try
        {
            // Open Connection
            v_connection.Open();

            OdbcCommand cmd = new OdbcCommand("call db2admin.pr_get_profile_internet", v_connection);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("p_profile_internet_id", OdbcType.Int);
            cmd.Parameters["p_profile_internet_id"].Direction = ParameterDirection.Input;
            cmd.Parameters["p_profile_internet_id"].Value = this.ProfileInternetId;

            OdbcDataAdapter da = new OdbcDataAdapter(cmd);
            DataSet ds = new DataSet("GetProfileInternet");
            da.Fill(ds);

            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                v_result.ErrorOccured = false;
                v_result.ErrorMessageEn = "";
            }
            else
            {
                v_result.ErrorOccured = true;
                v_result.ErrorMessageEn = "Error! User code is not valid.";
            }
        }

        catch (Exception ex)
        {
            v_result.ErrorOccured = true;
            v_result.ErrorMessageEn = "DB error: CProfileInternet->GetProfileInternet()";
            v_result.ErrorException = ex.Message + '\n' + ex.InnerException;
        }

        finally
        {
            if (v_connection != null)
            {
                v_connection.Close();
            }
        }

        return v_result;
    }

Here's my DB2 stored procedure code:

create procedure db2admin.pr_get_profile_internet

( IN p_profile_internet_id int ) Begin

Declare c_profile_internet CURSOR WITH RETURN TO CLIENT FOR
Select  * 
From    db2admin.profile_internet 
Where   profile_internet_id = p_profile_internet_id;

Open c_profile_internet;

End;

Here's my table structure

create table profile_internet
(
profile_internet_id         int not null, 
code_client                 varchar(30) not null,
prenom                      varchar(100) not null,
nom                         varchar(100) not null,
adresse                     varchar(100) not null,
appartement                 varchar(100) null,
ville                       varchar(100) not null,
code_postal                 varchar(30) not null,
code_province               varchar(10) not null,
telephone_domicile          varchar(30) null,
telephone_mobile            varchar(30) null,
telephone_bureau            varchar(30) null,
extension_bureau            varchar(10) null,
date_de_naissance           date null,
langue                      varchar(1)  not null,
courriel                    varchar(100) null,
profil_actif                smallint not null,
date_creation               timestamp not null,
creer_par                   varchar(30) not null,
date_modification           timestamp not null,
modifier_par                varchar(30) not null,
    primary key (profile_internet_id)
)
;

CREATE UNIQUE INDEX idx_profile_internet_code_client ON profile_internet (code_client);
CREATE UNIQUE INDEX idx_profile_internet_courriel ON profile_internet (courriel);

What shall I do to avoid getting the following error message:

ERROR [42884] [IBM][CLI Driver][DB2/NT64] SQL0440N No authorized routine named "DB2ADMIN.PR_GET_PROFILE_INTERNET" of type "PROCEDURE" having compatible arguments was found. SQLSTATE=42884

Answer 1

Looks like you should be opening a DataReader for when calling the stored procedure so that it can handle the End of Statement

OdbcDataReader Reader = cmd.ExecuteReader();

then

while (Reader.Read())
            {
                try
                {
                }

            }

Answer 2

You must specify full ODBC syntax with 'CALL' verb, not just SP name.

Answer 3

I want to share the solution with you for the future.

First of all I was working with Visual Studio 2005 and I installed DB2 Express 10.1.2.2 on my local machine. I added the reference to the file C:\Program Files\IBM\SQLLIB\BIN\netf20\IBM.Data.DB2.dll and I didn't work. It keeps return the following error message when it tries to open connection: ERROR - no error information available

To fix the issue, I created the same project with Visual Studio 2010 and I added the reference to the file C:\Program Files\IBM\SQLLIB\BIN\netf40\IBM.Data.DB2.dll. In the properties of the project, I changed the Target Framework to: .Net Framework 4 and I was able to call DB2 Stored procedure

Here's C# working code:

using IBM.Data.DB2;


public DataSet GetProfileInternet()
{
    string v_connection_string = "Database=myDatabase;Server=myServer:myPort;UID=myUserId;PWD=myPassword";
    DB2Connection v_connection = new DB2Connection(v_connection_string);

    try
        {
    // Open Connection
    v_connection.Open();

    DB2Command cmd = new DB2Command("pr_get_profile_internet", v_connection);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("p_profile_internet_id", DB2Type.Integer);
    cmd.Parameters["p_profile_internet_id"].Direction = ParameterDirection.Input;
    cmd.Parameters["p_profile_internet_id"].Value = 1;

    DB2DataAdapter da = new DB2DataAdapter(cmd);
    DataSet ds = new DataSet("GetProfileInternet");
    da.Fill(ds);

    return ds;

}

catch (Exception ex)
{
    throw ex;
}

finally
{
    if (v_connection != null)
    {
        v_connection.Close();
    }
}
}