main() { exec sql include sqlca exec sql begin declare section char prname[20]; char productno[8];exec sql end declare sectionprintf("Input producno:"); scanf("%s",&producno);exec sql select prname from product where productno=:productno into :prname;printf("Productname: %s\n",prname);exit();} 注意sql语句中调用c的变量前要加: 用exec sql来表示将执行sql语句
转贴：int main(int argc, char *argv[]) { LOGINREC* login; // login rec pointer DBPROCESS* dbproc; // SQL Server connection structure pointer char cmd[150]; // command buffer char server[30]; // server name buffer int x = 1; // command line counter STATUS retc; // return code const char* sqlversion; // pointer for version string *server = '\0'; // null start these two buffers *cmd = '\0'; if(argc == 1) // if no server name, request it { printf("Enter Server Name: "); gets(server); } else // else it was input as first arg strcpy(server,argv[1]); if(argc < 2) // if no login id, request it { printf("Enter User Name: "); gets(cmd); } else // otherwise it was input as second arg. strcpy(cmd,argv[2]); // check to see if communications layer was loaded (DOS ONLY) if((sqlversion = dbinit()) == (BYTE *)NULL) { // DOS TSR (DBNMPIPE.EXE) is not loaded, don't bother going any farther printf("Error in DB-Library initialization, exiting\n"); return 1; } else printf("DB-Library version: %s\n",sqlversion); // print dblib version dbsettime(30); // set timeouts to 30 seconds
// set error/msg handlers for this program dbmsghandle((DBMSGHANDLE_PROC)msg_handler); dberrhandle((DBERRHANDLE_PROC)err_handler); login = dblogin(); // get a login rec DBSETLUSER(login,cmd); // set login id DBSETLHOST(login,"SQL EXAMPLE"); // set host name for sp_who DBSETLVERSION(login, DBVER60); // To use secure, or trusted, connection, uncomment the following line. // DBSETLSECURE (login); // open connection to requested server. Pass null server name for local // connection, if name not entered. if((dbproc = dbopen(login,(*server) ? server : (char *)NULL)) == (DBPROCESS *)NULL) { // no one answered, so couldn't connect or error occurred printf("Login failed\n"); return 1; } else { // loop on command input until quit or exit appears in first 4 bytes. while((strnicmp(cmd,"quit",4) != 0) && (strnicmp(cmd,"exit",4)!=0)) { printf("%d> ", x++); // print command prompt gets(cmd); // get command if(strnicmp(cmd,"go",2) == 0) // is it go { if(dbsqlexec(dbproc) == FAIL) // execute command { // problem occurred, just try another command printf("Error in executing command batch!\n"); x = 1; continue; } // command executed correctly, get results information while((retc = dbresults(dbproc)) != NO_MORE_RESULTS) { if (retc == FAIL) // if error get out of loop break; // headers and data could be printed here with only two // function calls, dbprhead(dbproc), and dbprrow(dbproc), // which would output the headers, and all the data to // standard output. However, that isn't very informative // toward understanding how this data is obtained and // processed, so I do it the hard way, one column at a time. PrintHeaders(dbproc); // print header data // loop on each row, until all read while((retc= dbnextrow(dbproc))!=NO_MORE_ROWS) { if(retc == FAIL) // if fail, then clear { // connection completely, just dbcancel(dbproc); // in case. break; } else PrintRow(dbproc); // else print the current row } if (DBCOUNT(dbproc) == 1L) // print the row count printf("(1 row effected)\n"); else printf("(%ld rows effected)\n",DBCOUNT(dbproc)); } // end while(dbresults()) x = 1; // reset command line counter } else { strcat(cmd," "); // go not detected, so put space dbcmd(dbproc,cmd); // between each command and set in } // dbproc. } // end while() dbclose(dbproc); // quit/exit input, close connection // print adios and exit. printf("SQL Server Connection to %s closed, bye bye.\n",server); return 0; } }/* int DetermineRowSize ( DBPROCESS* dbproc, // The SQL Server connection structure int col // column size to get, 0 for all ) { int x,cols; // counters int length=0; // total length of column(row). int namelength; // length of name of column int prlength; // printable length char *name; // pointer to column name
if (!col) // get number of columns cols = dbnumcols(dbproc);
// count from 1 to numcols if col is 0, else x will = col only for(x=((col) ? col : 1);x<=((col) ? col : cols);x++) { switch(dbcoltype(dbproc,x)) // get column type, determine SQLCHAR { // converted length case SQLNUMERIC: case SQLDECIMAL: { DBCOL Col; Col.SizeOfStruct = sizeof(DBCOL); dbcolinfo(dbproc, CI_REGULAR, x, 0, &Col); prlength = Col.Precision + 2; } break; case SQLBIT: // The PR... values are found in the prlength = PRBIT; // SQLDB.H header file. break; case SQLINT1: prlength = PRINT1; break; case SQLINT2: prlength = PRINT2; break; case SQLINT4: prlength = PRINT4; break; case SQLFLT8: prlength = PRFLT8; break; case SQLDATETIME: prlength = PRDATETIME; break; case SQLMONEY: prlength = PRMONEY; break; case SQLVARBINARY: // VARBINARY IMAGE, and BINARY case SQLBINARY: // convert to 2 times length case SQLIMAGE: prlength = dbcollen(dbproc,x)*2; break; default : prlength = dbcollen(dbproc,x); // other types are maximum of break; // actual column length } name = (char*) dbcolname(dbproc, x); // names may be longer than namelength = (name) ? strlen(name) : 0; // column so use name len if if (prlength < namelength) // longer of two. length += namelength + 1; // add one for space between else // columns length += prlength + 1; } return length; // return the length of the field }
/* ** RETCODE PrintHeaders(DBPROCESS *) ** ** This function builds the string that contains the names of each column, ** and a string containing '=' as a separator line. It does this by finding ** the print size of each column, allocating a buffer to hold all column names ** plus one space between each column name, then copying that name into the ** appropriate location into the buffer. Finally the two lines are ** printed. */ RETCODE PrintHeaders ( DBPROCESS *dbproc // The SQL Server connection structure pointer ) { int x,cols,size; // counters char *header; // pointer for separator buffer char *colnames; // pointer for column name buffer char *colname; // scratch pointers char *ptr,*hptr; size = DetermineRowSize(dbproc,0); // get size of buffers ptr = colnames = malloc(size+1); // get name buffer hptr = header = malloc(size+1); // get separator buf memset (header,' ',size); // set buffers to all spaces memset (colnames,' ',size);
cols = dbnumcols(dbproc); // get number of columns for(x = 1; x <= cols; x++) // loop on all columns { size = DetermineRowSize(dbproc,x); // get size of this column colname = (char *)dbcolname(dbproc,x); // get column name strncpy(ptr,colname,strlen(colname)); // copy name memset(hptr,'=',size-1); // set ='s in separator line hptr+=size; // move to next position ptr+=size; // move to next position } *ptr = '\0'; // null term both strings *hptr = '\0'; printf("%s\n",colnames); // print both strings printf("%s\n",header); free(colnames); // free both buffers free(header); return SUCCEED; // done }/* ** RETCODE PrintRow(DBPROCESS *) ** ** This function prints out one row. dbnextrow() must be called to fetch the ** row to print. This routine could be used to print the current row as ** many times as wanted, as the current row data is always available until ** dbnextrow() is called to fetch the next row. This routine works like ** PrintHeaders above, but each column's data is obtained instead of a row ** name, and converted to a string. It is then set into the buffer. */ RETCODE PrintRow ( DBPROCESS *dbproc // SQL Server connection structure ) { int x,cols,size,datasize,colwidth,coltype; // counters char *datavals; // data buffer pointer char *data; // column data pointer char *ptr; // scratch pointer
cols = dbnumcols(dbproc); // get number of columns for(x=1;x<=cols;x++) // do all columns { coltype = dbcoltype(dbproc,x); size = DetermineRowSize(dbproc,x); // determine size of this column memset(ptr,' ',size); // set it to spaces data = (char *)dbdata(dbproc,x); // get pointer to column's data if(data == (BYTE *)NULL) // if NULL, use "NULL" { strncpy(ptr,"NULL",4); // set NULL into buffer ptr += size; // point past this column in output buf } else // else have data, so convert to char { datasize = dbconvert(dbproc,coltype,data,dbdatlen(dbproc,x), SQLCHAR,ptr,(DBINT)size-1); if (datasize < size && (coltype == SQLNUMERIC || coltype == SQLDECIMAL || coltype == SQLINT1 || coltype == SQLINT2 || coltype == SQLINT4 || coltype == SQLFLT8 || coltype == SQLFLT4)) { memmove(ptr+size-1-datasize,ptr,datasize); memset(ptr,' ',size-1-datasize); } ptr += size; } } *ptr = '\0'; // null term string printf("%s\n",datavals); // print row free(datavals); // free buffer return SUCCEED; // done }/* ** msg_handler(char *buffer, long len); ** ** This routine is a local isql message handler call back function that ** is invoked whenever the SQL Server is sending a message back to ** the program. ** */ int msg_handler ( DBPROCESS *dbproc, // SQL Server connection structure DBINT Msg, // SQL Server message number int State, // State of the message int Severity, // Severity of the message char *Message // The message itself (read only) ) { printf("Message No.: %ld, Msg. State: %d, Msg. Severity: %d\n", Msg, State, Severity); if(Message != NULL) printf("%s\n",Message); return (0); }/* ** err_handler(char *buffer, long len); ** ** This routine is a local error handler called by dblib if an internal ** error occurs, also to notify when a server message has been sent, which is ** obtained through the above message handler. ** */ int err_handler ( DBPROCESS *dbproc, // SQL Server connection structure int Severity, // Severity of Dblib error int dberr, // dblib error, all dblib errors start at 10000 int oserr, // OS error from, C runtime char *errstr, // dblib error string char *oserrstr // OS error string (if any) ) { printf("DB-LIBRARY Error - Severity: %d, Error No: %d, OS Error No: %d\n", Severity, dberr, oserr); if(errstr != NULL) printf("%s\n",errstr); if(oserrstr != NULL) printf("%s\n",oserrstr); return INT_CANCEL; }
{
exec sql include sqlca
exec sql begin declare section
char prname[20];
char productno[8];exec sql end declare sectionprintf("Input producno:");
scanf("%s",&producno);exec sql select prname
from product
where productno=:productno
into :prname;printf("Productname: %s\n",prname);exit();} 注意sql语句中调用c的变量前要加:
用exec sql来表示将执行sql语句
{
LOGINREC* login; // login rec pointer
DBPROCESS* dbproc; // SQL Server connection structure pointer char cmd[150]; // command buffer
char server[30]; // server name buffer
int x = 1; // command line counter
STATUS retc; // return code const char* sqlversion; // pointer for version string *server = '\0'; // null start these two buffers
*cmd = '\0'; if(argc == 1) // if no server name, request it
{
printf("Enter Server Name: ");
gets(server);
}
else // else it was input as first arg
strcpy(server,argv[1]); if(argc < 2) // if no login id, request it
{
printf("Enter User Name: ");
gets(cmd);
}
else // otherwise it was input as second arg.
strcpy(cmd,argv[2]); // check to see if communications layer was loaded (DOS ONLY)
if((sqlversion = dbinit()) == (BYTE *)NULL)
{
// DOS TSR (DBNMPIPE.EXE) is not loaded, don't bother going any farther
printf("Error in DB-Library initialization, exiting\n");
return 1;
}
else
printf("DB-Library version: %s\n",sqlversion); // print dblib version dbsettime(30); // set timeouts to 30 seconds
// set error/msg handlers for this program
dbmsghandle((DBMSGHANDLE_PROC)msg_handler);
dberrhandle((DBERRHANDLE_PROC)err_handler); login = dblogin(); // get a login rec DBSETLUSER(login,cmd); // set login id
DBSETLHOST(login,"SQL EXAMPLE"); // set host name for sp_who
DBSETLVERSION(login, DBVER60);
// To use secure, or trusted, connection, uncomment the following line.
// DBSETLSECURE (login); // open connection to requested server. Pass null server name for local
// connection, if name not entered.
if((dbproc = dbopen(login,(*server) ? server : (char *)NULL)) == (DBPROCESS *)NULL)
{
// no one answered, so couldn't connect or error occurred
printf("Login failed\n");
return 1;
}
else
{
// loop on command input until quit or exit appears in first 4 bytes.
while((strnicmp(cmd,"quit",4) != 0) && (strnicmp(cmd,"exit",4)!=0))
{
printf("%d> ", x++); // print command prompt
gets(cmd); // get command
if(strnicmp(cmd,"go",2) == 0) // is it go
{
if(dbsqlexec(dbproc) == FAIL) // execute command
{
// problem occurred, just try another command
printf("Error in executing command batch!\n");
x = 1;
continue;
}
// command executed correctly, get results information
while((retc = dbresults(dbproc)) != NO_MORE_RESULTS)
{
if (retc == FAIL) // if error get out of loop
break; // headers and data could be printed here with only two
// function calls, dbprhead(dbproc), and dbprrow(dbproc),
// which would output the headers, and all the data to
// standard output. However, that isn't very informative
// toward understanding how this data is obtained and
// processed, so I do it the hard way, one column at a time. PrintHeaders(dbproc); // print header data // loop on each row, until all read
while((retc= dbnextrow(dbproc))!=NO_MORE_ROWS)
{
if(retc == FAIL) // if fail, then clear
{ // connection completely, just
dbcancel(dbproc); // in case.
break;
}
else
PrintRow(dbproc); // else print the current row
} if (DBCOUNT(dbproc) == 1L) // print the row count
printf("(1 row effected)\n");
else
printf("(%ld rows effected)\n",DBCOUNT(dbproc)); } // end while(dbresults()) x = 1; // reset command line counter
}
else
{
strcat(cmd," "); // go not detected, so put space
dbcmd(dbproc,cmd); // between each command and set in
} // dbproc. } // end while() dbclose(dbproc); // quit/exit input, close connection // print adios and exit.
printf("SQL Server Connection to %s closed, bye bye.\n",server);
return 0;
}
}/*
int DetermineRowSize
(
DBPROCESS* dbproc, // The SQL Server connection structure
int col // column size to get, 0 for all
)
{
int x,cols; // counters
int length=0; // total length of column(row).
int namelength; // length of name of column
int prlength; // printable length
char *name; // pointer to column name
if (!col) // get number of columns
cols = dbnumcols(dbproc);
// count from 1 to numcols if col is 0, else x will = col only
for(x=((col) ? col : 1);x<=((col) ? col : cols);x++)
{
switch(dbcoltype(dbproc,x)) // get column type, determine SQLCHAR
{ // converted length
case SQLNUMERIC:
case SQLDECIMAL:
{
DBCOL Col; Col.SizeOfStruct = sizeof(DBCOL); dbcolinfo(dbproc, CI_REGULAR, x, 0, &Col);
prlength = Col.Precision + 2;
}
break; case SQLBIT: // The PR... values are found in the
prlength = PRBIT; // SQLDB.H header file.
break;
case SQLINT1:
prlength = PRINT1;
break;
case SQLINT2:
prlength = PRINT2;
break;
case SQLINT4:
prlength = PRINT4;
break;
case SQLFLT8:
prlength = PRFLT8;
break;
case SQLDATETIME:
prlength = PRDATETIME;
break;
case SQLMONEY:
prlength = PRMONEY;
break;
case SQLVARBINARY: // VARBINARY IMAGE, and BINARY
case SQLBINARY: // convert to 2 times length
case SQLIMAGE:
prlength = dbcollen(dbproc,x)*2;
break;
default :
prlength = dbcollen(dbproc,x); // other types are maximum of
break; // actual column length
} name = (char*) dbcolname(dbproc, x); // names may be longer than
namelength = (name) ? strlen(name) : 0; // column so use name len if if (prlength < namelength) // longer of two.
length += namelength + 1; // add one for space between
else // columns
length += prlength + 1;
} return length; // return the length of the field
}
** RETCODE PrintHeaders(DBPROCESS *)
**
** This function builds the string that contains the names of each column,
** and a string containing '=' as a separator line. It does this by finding
** the print size of each column, allocating a buffer to hold all column names
** plus one space between each column name, then copying that name into the
** appropriate location into the buffer. Finally the two lines are
** printed.
*/
RETCODE PrintHeaders
(
DBPROCESS *dbproc // The SQL Server connection structure pointer
)
{
int x,cols,size; // counters
char *header; // pointer for separator buffer
char *colnames; // pointer for column name buffer
char *colname; // scratch pointers
char *ptr,*hptr; size = DetermineRowSize(dbproc,0); // get size of buffers
ptr = colnames = malloc(size+1); // get name buffer
hptr = header = malloc(size+1); // get separator buf
memset (header,' ',size); // set buffers to all spaces
memset (colnames,' ',size);
cols = dbnumcols(dbproc); // get number of columns
for(x = 1; x <= cols; x++) // loop on all columns
{
size = DetermineRowSize(dbproc,x); // get size of this column
colname = (char *)dbcolname(dbproc,x); // get column name
strncpy(ptr,colname,strlen(colname)); // copy name
memset(hptr,'=',size-1); // set ='s in separator line
hptr+=size; // move to next position
ptr+=size; // move to next position
} *ptr = '\0'; // null term both strings
*hptr = '\0';
printf("%s\n",colnames); // print both strings
printf("%s\n",header);
free(colnames); // free both buffers
free(header); return SUCCEED; // done
}/*
** RETCODE PrintRow(DBPROCESS *)
**
** This function prints out one row. dbnextrow() must be called to fetch the
** row to print. This routine could be used to print the current row as
** many times as wanted, as the current row data is always available until
** dbnextrow() is called to fetch the next row. This routine works like
** PrintHeaders above, but each column's data is obtained instead of a row
** name, and converted to a string. It is then set into the buffer.
*/
RETCODE PrintRow
(
DBPROCESS *dbproc // SQL Server connection structure
)
{
int x,cols,size,datasize,colwidth,coltype; // counters
char *datavals; // data buffer pointer
char *data; // column data pointer
char *ptr; // scratch pointer
colwidth = DetermineRowSize(dbproc,0);
ptr = datavals = malloc(colwidth+1); // get buffer
cols = dbnumcols(dbproc); // get number of columns
for(x=1;x<=cols;x++) // do all columns
{
coltype = dbcoltype(dbproc,x);
size = DetermineRowSize(dbproc,x); // determine size of this column
memset(ptr,' ',size); // set it to spaces
data = (char *)dbdata(dbproc,x); // get pointer to column's data
if(data == (BYTE *)NULL) // if NULL, use "NULL"
{
strncpy(ptr,"NULL",4); // set NULL into buffer
ptr += size; // point past this column in output buf
}
else // else have data, so convert to char
{
datasize = dbconvert(dbproc,coltype,data,dbdatlen(dbproc,x),
SQLCHAR,ptr,(DBINT)size-1);
if (datasize < size && (coltype == SQLNUMERIC ||
coltype == SQLDECIMAL || coltype == SQLINT1 ||
coltype == SQLINT2 || coltype == SQLINT4 ||
coltype == SQLFLT8 || coltype == SQLFLT4))
{
memmove(ptr+size-1-datasize,ptr,datasize);
memset(ptr,' ',size-1-datasize);
} ptr += size;
}
} *ptr = '\0'; // null term string
printf("%s\n",datavals); // print row
free(datavals); // free buffer return SUCCEED; // done
}/*
** msg_handler(char *buffer, long len);
**
** This routine is a local isql message handler call back function that
** is invoked whenever the SQL Server is sending a message back to
** the program.
**
*/
int msg_handler
(
DBPROCESS *dbproc, // SQL Server connection structure
DBINT Msg, // SQL Server message number
int State, // State of the message
int Severity, // Severity of the message
char *Message // The message itself (read only)
)
{
printf("Message No.: %ld, Msg. State: %d, Msg. Severity: %d\n",
Msg, State, Severity); if(Message != NULL)
printf("%s\n",Message); return (0);
}/*
** err_handler(char *buffer, long len);
**
** This routine is a local error handler called by dblib if an internal
** error occurs, also to notify when a server message has been sent, which is
** obtained through the above message handler.
**
*/
int err_handler
(
DBPROCESS *dbproc, // SQL Server connection structure
int Severity, // Severity of Dblib error
int dberr, // dblib error, all dblib errors start at 10000
int oserr, // OS error from, C runtime
char *errstr, // dblib error string
char *oserrstr // OS error string (if any)
)
{ printf("DB-LIBRARY Error - Severity: %d, Error No: %d, OS Error No: %d\n",
Severity, dberr, oserr); if(errstr != NULL)
printf("%s\n",errstr);
if(oserrstr != NULL)
printf("%s\n",oserrstr); return INT_CANCEL;
}