[Asterisk-Dev] calling stored procedure

[Brian West]

Why not just create a trigger to call the procedure for you?

/b

On Feb 21, 2005, at 8:21 AM, Kamran Ahmad wrote:

> 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
> _______________________________________________
> Asterisk-Dev mailing list
> Asterisk-Dev at lists.digium.com
> http://lists.digium.com/mailman/listinfo/asterisk-dev
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-dev