| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re:RE: Bind variable use in C++ SQL Calls
Well now, It's said you learn something new every day. Thanks Peter!!
Dick Goulet
____________________Reply Separator____________________Subject: RE: Bind variable use in C++ SQL Calls Author: "Schauss; Peter" <pschauss_at_northropgrumman.com> Date: 3/21/2003 1:49 PM
There is an easier way to handle character strings in PRO*C:
EXEC SQL BEGIN DECLARE SECTION;
char stime[12];
EXEC SQL VAR stime is STRING(12);
EXEC SQL END DECLARE SECTION;
Now you can handle stime as a standard C, null terminated character
string.
HTH,
Peter Schauss
Northrop Grumman Corporation
-----Original Message-----
Sent: Friday, March 21, 2003 2:31 PM
To: Multiple recipients of list ORACLE-L
Karen,
I'm far from a pro at C++ also, haven't found a good reason to use it
anyway
as anything you want to do in C++ is also just as easily (if not more so) in
plain old C. I characterize C++ as a language for old Cobol programers who
are
sorry the verboseness of that language is dying. Anyway, to make use of
bind
variables which can also be called host variables they need to make a few
small
changes to their code. There's someone else out here who likes using the
OCI
approach to this, I prefer the PRO*C method. I do believe their somewhat
the
same although the code you provided tells me their probably using ODBC and
not a
direct Oracle connection. Now if you can convince them to go directly to
Oracle
and bypass ODBC that will buy them some additional performance as well.
Anyway
I digress. To use the host/bind variable method (in Pro*C):
First you need to declare the host variables:
EXEC SQL BEGIN DECLARE SECTION;
int ava_nodeid = 0;
int ava_wkday = 0;
VARCHAR ava_sdate[12];
VARCHAR ava_stime[9];
VARCHAR ava_serial[21];
char *s = NULL;
ava_nodeid = cAvalObject->cItemSerial;
ava_wkday = m_weekday;
strcpy(ava_sdate.arr, ConvertDateToODBCStr (pDatabase, m_sdatetime))
ava_sdate.len = strlen(ava_sdate.arr);
strcpy(ava_stime.arr, m_schartime);
ava_stime.len = strlen(ava_stime.arr);
Now directly using those variables:
EXEC SQL DECLARE A1 CURSOR FOR
SELECT AVA_SERIAL
FROM AVAMAS
WHERE AVA_TABNAME = 'sys_node'
AND AVA_NODEID = :ava_nodeid
AND AVA_WKDAY = :ava_wkday
AND AVA_SDATE = :ava_sdate
AND AVA_STIME = :ava_stime;
if(sqlca.sqlcode != 0) do_something();
if(sqlca.sqlcode != 0) break;
else ava_serial.arr[ava_serial.len] = '\0';
Whatever_you_have_in_mind();
}while(sqlca.sqlcode == 0);
EXEC SQL CLOSE A1;
Now if you positively can't appreciate the above, try this:
char *stmt = "SELECT AVA_SERIAL FROM avamas \
WHERE ava_tabname = 'sys_node' \
AND ava_nodeid = :1 \
AND ava_wkday = :2 \
AND ava_sdate = :3 \
AND ava_stime = :4 ";
s = stmt;
EXEC SQL PREPARE A1S FROM :s;
if(sqlca.sqlcode != 0) do_something();
EXEC SQL DECLARE A1 CURSOR FOR A1S;
if(sqlca.sqlcode != 0) do_something();
EXEC SQL OPEN A1 USING :ava_nodeid, :ava_wkday, :ava_sdate, :ava_stime;
if(sqlca.sqlcode != 0) do_something();
do
{ EXEC SQL FETCH A1 INTO :ava_serial;
if(sqlca.sqlcode != 0) break;
else ava_serial.arr[ava_serial.len] = '\0';
Whatever_you_have_in_mind();
}while(sqlca.sqlcode == 0);
EXEC SQL CLOSE A1;
Hope this helps!!
Dick Goulet
____________________Reply Separator____________________ Author: Karen Morton <oracledba_at_morton-consulting.com> Date: 3/21/2003 10:14 AM
All,
I've got an application that does not use bind variables. The code is
written
in Microsoft Visual C++. I have no background with C++ and need some help
in
telling the developers how to use bind variables in their code (they don't
know and aren't sure how to find out). I pulled the following examples out
of
the code for different ways they execute SQL. If anyone can assist with
specific examples on how to rewrite this to use bind variables, it would be
immensely helpful.
Thanks,
Karen Morton
Samples
WHERE ava_tabname = 'sys_node' \
AND ava_nodeid = %-d \
AND ava_wkday = %-d \
AND ava_sdate = %s \
AND ava_stime = '%-s'",
cAvalObject->cItemSerial, m_weekday, ConvertDateToODBCStr (pDatabase, m_sdatetime), m_schartime);
rSpanRecord.Open (CRecordset::forwardOnly, cSpanSelect);
if (rSpanRecord.IsEOF () == 0)
{ rSpanRecord.GetFieldValue ("AVA_SERIAL", vCDBVariant);
m_serial = atol (ObjectConvert (&vCDBVariant));
vCDBVariant.Clear ();
}
rSpanRecord.Close ();
SysnumSet.Open();
if (SysnumSet.IsOpen())
lNewSysNo = SysnumSet.m_file_identity; else
lNewSysNo = 0;
strSQL.Format("UPDATE sys_file WITH (ROWLOCK) SET file_identity = file_identity + 1 WHERE file_table = '%s' ", strFile);
pDatabase->ExecuteSQL(strSQL);
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karen Morton INET: oracledba_at_morton-consulting.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: dgoulet_at_vicr.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Schauss, Peter INET: pschauss_at_northropgrumman.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inReceived on Fri Mar 21 2003 - 17:09:55 CST
 |
 |