Labwindows SQL Toolkit SELECT command example

[Old_Dave]

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
}