I wanted to post a basic WORKING example that powers through some of the gotchya's
when doing a select command into a Microsoft SQL Server database.
This was tested and works. Hopefully this will help someone out.
int Retrieve_Other_Data(void)
{
int DBStatus;
int DBhandle;
int Hstmt;
char DBstatement[400];
char RecordKey1[11];
int Stat;
int Rescode;
int DBErrorCode;
char MsgStr[400];
char Param[200];
DisableBreakOnLibraryErrors();
// Note:
// In Windows, You have to do: Administrative Tools -> ODBC Data Sources (64-bit)
// and create a User DSN name that you will use here below
// We use ODBC 17 to connect to Microsoft SQL ServerDB.
// We are using Server Authentication
// Open DB
DBhandle = DBConnect("DSN=ManexDSN; UID=Dave Garrett; PWD=xyz");
if ((DBhandle == -11) || (DBhandle == -10))
{
MessagePopup("", "DB TROUBLE");
return -1;
}
if (DBhandle <= 0)
{
MessagePopup("", "DB TROUBLE");
return -2;
}
// Query Part Number with SELECT statement
// The '?' will be replaced with a parameter that we specify later
// Note that Table name includes the Database name and "dbo" and the Tablename
// because we have two databases on the same server.
// You have to specify which DB, and which Table
DBstatement[0] = 0;
sprintf(DBstatement,"SELECT UNIQ_KEY FROM MANEX.DBO.INVENTOR WHERE PART_NO = ?");
Hstmt = DBPrepareSQL(DBhandle,DBstatement);
if (Hstmt <= 0)
{
DBDisconnect(DBhandle);
MessagePopup("", "DB STATEMENT TROUBLE");
return -3;
}
// This is the prog. variable that we are binding to the info
// returned by the SELECT statement
Rescode = DBBindColChar (Hstmt, 1, 11, RecordKey1, &Stat,"");
if (Rescode != DB_SUCCESS)
{
DBDisconnect(DBhandle);
MessagePopup("", "DB STATEMENT TROUBLE");
return -4;
}
// This input will replace the '?' in the SELECT statement
Param[0] = 0;
strcpy(Param,"158-0001051");
// Tell the DB the input parameter. Note add +1 to length to account for NULL terminator on string.
Rescode = DBCreateParamChar (Hstmt,"",DB_PARAM_INPUT, Param, sizeof(Param)+1);
if (Rescode != DB_SUCCESS)
{
DBDisconnect(DBhandle);
MessagePopup("", "DB STATEMENT TROUBLE");
return -7;
}
Rescode = DBExecutePreparedSQL (Hstmt);
if (Rescode != DB_SUCCESS)
{
DBErrorCode = DBNativeError();
DBDisconnect(DBhandle);
MessagePopup("", "DB STATEMENT TROUBLE");
return -5;
}
// Fetch the values. Note that as each record that matched
// the SELECT criteria is fetched, the
// column values are placed in the specified variable.
Fetch = TRUE;
while (Fetch)
{
Rescode = DBFetchNext (Hstmt);
if (Rescode != DB_SUCCESS)
{
Fetch = FALSE;
break;
}
MsgStr[0] = 0;
sprintf(MsgStr,"Value = %s",RecordKey1);
MessagePopup("",MsgStr);
}
// Because there are no output parameters, you do
// not have to close the statement separately.
Rescode = DBDeactivateSQL (Hstmt);
if (Rescode != DB_SUCCESS)
{
DBDisconnect(DBhandle);
MessagePopup("", "DB STATEMENT TROUBLE");
return -6;
}
// Close DB
DBDisconnect(DBhandle);
return 0; // if 0, then all OK
}
