Solved

Full Statements vs. Parameterized Queries

Posted on 2001-06-19
8
358 Views
Last Modified: 2010-08-05
What is the better (FullStatement or Parameterized Queries) way to manipulate queries with ADO ?

I need to know about performance, code-timing, pros & cons, etc...

Can U Help ?

Example :

     TABLE001

     Cd_Campo001      Integer
     Cd_Campo002     SmallInt
     Vl_Campo001     Numeric(15,2)
     Dt_campo001     DateTime
     Ds_Campo001     Varchar(50)


Full Statement :

Dim sql as String

Dim cd_Campo001 as Long
Dim cd_Campo002 as Integer
Dim vl_Campo001 as Double
Dim dt_Campo001 as Date
Dim ds_Campo001 as String


Cd_Campo001 = 1
Cd_Campo002 = 10
Vl_campo001 = 2134.23
Dt_Campo001 = #15/06/1975#
Ds_Campo001 = "Caixa D?agua"


Sql =   "Insert into TABELA001 (" _
     & "  cd_Campo001, cd_Campo002, " _
     & "  vl_Campo001, dt_Campo001, " _
     & "  ds_Campo001)" _
     & "Values (" _
     & cd_Campo001 & "," & cd_Campo002 & "," _
     & replace(vl_Campo001, ",", ".") & ",?" _
     & format(dt_Campo001,?mm/dd/yyyy?) & "?,?" _
     & replace(ds_Campo001,"?"," ") & "?)"

.
.
.



Parameterized :



Dim sql as String

Dim cd_Campo001 as Long
Dim cd_Campo002 as Integer
Dim vl_Campo001 as Double
Dim dt_Campo001 as Date
Dim ds_Campo001 as String


Cd_Campo001 = 1
Cd_Campo002 = 10
Vl_campo001 = 2134.23
Dt_Campo001 = #15/06/1975#
Ds_Campo001 = "Caixa D?agua"

Sql =   "Insert into TABELA001 (" _
     & "  cd_Campo001, cd_Campo002, " _
     & "  vl_Campo001, dt_Campo001, " _
     & "  ds_Campo001)" _
     & "Values (?, ?, ?, ?, ?)".

    With cmd
        .Parameters.Append .CreateParameter("cd_Campo001", adInteger, adParamInput, , cd_Campo001)
        .Parameters.Append .CreateParameter("cd_Campo002", adSmallInt, adParamInput, , cd_Campo002)
        .Parameters.Append .CreateParameter("vl_Campo001", adDouble, adParamInput, , vl_Campo001)
        .Parameters.Append .CreateParameter("dt_Campo001", adDBTimeStamp, adParamInput, , dt_Campo001)
        .Parameters.Append .CreateParameter("ds_Campo001", adVarchar, adParamInput, 50 , cd_Campo001)

end With

.
.
.




When I call a Parameterized query into a transaction in the Sybase (11) a get the error below :

'Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.'
!!!!!

My Code :

    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
   
    Set cnn = New ADODB.Connection
       
    cnn.Open strConn

    cnn.BeginTrans
   
    Set cmd = New ADODB.Command
    With cmd
        .CommandType = adCmdStoredProc
        .CommandText = "PR_FASE"
        .Parameters.Append cmd.CreateParameter("cd_desc_fase", adInteger, adParamInput, , 82)
        .Parameters.Append cmd.CreateParameter("ds_fase", adVarChar, adParamInput, 50, "TEST XXXXXXX")
        .ActiveConnection = cnn
       
        .Execute , , adExecuteNoRecords
   End With
       
    cnn.CommitTrans
    cnn.Close
    Set cmd = Nothing
    Set cnn = Nothing




the same code run without 'BeginTrans' and 'CommiTrans', can U help ?
0
Comment
Question by:Nofx
8 Comments
 

Expert Comment

by:sachin_nigam
ID: 6209183
Parametrised queries are lot faster than OTF Sql queries if you run the same query more than once becoz' a parametrized query is compiled only the first time you run it...where as OTF Sql queries are compiled every time you run them
cheers
Sachin
0
 
LVL 6

Accepted Solution

by:
andyclap earned 200 total points
ID: 6209805
It seems Sybase's ODBC driver doesn't handle parameters from ADO properly for some reason.
What I've used (on sybase) is to use stored procedures created on the server,
eg

create procedure pInsertTABELA001
(
  @cd_Campo001 integer,
  @cd_Campo002 integer,
  @vl_Campo001 float,
  @dt_Campo001 timestamp,
  @ds_Campo001 varchar(50)
) as
begin
  Insert into TABELA001(
    cd_Campo001,
    cd_Campo002,
    vl_Campo001,
    dt_Campo001,
    ds_Campo001)
  values(
    @cd_Campo001,
    @cd_Campo002,
    @vl_Campo001,
    @dt_Campo001,
    @ds_Campo001)
end



from VB you use
sSQL= "exec pInsertTABELA001("
sSQL=sSQL & cd_Campo001 & ","
sSQL=sSQL & cd_Campo002 & ","
sSQL=SSQL & vl_Campo001 & ","
sSQL=sSQL & "'" & format$(dt_Campo001,"yyyy/mm/dd hh:nn:ss") & "',"
sSQL=sSQL & "'" & dsCampo001 & "'"
sSQL=sSQL & ")"

con.execute sSQL,,adExecuteNoRecords



Note if you're going to be using this technique often, you're best of writing specific conversion helper functions for each datatype which can take care of doubling up quotes in strings etc.

0
 

Author Comment

by:Nofx
ID: 6210599
Thanx guys.

sachin_nigam :
Can you indicate any document (url) that says about it.

andyclap :
My code run without transaction, look :

The proc :

CREATE PROC PR_FASE
(@cd_desc_fase Int,
@desc_fase varchar(50)
)
as
Begin
insert into tbl_desc_fases
values (@cd_desc_fase,@desc_fase)
end


if I use the code below, works fine :



   Dim cnn As ADODB.Connection
   Dim cmd As ADODB.Command
   
   Set cnn = New ADODB.Connection
       
   cnn.Open strConn

   Set cmd = New ADODB.Command
   With cmd
       .CommandType = adCmdStoredProc
       .CommandText = "PR_FASE"
       .Parameters.Append cmd.CreateParameter("cd_desc_fase", adInteger, adParamInput, , 82)
       .Parameters.Append cmd.CreateParameter("ds_fase", adVarChar, adParamInput, 50, "TEST XXXXXXX")
       .ActiveConnection = cnn
       
       .Execute , , adExecuteNoRecords
  End With
     
   cnn.Close
   Set cmd = Nothing
   Set cnn = Nothing





If I use the below, doesn't works :



   Dim cnn As ADODB.Connection
   Dim cmd As ADODB.Command
   
   Set cnn = New ADODB.Connection
       
   cnn.Open strConn

   cnn.BeginTrans
   
   Set cmd = New ADODB.Command
   With cmd
       .CommandType = adCmdStoredProc
       .CommandText = "PR_FASE"
       .Parameters.Append cmd.CreateParameter("cd_desc_fase", adInteger, adParamInput, , 82)
       .Parameters.Append cmd.CreateParameter("ds_fase", adVarChar, adParamInput, 50, "TEST XXXXXXX")
       .ActiveConnection = cnn
       
       .Execute , , adExecuteNoRecords
  End With
     
   cnn.CommitTrans
   cnn.Close
   Set cmd = Nothing
   Set cnn = Nothing



Any Idea ?
Thanks.
0
 
LVL 6

Expert Comment

by:andyclap
ID: 6210630
I'm not sure of the problem you're having, but try:
using the command "exec PR_FALSE"
or not using a command object to execute it, just do it directly with SQL, eg

dim sSQL as string
sSQL="exec PR_FALSE 82,'TEST XXXX'"
cnn.execute sSQL
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Nofx
ID: 6211307
andyclap :

I need to execute Parameterized Commands and the 'ADODB.Connection' doesn't support this feature.

Thanks.
0
 
LVL 6

Expert Comment

by:andyclap
ID: 6211667
Unless you're using "output" parameters (which plain & simple don't work with Syabse, or at least didn't when I tried to use them), then there's nothing stopping you parameterising things yourself - just build up the command string converting the parameters to constant representations.

If you really must use output parameters, I have some code I could get hold of that I wrote a few years ago for Sybase which enables you to do this via the OpenClient API. It's very hacky though, and doesn't return recordsets.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7139544
Hi Nofx,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will suggest to:

    Accept andyclap's comment(s) as an answer.

Nofx, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will follow up.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7173290
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now