Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Bind variable use in C++ SQL Calls

RE: Bind variable use in C++ SQL Calls

From: Schauss, Peter <pschauss_at_northropgrumman.com>
Date: Fri, 21 Mar 2003 13:19:24 -0800
Message-Id: <24738.322749@fatcity.com>


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-----
From: dgoulet_at_vicr.com [mailto:dgoulet_at_vicr.com] Sent: Friday, March 21, 2003 2:31 PM
To: Multiple recipients of list ORACLE-L Subject: Re:Bind variable use in C++ SQL Calls

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 <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



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: 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
Received on Fri Mar 21 2003 - 15:19:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US