Code:
/*
This sample is just my interpretation of sql.h
it works fine for me if you got another sample
just post-it here.
Please excuse my english and my coding style
im just leraning.....
by the way ....... im using lcc-win32 it's a
very good and comfortable complier-debugger
Greetings!
*/
#include <stdio.h>
#include <string.h>
#include <sqlext.h> //in win32 it includes sql.h
/* Unix users try this:
#include <odbc/sql.h>
#include <odbc/sqlext.h>
*/
SQLHENV henv;
SQLHDBC hdbc;
#define DSN "base"
#define UID "user"
#define PWD "password"
#define INSERT_QUERY "insert into tabla (username,domain,description) values(" "'%s','%s','%s')"
#define SELECT_QUERY "select id,username,domain,description from table"
#define DELETE_QUERY "delete from table where " "usermane='rodolfo' and " "dominio='@cabeza.com'"
void sql_close(void);
int show_error(SQLHENV henv,SQLHDBC hdbc,SQLHSTMT hstmt);
int main(){
SQLHSTMT hsql;
int rc;
unsigned long id;
char usuario[256] = {0};
char dominio[256] = {0};
char descrip[256] = {0};
if(SQLAllocEnv(&henv) != SQL_SUCCESS)
return -1;
if(SQLAllocConnect(henv,&hdbc) != SQL_SUCCESS){
sql_close();
return -1;
}
rc = SQLConnect(hdbc,
(unsigned char *)DSN,SQL_NTS,
(unsigned char *)UID,SQL_NTS,
(unsigned char *)PWD,SQL_NTS);
if(rc != SQL_SUCCESS){
sql_close();
return -1;
}
strcpy(usuario,"rodolfo");
strcpy(dominio,"@cabeza.com");
strcpy(descrip,"rodolfo cabeza");
char sql[2000] = {0};
sprintf(sql,INSERT_QUERY,
usuario,dominio,descrip);
if(SQLAllocStmt(hdbc,&hsql) != SQL_SUCCESS){
sql_close();
return -1;
}
if(SQLExecDirect(hsql,sql,SQL_NTS) != SQL_SUCCESS){
SQLFreeHandle(SQL_HANDLE_STMT,hsql);
sql_close();
return -1;
}
/*Note that between the inset statement and the select
statement theres no need of
SQLFreeHandle(SQL_HANDLE_STMT,hsql)
i guess that is because the insert does not returns data
but (look at delete statement) before i can use the same
HSTMT hsql with another non-data-return query i had to put
SQLFreeHandle(SQL_HANDLE_STMT,hsql)
*/
strcpy(sql,SELECT_QUERY);
if(SQLExecDirect(hsql,sql,SQL_NTS) != SQL_SUCCESS){
SQLFreeHandle(SQL_HANDLE_STMT,hsql);
sql_close();
return -1;
}
/*using SQLGetData avoid errors when one of the fields is null
of course you can use SQLBindCol(same sintax that SQLGetData)
but if one of the fields got a null value, the SQLFetch()
gives an error that i just cant solve (by now)
*/
while(SQLFetch(hsql) != SQL_NO_DATA){
//i used sizeof() here 'cause id is unsigned long,so sizeof() returns its size
SQLGetData(hsql,1,SQL_C_ULONG,&id,sizeof(id),NULL);
SQLGetData(hsql,2,SQL_C_CHAR,&usuario,255,NULL);
SQLGetData(hsql,3,SQL_C_CHAR,&dominio,255,NULL);
SQLGetData(hsql,4,SQL_C_CHAR,&descrip,255,NULL);
printf("%ld) email: %s%s (%s)\n",
id,usuario,dominio,descrip);
id = 0;
usuario[0] = 0;
dominio[0] = 0;
descrip[0] = 0;
}
SQLFreeHandle(SQL_HANDLE_STMT,hsql);
SQLAllocStmt(hdbc,&hsql);
strcpy(sql,DELETE_QUERY);
if(SQLExecDirect(hsql,sql,SQL_NTS) != SQL_SUCCESS){
show_error(henv,hdbc,hsql);
SQLFreeHandle(SQL_HANDLE_STMT,hsql);
sql_close();
return -1;
}
SQLFreeHandle(SQL_HANDLE_STMT,hsql);
sql_close();
return 0;
}
void sql_close(void){
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
SQLFreeHandle(SQL_HANDLE_ENV,henv);
}
int show_error(SQLHENV henv,SQLHDBC hdbc,SQLHSTMT hstmt){
UCHAR szSqlState[500];
SDWORD pfNativeError;
UCHAR szErrorMsg[500];
SWORD cbErrorMsgMax = 500;
SWORD pcbErrorMsg;
int retcode;
retcode = SQLError(henv, hdbc, hstmt, szSqlState, &pfNativeError,
szErrorMsg, cbErrorMsgMax, &pcbErrorMsg);
printf("%d - SQLSTATE: %s : %s \n",retcode, szSqlState, szErrorMsg);
return(0);
}