C Examples: DbiQSetParams

Create a Query result set table.

This example uses the following input:
fQFunction1(hDb, "Result");

DBIResult fQFunction1(hDBIDb hTmpDb, pCHAR TblName)

{

DBIResult rslt;

hDBIStmt hStmt;

hDBICur hQryCur = 0, hNewCur = 0;

FLDDesc FldDesc;

CHAR SQL[] = "select c.cust_no, c.name, c.state_prov, "

" o.'Sale Date', o.'Month' "

"from 'cust.dbf' c, orders o "

"where (c.cust_no between ? and 4999) "

" and (c.cust_no = o.'customer no') "

" and c.state_prov is not null "

"order by c.name desc, o.'Month' asc; ";

DFLOAT Cust = 2000.00;

// Create a Field Descriptor for the parameter

memset(&FldDesc, 0, sizeof(FLDDesc));

FldDesc.iFldNum = 1;

FldDesc.iFldType = fldFLOAT;

FldDesc.iUnits1 = 1;

FldDesc.iLen = sizeof(Cust);

// Allocate a statement handle

rslt = Chk(DbiQAlloc(hTmpDb, qrylangSQL, &hStmt));

if (rslt != DBIERR_NONE)

return rslt;

// Prepare the SQL statement

rslt = Chk(DbiQPrepare(hStmt, SQL));

if (rslt != DBIERR_NONE)

{

DbiQFree(&hStmt);

return rslt;

}

// Since only one parameter is used, there is no need for a record buffer.

// Use the Cust variable itsself.

rslt = Chk(DbiQSetParams(hStmt, 1, &FldDesc, (pBYTE)&Cust));

if (rslt != DBIERR_NONE)

{

DbiQFree(&hStmt);

return rslt;

}

// Execute the SQL statement

rslt = Chk(DbiQExec(hStmt, &hQryCur));

if (rslt != DBIERR_NONE)

{

DbiQFree(&hStmt);

return rslt;

}

// Save the Result set to disk and close the result query(hQryCur)

// Now there is an open cursor on the result set on disk.

rslt = Chk(DbiQInstantiateAnswer(hStmt, hQryCur, TblName, szPARADOX,

TRUE, &hNewCur));

if (rslt != DBIERR_NONE)

{

DbiQFree(&hStmt);

return rslt;

}

// Close the cursor

if (hNewCur != 0)

Chk(DbiCloseCursor(&hNewCur));

return rslt;

}

DBIResult fQFunction1(hDBIDb hTmpDb, pCHAR TblName)

{

DBIResult rslt;

hDBIStmt hStmt;

hDBICur hQryCur = 0, hNewCur = 0;

FLDDesc FldDesc;

CHAR SQL[] = "select c.cust_no, c.name, c.state_prov, "

" o.'Sale Date', o.'Month' "

"from 'cust.dbf' c, orders o "

"where (c.cust_no between ? and 4999) "

" and (c.cust_no = o.'customer no') "

" and c.state_prov is not null "

"order by c.name desc, o.'Month' asc; ";

DFLOAT Cust = 2000.00;

// Create a Field Descriptor for the parameter

memset(&FldDesc, 0, sizeof(FLDDesc));

FldDesc.iFldNum = 1;

FldDesc.iFldType = fldFLOAT;

FldDesc.iUnits1 = 1;

FldDesc.iLen = sizeof(Cust);

// Allocate a statement handle

rslt = DbiQAlloc(hTmpDb, qrylangSQL, &hStmt);

if (rslt != DBIERR_NONE)

return rslt;

// Prepare the SQL statement

rslt = DbiQPrepare(hStmt, SQL);

if (rslt != DBIERR_NONE)

{

DbiQFree(&hStmt);

return rslt;

}

// Since only one parameter is used, there is no need for a record buffer.

// Use the Cust variable itsself.

rslt = DbiQSetParams(hStmt, 1, &FldDesc, (pBYTE)&Cust);

if (rslt != DBIERR_NONE)

{

DbiQFree(&hStmt);

return rslt;

}

// Execute the SQL statement

rslt = DbiQExec(hStmt, &hQryCur);

if (rslt != DBIERR_NONE)

{

DbiQFree(&hStmt);

return rslt;

}

// Save the Result set to disk and close the result query(hQryCur)

// Now there is an open cursor on the result set on disk.

rslt = DbiQInstantiateAnswer(hStmt, hQryCur, TblName, szPARADOX,

TRUE, &hNewCur);

if (rslt != DBIERR_NONE)

{

DbiQFree(&hStmt);

return rslt;

}

// Close the cursors

if (hNewCur != 0)

DbiCloseCursor(&hNewCur);

if (hQryCur != 0)

DbiCloseCursor(&hQryCur);

return rslt;

}

Inserts GIF image into a Sybase table with a column of type Image

#include <stdio.h>

#include <io.h>

#include <fcntl.h>

#include <assert.h>

#include <memory.h>

#include <malloc.h>

#include <string.h>

#include <idapi.h>

int main()

{

hDBIDb hDb;

hDBIStmt hStmt;

char *pszQuery = "insert into blobs values (?, ?)";

FLDDesc params[2];

BLOBParamDesc blob;

BYTE pParamBuf[10 + sizeof (BLOBParamDesc)+ 2];

pBYTE pBlobBuf;

pBYTE p = NULL;

DBIResult rc;

int fHandle;

int size;

FILE *infile;

// get the file size.

fHandle = open("c:\\temp\\cs1.gif", O_BINARY);

assert (fHandle);

size = filelength(fHandle);

close(fHandle);

pBlobBuf = (pBYTE)malloc(size);

assert(pBlobBuf);

memset(pBlobBuf, 0x0, size);

// Read the blob

infile = fopen("c:\\temp\\cs1.gif", "r");

assert(infile);

rc = fread (pBlobBuf, size, 1, infile);

fclose(infile);

rc = DbiInit(NULL);

if (rc == DBIERR_NONE)

rc = DbiOpenDatabase("sybase_database", NULL, dbiREADWRITE,

dbiOPENSHARED, "sybpass", NULL, NULL, NULL, &hDb);

if (rc == DBIERR_NONE)

rc = DbiQAlloc(hDb, qrylangSQL, &hStmt);

if (rc == DBIERR_NONE)

rc = DbiQPrepare(hStmt, pszQuery);

if (rc == DBIERR_NONE)

{

memset (&params, 0x0, sizeof(params));

memset (&blob, 0x0, sizeof(blob));

memset (pParamBuf, 0x0, sizeof (pParamBuf));

params[0].iFldNum = 1;

params[0].iFldType = fldFLOAT;

params[0].iOffset = 0;

params[0].iLen = 8;

params[0].iNullOffset = 8;

params[1].iFldNum = 2;

params[1].iFldType = fldBLOB;

params[1].iSubType = fldstBINARY;

params[1].iOffset = 10;

params[1].iLen = sizeof(BLOBParamDesc);

params[1].iNullOffset = params[1].iOffset + sizeof(BLOBParamDesc);

params[1].iUnits1 = 0;

params[1].iUnits2 = 0;

p = pParamBuf;

*(pDFLOAT)p = 7;

p = pParamBuf+params[0].iNullOffset;

*(pINT16)p = 8;

blob.pBlobBuffer = pBlobBuf;

blob.ulBlobLen = size;

p = pParamBuf+params[1].iOffset;

*(pBLOBParamDesc)p = blob;

p = pParamBuf+params[1].iNullOffset;

*(pINT16)p = 1;

rc = DbiQSetParams(hStmt, 2,params, pParamBuf);

}

if (rc == DBIERR_NONE)

rc = DbiQExec(hStmt, NULL);

rc = DbiQFree(&hStmt);

rc = DbiCloseDatabase(&hDb);

rc = DbiExit();

if (pBlobBuf)

free(pBlobBuf);

return (rc);

}

Inserts a long character string into an Informix table

#include <memory.h>

#include <malloc.h>

#include <string.h>

#include <idapi.h>

int main()

{

hDBIDb hDb;

hDBIStmt hStmt;

char *pszQuery = "insert into longstrings values (?, ?)";

FLDDesc params[2];

BLOBParamDesc blob;

BYTE pParamBuf[10 + sizeof (BLOBParamDesc)+ 2];

BYTE pBlobBuf[32511];

pBYTE p = NULL;

DBIResult rc;

rc = DbiInit(NULL);

if (rc == DBIERR_NONE)

rc = DbiOpenDatabase("informix_database", NULL, dbiREADWRITE,

dbiOPENSHARED, "infpass", NULL, NULL, NULL, &hDb);

if (rc == DBIERR_NONE)

rc = DbiQAlloc(hDb, qrylangSQL, &hStmt);

if (rc == DBIERR_NONE)

rc = DbiQPrepare(hStmt, pszQuery);

if (rc == DBIERR_NONE)

{

memset (&params, 0x0, sizeof(params));

memset (&blob, 0x0, sizeof(blob));

memset (pParamBuf, 0x0, sizeof (pParamBuf));

memset (pBlobBuf, 0x0, 32511);

params[0].iFldNum = 1;

params[0].iFldType = fldFLOAT;

params[0].iOffset = 0;

params[0].iLen = 8;

params[0].iNullOffset = 8;

params[1].iFldNum = 2;

params[1].iFldType = fldZSTRING;

params[1].iSubType = 0;

params[1].iOffset = 10;

params[1].iLen = sizeof(BLOBParamDesc);

params[1].iNullOffset = params[1].iOffset + sizeof(BLOBParamDesc);

params[1].iUnits1 = 32511;

params[1].iUnits2 = 0;

p = pParamBuf;

*(pDFLOAT)p = 2;

p = pParamBuf+params[0].iNullOffset;

*(pINT16)p = 8;

blob.pBlobBuffer = pBlobBuf;

blob.ulBlobLen = 0;

p = pParamBuf+params[1].iOffset;

*(pBLOBParamDesc)p = blob;

p = pParamBuf+params[1].iNullOffset;

*(pINT16)p = 1;

p = pBlobBuf;

for ( int i = 0; i < 32511; i++)

*(p+i) = '3';

rc = DbiQSetParams(hStmt, 2,params, pParamBuf);

}

if (rc == DBIERR_NONE)

rc = DbiQExec(hStmt, NULL);

rc = DbiQFree(&hStmt);

rc = DbiCloseDatabase(&hDb);

rc = DbiExit();

return (rc);

}