Re: Bind variable use in C++ SQL Calls

From: Bjørn Engsig <bjorn_at_miracleas.dk>
Date: Mon, 24 Mar 2003 15:49:45 +0100
Message-Id: <24765.322893@fatcity.com>

--------------000603020409030703080707
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

What is the type of your pDatabase class? If it is an ODBC database container, you should use the bind variable facility of ODBC, where you use a question-mark as your placeholder. Hence, in stead of assembling the SQL statement using printf like formating, you merely put a number of ?'s in there as in

select * from avamas where ava_nodeid = ? and ava_wkday = ? and ...

The ?'s are subsequently bound to program variables using the bind routines of ODBC. (I actaully don't know the ODBC API).

If your pDatabase class is not (derived from) ODBC, I presume it is something of your own, that most likely is implemented using Oracle Call Interface, OCI. If that is the case, you need to augment your pDatabase class with calls to the various bind routines of ODBC, and you need to change your code to use standard style placeholders with : in stead of the ODBC-style ?, as in:

select * from avamas where ava_nodeid = :1 and ava_wkday = :2 and ..

/Bjørn.

Karen Morton wrote:

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

-- 
Bjørn Engsig, Miracle A/S
Member of Oak Table Network <http://www.oaktable.net>
Bjorn.Engsig@MiracleAS.dk - http://MiracleAS.dk


--------------000603020409030703080707
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title></title>
</head>
<body>
What is the type of your pDatabase class? &nbsp;If it is an ODBC database container,
you should use the bind variable facility of ODBC, where you use a question-mark
as your placeholder. &nbsp;Hence, in stead of assembling the SQL statement using
printf like formating, you merely put a number of ?'s in there as in<br>
<br>
&nbsp; select * from avamas where ava_nodeid = ? and ava_wkday = ? and ...<br>
<br>
The ?'s are subsequently bound to program variables using the bind routines
of ODBC. &nbsp;(I actaully don't know the ODBC API).<br>
<br>
If your pDatabase class is not (derived from) ODBC, I presume it is something
of your own, that most likely is implemented using Oracle Call Interface,
OCI. &nbsp;If that is the case, you need to augment your pDatabase class with
calls to the various bind routines of ODBC, and you need to change your code
to use standard style placeholders with : in stead of the ODBC-style ?, as
in:<br>
<br>
&nbsp;&nbsp;&nbsp; select * from avamas where ava_nodeid = :1 and ava_wkday = :2 and ..<br>
<br>
/Bj&oslash;rn.<br>
<br>
<br>
Karen Morton wrote:<br>
<blockquote type="cite"
 cite="midF001.0056F999.20030321101431_at_fatcity.com">
  <pre wrap="">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-&gt;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 (&amp;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-&gt;ExecuteSQL(strSQL);


  </pre>
</blockquote>
<br>
<div class="moz-signature">-- <br>
 Bj&oslash;rn Engsig, Miracle A/S <br>
 Member of Oak Table Network <br>
 <a class="moz-txt-link-abbreviated" href="mailto:Bjorn.Engsig@MiracleAS.dk">Bjorn.Engsig@MiracleAS.dk</a> - <a class="moz-txt-link-freetext" href="http://MiracleAS.dk">http://MiracleAS.dk</a> <br>

Received on Mon Mar 24 2003 - 08:49:45 CST