[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Full Statements vs. Parameterized Queries

Posted on 2001-06-19
8
Medium Priority
?
372 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 800 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

829 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