Database Connectivity in C

[Him3]

Hi, Everybody,
I wrote the program to connect to the Microsoft Sql server 2000. I have also defined constrains and triggers on Tables of SQL. Now I want to catch the error message generated by the triggers or constraints in c prog and want to show it to user. Can any body solve my problem?
I came to know about function SQLGetDiagRec that fetches errorno, message etc. but i don't know how to use it

:idea: I am not using any adodb or ado call or control.

[BlackDice]

Hi, Everybody, I wrote the program to connect to the Microsoft Sql server 2000. I have also defined constrains and triggers on Tables of SQL. Now I want to catch the error message generated by the triggers or constraints in c prog and want to show it to user. Can any body solve my problem? I came to know about function SQLGetDiagRec that fetches errorno, message etc. but i don't know how to use it :idea: I am not using any adodb or ado call or control.

how are you connecting? if you're just using straight odbc, you'll have to use the odbc functions to return the error messages. Using CRecordset you can probably get them through your CException-derived class counterparts. If you're using straight odbc I have some code that I could post back to give you an example. let me know

[Him3]

hi buddy
here's the sample code tht i am using.

HENV hEnv = NULL;
HDBC hDbc = NULL;
HSTMT hStmt = NULL;
UCHAR szDSN[SQL_MAX_DSN_LENGTH] = "himanshuDSN";
char szmodel[128];
char szmodel1[128];
char szmodel2[128];
SDWORD cbmodel;
SDWORD cbmodel1;
SDWORD cbmodel2;
RETCODE retcode;

//Query statement

UCHAR sql1[] = "Select subcode from registration where rollno=";
UCHAR sql2[] = "Select subcode from registration where rollno=";

int i,j,no = 1;
int rno;
char s[10];

SQLAllocEnv(&hEnv);
SQLAllocConnect(hEnv,&hDbc);

retcode = SQLConnect(hDbc,szDSN,SQL_NTS,'\0',SQL_NTS,'\0',SQL_NTS);

printf("Enter the roll no : ");
scanf("%d",&rno);
itoa(rno,s,10);
strcat(sql1,s);
if(retcode == SQL_SUCCESS|| retcode == SQL_SUCCESS_WITH_INFO)
{

retcode = SQLAllocStmt(hDbc,&hStmt);
retcode = SQLPrepare(hStmt,sql1,sizeof(sql1));
retcode = SQLExecute(hStmt);

printf("\nReading the records\n");

SQLBindCol(hStmt,no,SQL_C_CHAR,szmodel,sizeof(szmodel),&cbmodel);
//SQLBindCol(hStmt,no+1,SQL_C_CHAR,szmodel1,sizeof(szmodel1),&cbmodel1);
//SQLBindCol(hStmt,no+2,SQL_C_CHAR,szmodel2,sizeof(szmodel2),&cbmodel2);
retcode = SQLFetch(hStmt);

while(retcode == SQL_SUCCESS|| retcode == SQL_SUCCESS_WITH_INFO)
{
printf("\t%s \n",szmodel,szmodel1,szmodel2);
retcode = SQLFetch(hStmt);
}
SQLFreeStmt(hStmt,SQL_DROP);
SQLDisconnect(hDbc);
}else{
printf("Can not read the database\n");
}

SQLFreeConnect(hDbc);
SQLFreeEnv(hEnv

how are you connecting? if you're just using straight odbc, you'll have to use the odbc functions to return the error messages. Using CRecordset you can probably get them through your CException-derived class counterparts. If you're using straight odbc I have some code that I could post back to give you an example. let me know

[BlackDice]

here's a sample of a function that I have. It's kinda old so there's stuff in there that probably shouldn't be (like inMessage). Most of the ODBC functions you do return an SQLRETURN , so if the call failed, I use the SQLRETURN and send it to this function. The reason that the message getting mechanism is implemented in a loop is because there might be more than one error, and this will get them all. In this app I had the errors displaying in a textbox to the user. But you can do with them whatever you like.

void CQuickQueryView::displayODBCError(SQLRETURN sr, char *inMessage)
{
/*****************************************************************************
Author:diamond
Purpose:error handling routine 
******************************************************************************/
try
{
EndWaitCursor();
if(BadSqlReturn(sr))
{
SQLCHAR SqlState[6];
SQLINTEGER NativeError;
SQLCHAR	ErrMsg[SQL_MAX_MESSAGE_LENGTH];
int	 i = 1;
char	message[512];
CString	defaultmessage = inMessage;
strcpy(message, "");
if(inMessage)
{
	strcpy(message,inMessage);
 
}
 
 
//MessageBox(message);
CString str;
while(SQLGetDiagRec(SQL_HANDLE_DBC,hDbConn, i,SqlState, &NativeError,
	 ErrMsg,sizeof(ErrMsg),NULL) != SQL_NO_DATA)
{
	sprintf(message,
	 "Message: %d\nSQLSTATE: %s\nNativeError: %d\nDescription: %s\n\n",
	 i++, SqlState, NativeError, ErrMsg);
	str += message;
}
defaultmessage += str;
m_txtMessage.SetWindowText(defaultmessage);
MessageBeep(MB_ICONQUESTION);
}
}
catch(CException* err)
{
pErrObject->HandleError(err," "," ");
}
catch(...)
{
AfxMessageBox("Unhandled Error ");
}
}

here's a sample of how I use it:

//get the number of columns
sr = SQLNumResultCols(hstmt,(SQLSMALLINT*) &cols);
if(BadSqlReturn(sr))
{
char message[200];
sprintf(message, "Error getting number of columns: %d\n" ,sr);
displayODBCError(sr,message);
return;
}

BadSQLReturn() is just a function that returns TRUE if sr == SQL_SUCCESS_WITH_INFO or sr == SQL_ERROR

[Him3]

Hi Diamond,
Thanks a lot for the code.

[kanchoo]

hi,
i m just a beginner with the C language. and i want to know for some good books or material available anywhere on Database connectivity in c using excel, sql.
please can u refer me some books or some links for the same?
waiting for ur reply
-kanchoo

[magdhi]

Hi, Everybody, I wrote the program to connect to the Microsoft Sql server 2000. I have also defined constrains and triggers on Tables of SQL. Now I want to catch the error message generated by the triggers or constraints in c prog and want to show it to user. Can any body solve my problem? I came to know about function SQLGetDiagRec that fetches errorno, message etc. but i don't know how to use it :idea: I am not using any adodb or ado call or control.

hi
Can u give that connectivity code to me

[rpiper138]

This is an MSSQL API call and it will only get you the diag info that is directly involved in the previous action or transaction on the current connection.

rc = SQLExecDirect(hstmt, SQLStmt, SQL_NTS);
if ((rc == SQL_SUCCESS_WITH_INFO) || (rc == SQL_ERROR)) {
i = 1;
while ((SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, i, SqlState, &NativeError,
Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) {
DisplayError(SqlState,NativeError,Msg,MsgLen);
i++;
}
}
if((rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO){
// handle record
}

[Gowrishankar007]

Ok I prefer Byron S Gottfried's "Programming with C"

[monu chauhan]

fundamentals of connectivity
how to connect to ms access database