Re: How to lock a record in a table using a query, VB6 and Oracle as a database

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 2 Oct 2006 06:24:06 -0700
Message-ID: <1159795446.306907.271810@h48g2000cwc.googlegroups.com>

Jim Kennedy wrote:
> "Gaz" <gonkowonko_at_gmail.com> wrote in message
> news:1159794025.061126.52960_at_m73g2000cwd.googlegroups.com...
> >
> > > It will stay locked until rollback or commit. Also make sure you use
> bind
> > > variables in your code. Otherwise you will create a very unscalable
> > > solution.
> > > Jim
> >
> >
> > Could you explain how i would use bind variables? i roughly know what
> > they are but dont know how to implement them, do you know of a good
> > site for reading??
> >
> The docs specifically The application developer's guide. I don't know how
> to do it in ado, look under parameterized queries or bind variables or host
> variables. (MS SQL server wants them also so it isn't unusual.)
>
> eg select ... from mytable where col1=:col1_bind_variable instead of
> select ... from mytable where col1='some value';
> Jim

Jim of is course right about using bind variables.

Using bind variables in VB is not exactly a straight forward, logical process, so likely a lot of VB programs do not use bind variables. Here is sample code that I posted once before that shows how to use bind variables using named parameters in VB. The way this is supposed to work, is that you would open a recordset (once when the application starts), and simply feed different bind variable values, followed by an execute when a new set of records is needed.

  Dim db as ADODB.Connection
  Dim cmdSQL As New ADODB.Command
  Dim parSQLParameter As New ADODB.Parameter   Dim snpDataSQL As New ADODB.Recordset
  Dim strSQL as String

  Set db = New ADODB.Connection
  db.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strOracleSID & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;"

  db.ConnectionTimeout = 40
  db.CursorLocation = adUseClient
  db.Open

  strSQL = "SELECT" & vbCrLf
  strSQL = strSQL & "  SQL_TEXT" & vbCrLf
  strSQL = strSQL & "FROM" & vbCrLf
  strSQL = strSQL & "  V$SQLTEXT" & vbCrLf
  strSQL = strSQL & "WHERE" & vbCrLf
  strSQL = strSQL & "  HASH_VALUE= ?" & vbCrLf

  strSQL = strSQL & "ORDER BY" & vbCrLf
  strSQL = strSQL & " PIECE"
  cmdSQL.CommandText = strSQL
  cmdSQL.CommandType = adCmdText

  cmdSQL.Prepared = True
  Set parSQLParameter = cmdSQL.CreateParameter("hash", adDouble, adParamInput, 16, 3969931980#)
  cmdSQL.Parameters.Append parSQLParameter

  cmdSQL.ActiveConnection = dbVMFG
  Set snpDataSQL = cmdSQL.Execute

  'Specify a different hash to retrieve:   cmdSQL("hash") = 3969930000#
  Set snpDataSQL = cmdSQL.Execute
  strSQL = ""
  Do While Not (snpDataSQL.EOF)

      strSQL = strSQL & snpDataSQL("sql_text")
      snpDataSQL.MoveNext

  Loop

  snpDataSQL.Close
  Set snpDataSQL = Nothing

  db.Close
  Set db = Nothing

A Google search should help you find other examples of using bind variables in VB, but the above technique seemed to be the most straight-forward method the last time I checked.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Oct 02 2006 - 08:24:06 CDT