• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

Full Statements vs. Parameterized Queries

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
Nofx
Asked:
Nofx
1 Solution
 
sachin_nigamCommented:
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
 
andyclapCommented:
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
 
NofxAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
andyclapCommented:
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
 
NofxAuthor Commented:
andyclap :

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

Thanks.
0
 
andyclapCommented:
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
 
DanRollinsCommented:
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
 
Computer101Commented:
Comment from expert accepted as answer

Computer101
E-E Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now