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;
EXEC SQL END DECLARE SECTION;
Now you need to initialize those variables:
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();
EXEC SQL OPEN A1;
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;
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 <[EMAIL PROTECTED]>
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
---------------------------------------
Mystring.Format("SELECT AVA_SERIAL FROM avamas \
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 ();
----------------------------------------------------
CSysNumSet SysnumSet(pDatabase);
SysnumSet.m_TableParam = strFile;
SysnumSet.m_strFilter = "myid = 1234 and yourmom = 'NICE'"
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
