1/06/2014

ODBC-API存取MS-SQL預存程序(Stored Procedure)的正確用法

回想之前...我曾被ODBC-API針對MS-SQL預存程序(Stored Procedure)的使用方式搞到頭快爆了~
在網路上搜尋到很多相關資料,但是都沒有正確的使用範例!?!
一堆人在討論,但是有解答的幾乎沒有,或是我沒找到!?!
只有那些微軟的MSDN英文說明有非常多的解釋!但是這麼多英文...慢慢看...實在會瘋掉!
而且重點是要有直接的程式範例參考啊!
不然MSDN寫再多文字也只是曲高和寡,實質意義不大!

而且書店的SQL電腦書堆裡竟然沒有一本有完整介紹ODBC-API!?!
難道這技術這麼差嗎?還是太舊了,過時了,沒人想使用!?!怪怪~
不過,因為我蠻堅持要使用ODBC-API來存取MS-SQL預存程序,
(因為我想將已學的ODBC繼續強化)

所以我足足花了四天時間待在外包公司內做研究!終於東湊西湊的,將正確的用法找出來了!
而且還有外包公司的韓國工程師來支援我,讓我更快瞭解預存程序的用法!
ps : 有趣的是... 小事情我們就用英文溝通,大事情才靠韓文翻譯人員幫忙!
  能用英文來溝通,工作,這點我倒是覺得很新鮮!

在此我就和大家分享我辛苦的小研究喔!
ODBC-API的初始化我就不列出了,這種基本資料網路上還是很多的!
我就直接進入主題來說明!

//-------------------------

// 以下是SQL中的sp_test預存程序的範例:(自己要去MS-SQL中定義sp_test喔)
CREATE Procedure sp_test
    @val1    As    int,
    @val2    As    int output,
    @uid1    As    varchar(12),
    @uid2    As    varchar(12) output

As

set @val2 = @val1;

set @uid2 = @uid1;

return 1
GO


//-------------------------

// 以下是用ODBC-API來呼叫SQL中的sp_test預存程序的範例

long MSSQLClass::vStoredProcedure_SelectData()
{
    SQLRETURN   rc = 0;
    char szQuery[ SQL_STRING_MAX ] = { 0 };

    rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt );
    ERROR_OUT( rc, "MSSQLClass::vStoredProcedure_SelectData   error : SQLAllocHandle" );

    // 下面的?號,是更下面的SQLBindParameter要綁定參數的順序
    // 而第一個?號是回傳值(如果需要有回傳的話)
    // 若是沒有回傳值的預存程序,則輸入的命令是 {call sp_test(?,?,?,?)}
    strcat_s( szQuery, SQL_STRING_MAX, "{? = call sp_test(?,?,?,?)}" );

    // 準備要下的SQL命令
    rc = ::SQLPrepare( hstmt, (SQLCHAR *)szQuery, SQL_NTS );
    ERROR_OUT( rc, "MSSQLClass::vStoredProcedure_SelectData   error : SQLPrepare" );

    SQLINTEGER receive_length1 = 0;
    SQLINTEGER receive_length2 = 0, receive_length3 = 0;
    SQLINTEGER receive_length4 = 0;

    // 這個SQL_NTS代號是SQL_PARAM_INPUT的字串時必須用的,
    // 否則預存程序會接收不到傳入的字串
    SQLINTEGER char_type_input_code = SQL_NTS;

    SQLINTEGER return_value = -99;

    SQLBIGINT val1 = 10, val2 = 0; // val2要先清為0,不然會收不到資訊!
    SQLCHAR uid1[ 20 ] = { "hello" }, uid2[ 20 ] = { 0 }; // uid2要先清為0,不然會收不到資訊!
      
    // 第一個SQLBindParameter是接收回傳值,所以要使用SQL_PARAM_OUTPUT
    rc = ::SQLBindParameter( hstmt, 1, SQL_PARAM_OUTPUT,
                                 SQL_C_SLONG, SQL_INTEGER, 0, 0,
                                 &return_value, 0,
                                 &receive_length1 );
    ERROR_OUT( rc, "MSSQLClass::vStoredProcedure_SelectData   error : SQLBindParameter" );

    // 第二個是只傳入val1數值,所以用SQL_PARAM_INPUT
    rc = ::SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT,
                                 SQL_C_ULONG, SQL_INTEGER, 0, 0,
                                 &val1, 0,
                                 &receive_length2 );
    ERROR_OUT( rc, "MSSQLClass::vStoredProcedure_SelectData   error : SQLBindParameter" );

    // 第三個是預存程序內會改變val2數值,所以必須使用SQL_PARAM_INPUT_OUTPUT
    rc = ::SQLBindParameter( hstmt, 3, SQL_PARAM_INPUT_OUTPUT,
                                 SQL_C_ULONG, SQL_INTEGER, 0, 0,
                                 &val2, 0,
                                 &receive_length3 );
    ERROR_OUT( rc, "MSSQLClass::vStoredProcedure_SelectData   error : SQLBindParameter" );

    // 第四個是只傳入uid1字串,所以用SQL_PARAM_INPUT
    // 這裡有個重點,就是SQLBindParameter的最後一個參數必須填入SQL_NTS代號!
    // 但是因為他是只能傳入指標,所以必須將代號先放在char_type_input_code變數上
    // 如果不傳入SQL_NTS代號,則預存程序會接收不到傳入的字串喔!
    // 這點,我花了很多時間研究原因,終於讓我發現到這個設定上的小眉角!
    rc = ::SQLBindParameter( hstmt, 4, SQL_PARAM_INPUT,
                                 SQL_C_CHAR, SQL_VARCHAR, sizeof( uid1 ), 0,
                                 uid1, sizeof( uid1 ),
                                 &char_type_input_code ); // 這裡是傳入字串的重點設定喔
    ERROR_OUT( rc, "MSSQLClass::vStoredProcedure_SelectData   error : SQLBindParameter" );

    // 第五個是預存程序內會改變uid2字串,所以必須使用SQL_PARAM_INPUT_OUTPUT
    rc = ::SQLBindParameter( hstmt, 5, SQL_PARAM_INPUT_OUTPUT,
                                 SQL_C_CHAR, SQL_VARCHAR, sizeof( uid2 ), 0,
                                 uid2, sizeof( uid2 ),
                                 &receive_length4 );
    ERROR_OUT( rc, "MSSQLClass::vStoredProcedure_SelectData   error : SQLBindParameter" );
  
    // 執行準備好的命令
    rc = ::SQLExecute( hstmt );
    ERROR_OUT( rc, "MSSQLClass::vStoredProcedure_SelectData   error : SQLExecute" );

    // 如果預存程序內有多條SQL敘述,則需要再執行SQLMoreResults,依序將資料取出!
    while( 1 )
    {
        rc = ::SQLMoreResults( hstmt );
        if( rc == SQL_NO_DATA )
        {
            break;
        }
        else
            ERROR_OUT( rc, "MSSQLClass::vStoredProcedure_SelectData   error : SQLMoreResults" );
    }

    ::SQLFreeHandle( SQL_HANDLE_STMT, hstmt );
    hstmt = SQL_NULL_HSTMT;

    return return_value;
}


還有另一個用法,就是能藉由預存程序,取得一連串的資料!
例如:一次取出50筆朋友清單資料!
這用法其實也很簡單,等我有空時再發佈嚕~


最後要說的是,就將這很辛苦的小小研究成果獻給有緣人嚕!



ps : 我又多花了三天,才解決預存程序內若有多條SQL敘述...會發生取不到資料的問題,
  原來是需要使用SQLMoreResults來取出!找的我快瘋了!



2 則留言:

Chester Cheng 提到...

不早說 這問我就行了

匿名 提到...

您好,我是國立臺灣海洋大學的學生,在高中時期就已經有在注意您在Yahoo! Blog的文章了,對您美術出身卻能跨足到程式設計的領域並想開發遊戲引擎很感興趣。在10/6(一)、10/7(二)兩天晚上有我校電玩社以及數媒社的聯合迎新活動,我們希望能夠邀請您前來為兩社社員以及海大學生做一個經驗的分享。如果您願意的話請與我聯絡,謝謝。

https://www.facebook.com/bangjie.han