[Asterisk-Dev] calling stored procedure

[Kamran Ahmad]

hello

i am writting an application to call procedure from MS
Sql Server. Can anyone tell me whend i want to call MS
Sql Server Procedure. it is giving me error when i try
to bind input/output parameter dynamicly.

but when i try to call procedure by hardcoding it is
working i want to bind parameters for input and
output.
-----------------------------------------------------
ODBC_res = SQLPrepare( ODBC_stmt, "{call AuthUser
('kamran', 'ahmad')}", SQL_NTS );
-----------------------------------------------------

here is my code two functions first db_check_connected
is working well second one ExecProcedure() is only
working in case i mentioned early. can you check it
what could be the problem. 
this solution is defined on this site
http://developer.mimer.se/interfaces/interface_3.htm

int db_check_connected(void)
{
	long int ODBC_err;
	short int ODBC_mlen;
	int ODBC_res;
	char ODBC_msg[200], ODBC_stat[10];

	if (ODBC_env == SQL_NULL_HANDLE || connected == 0)
	{
		ODBC_res = SQLAllocHandle(SQL_HANDLE_ENV,
SQL_NULL_HANDLE, &ODBC_env);
		if ((ODBC_res != SQL_SUCCESS) && (ODBC_res !=
SQL_SUCCESS_WITH_INFO))
		{
			printf("app_prepaid_auth_pin: Error
AllocHandle\n");
			
			connected = 0;
			return -1;
		}

		ODBC_res = SQLSetEnvAttr(ODBC_env,
SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

		if ((ODBC_res != SQL_SUCCESS) && (ODBC_res !=
SQL_SUCCESS_WITH_INFO))
		{
			printf("app_prepaid_auth_pin: Error SetEnv\n");
			
			SQLFreeHandle(SQL_HANDLE_ENV, ODBC_env);
			connected = 0;
			return -1;
		}

		ODBC_res = SQLAllocHandle(SQL_HANDLE_DBC, ODBC_env,
&ODBC_con);

		if ((ODBC_res != SQL_SUCCESS) && (ODBC_res !=
SQL_SUCCESS_WITH_INFO))
		{
			printf("app_prepaid_auth_pin: Error AllocHDB %d\n",
ODBC_res);
			
			SQLFreeHandle(SQL_HANDLE_ENV, ODBC_env);
			connected = 0;
			return -1;
		}
		
		SQLSetConnectAttr(ODBC_con, SQL_LOGIN_TIMEOUT,
(SQLPOINTER *)10, 0);	
	}
	
	printf("app_prepaid_auth_ping:information
DSN=%s;username=%s;password=%s\n",dsn,username,password);

	ODBC_res = SQLConnect(ODBC_con, (SQLCHAR*)dsn,
SQL_NTS, (SQLCHAR*)username, SQL_NTS,
(SQLCHAR*)password, SQL_NTS);

	if ((ODBC_res != SQL_SUCCESS) && (ODBC_res !=
SQL_SUCCESS_WITH_INFO))
	{
		printf("app_prepaid_auth_pin: Error SQLConnect
%d\n", ODBC_res);
		
		SQLGetDiagRec(SQL_HANDLE_DBC, ODBC_con, 1,
ODBC_stat, &ODBC_err, ODBC_msg, 100, &ODBC_mlen);
		SQLFreeHandle(SQL_HANDLE_ENV, ODBC_env);
		connected = 0;
		return -1;
	}
	else
	{
		printf("app_prepaid_auth_pin: successfully1
Connected to %s\n", dsn);
		printf("app_prepaid_auth_pin: successfully Connected
to %s\n", dsn);
			//ast_verbose( VERBOSE_PREFIX_4
"app_prepaid_auth_pin: successfully Connected to
%s\n", dsn);
		connected = 1;
	}
	
	return 0;
}

static int ExecProcedure()
{
	char sqlcmd[sqlcommandsize] = "";
	int res = 0;
	char * prompt = NULL;
	
	char ODBC_stat[10];
	char ODBC_msg[200];
	int ODBC_mlen;
	
	if (connected)
	{
		int res=0;
		
		SQLCHAR     user[21];
		SQLCHAR     password[21];
		SQLINTEGER  userInd, passwordInd;
		SQLUINTEGER result;
		SQLINTEGER  resultInd;
		
		int ODBC_res;
		
		ODBC_res =
SQLAllocHandle(SQL_HANDLE_STMT,ODBC_con,&ODBC_stmt);
		
		if (ODBC_res == SQL_ERROR)
		{
			printf( "Failed to allocate connection handle\n" );
		}
		
		//ODBC binding parameter to check result after
select
		
		ODBC_res = SQLPrepare( ODBC_stmt, "{call AuthUser
(?, ?)}", SQL_NTS );
		
		if ((ODBC_res != SQL_SUCCESS) && (ODBC_res !=
SQL_SUCCESS_WITH_INFO))
		{
			printf("Error in PREPARE %d\n", ODBC_res);
			SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmt);
			connected = 0;
			return 0;
		}
		
		SQLSMALLINT numparams;
		
		SQLBindParameter( ODBC_stmt, 1, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, 20, 0, user, sizeof(user),
&userInd );
		SQLBindParameter( ODBC_stmt, 2, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, 20, 0, password,
sizeof(password), &passwordInd );
		
		SQLSetStmtAttr( ODBC_stmt, SQL_ATTR_QUERY_TIMEOUT,
(SQLPOINTER)10, 0 );
		
		SQLNumParams( ODBC_stmt, &numparams );
		
		strcpy(user, "Kamran");
		userInd = SQL_NTS;
		strcpy(password, "ahmad");
		passwordInd = SQL_NTS;
		
		printf("calling AuthUser procedure\n");
		
		ODBC_res = SQLExecute(ODBC_stmt);
		
		printf("called AuthUser procedure now checking
result\n");
		
		if ((ODBC_res != SQL_SUCCESS) && (ODBC_res !=
SQL_SUCCESS_WITH_INFO))
		{
			printf( "Error in Query %d\n", ODBC_res);
			
		
SQLGetDiagRec(SQL_HANDLE_DBC,ODBC_con,1,ODBC_stat,&ODBC_msg,100,
&ODBC_mlen,NULL);
			SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmt);
			
			printf("app_prepaid_auth_pin:Fatal Error:123\n");
			
			prompt = "prepaid-no-aaa";
			
			connected = 0;
			return -1;
		}
		else
		{
			printf("app_prepaid_auth_pin:Query Successful\n");
			
			connected = 1;
		}
		
		return 0;
	}
	else
	{
		//We are not connected to the DB
		return -1;
	}

}



		
__________________________________ 
Do you Yahoo!? 
Yahoo! Mail - 250MB free storage. Do more. Manage less. 
http://info.mail.yahoo.com/mail_250