Which one is best for ODBC/MSSQL: pass as ?MyVariable or as the value?

Hi all:

I wonder which one would be faster to execute by SQL Server - especially if
I put this inside a scan-endscan loop:
1)
text to lcSQLCmd textmerge noshow pretext 2
select some_field_1, some_field_2
from a_table
where the_pk = ?lcThePrimaryKey
endtext

=sqlexec ( liSQLConnHandle, lcSQLCmd, [xCursor] )

OR: 2)
text to lcSQLCmd textmerge noshow pretext 2
select some_field_1, some_field_2
from a_table
where the_pk = '<<lcThePrimaryKey>>'
endtext

=sqlexec ( liSQLConnHandle, lcSQLCmd, [xCursor] )

(please notice the difference on where clause).

Looking forward to hearing from all of you. Thanks.

WBR,
Willianto

swdev2
Tue Mar 11 22:47:33 CDT 2008

I thought the question mark was was evaluated inside of the sqlexec line,
prior to it being SENT to sql server.
In all of the sql tracing i've done, I've NEVER seen the question
mark__variableName pair sent OVER to SQL Server.

I would state that the ?eval is done prior to the final 'sending' to sql
server.

hth - Mondo Regards [Bill]

--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

"Willianto" <willianto@deletetelkom.net> wrote in message
news:ueFM3L#gIHA.4692@TK2MSFTNGP05.phx.gbl...
> Hi all:
>
> I wonder which one would be faster to execute by SQL Server - especially
if
> I put this inside a scan-endscan loop:
> 1)
> text to lcSQLCmd textmerge noshow pretext 2
> select some_field_1, some_field_2
> from a_table
> where the_pk = ?lcThePrimaryKey
> endtext
>
> =sqlexec ( liSQLConnHandle, lcSQLCmd, [xCursor] )
>
> OR: 2)
> text to lcSQLCmd textmerge noshow pretext 2
> select some_field_1, some_field_2
> from a_table
> where the_pk = '<<lcThePrimaryKey>>'
> endtext
>
> =sqlexec ( liSQLConnHandle, lcSQLCmd, [xCursor] )
>
> (please notice the difference on where clause).
>
> Looking forward to hearing from all of you. Thanks.
>
> WBR,
> Willianto
>
>

swdev2
Tue Mar 11 22:51:33 CDT 2008

On a totally different tact -

I actually like the textmerge paradigm - I have more options inside of it
and not need to code up (and go blind on debugging) a series of IIF
statements. ..

2nd thing - sorry - i am braindead tonight .

the 2nd method would be faster, as the variable to be evaluated would
actually get evaluated inside the textmerge and not at the sqlexec call.

Regards [Bill]

--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

"Willianto" <willianto@deletetelkom.net> wrote in message
news:ueFM3L#gIHA.4692@TK2MSFTNGP05.phx.gbl...
> Hi all:
>
> I wonder which one would be faster to execute by SQL Server - especially
if
> I put this inside a scan-endscan loop:
> 1)
> text to lcSQLCmd textmerge noshow pretext 2
> select some_field_1, some_field_2
> from a_table
> where the_pk = ?lcThePrimaryKey
> endtext
>
> =sqlexec ( liSQLConnHandle, lcSQLCmd, [xCursor] )
>
> OR: 2)
> text to lcSQLCmd textmerge noshow pretext 2
> select some_field_1, some_field_2
> from a_table
> where the_pk = '<<lcThePrimaryKey>>'
> endtext
>
> =sqlexec ( liSQLConnHandle, lcSQLCmd, [xCursor] )
>
> (please notice the difference on where clause).
>
> Looking forward to hearing from all of you. Thanks.
>
> WBR,
> Willianto
>
>

Willianto
Tue Mar 11 22:41:27 CDT 2008

Hi Bill:

Thanks for the enlightment. Appreciate it very much. Actually I use both
method and -like you- haven't found any problem. It's just feel nice to know
what to consider while deciding what to code.

WBR,
Willianto

Stefan
Wed Mar 12 01:30:54 CDT 2008


"Willianto" <willianto@deletetelkom.net> wrote in message
news:uRDPvC$gIHA.4320@TK2MSFTNGP06.phx.gbl...
> Hi Bill:
>
> Thanks for the enlightment. Appreciate it very much. Actually I use both
> method and -like you- haven't found any problem. It's just feel nice to
> know what to consider while deciding what to code.
Salut Willianto -

I agree with the first Bill statement - in practice there is no difference
in speed whether you use the questionmark syntax or try to put
the actual value into the SPT string directly.
But the former way has the big advantage to do "ODBC mapping" for
you, e.g. replace ' single quotes with an '' escape character or transform
boolean .T. to true or DateTime() to GetDate() etc.


hth
-Stefan



--
|\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....
- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------

Fred
Wed Mar 12 07:32:09 CDT 2008

Actually, the statement that uses the "?" is safer as it won't allow SQL
injection to occur. When the statement is "built-up" using a variable, that
variable's content can be compromised to include additional SQL statements
that may allow things to occur that were not intended.

--
Fred
Microsoft Visual FoxPro MVP


"Willianto" <willianto@deletetelkom.net> wrote in message
news:uRDPvC$gIHA.4320@TK2MSFTNGP06.phx.gbl...
> Hi Bill:
>
> Thanks for the enlightment. Appreciate it very much. Actually I use both
> method and -like you- haven't found any problem. It's just feel nice to
> know what to consider while deciding what to code.
>
> WBR,
> Willianto
>

swdev2
Wed Mar 12 12:42:13 CDT 2008

Heya Mr. T -

SQL Injection with VFP ?
Well, I can see how it could happen. EEK.
Have you run across this?

Mondo Regards [Bill]

--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

"Fred Taylor" <ftaylor@mvps.org!REMOVE> wrote in message
news:e62KczDhIHA.536@TK2MSFTNGP06.phx.gbl...
> Actually, the statement that uses the "?" is safer as it won't allow SQL
> injection to occur. When the statement is "built-up" using a variable,
that
> variable's content can be compromised to include additional SQL statements
> that may allow things to occur that were not intended.
>
> --
> Fred
> Microsoft Visual FoxPro MVP
>
>
> "Willianto" <willianto@deletetelkom.net> wrote in message
> news:uRDPvC$gIHA.4320@TK2MSFTNGP06.phx.gbl...
> > Hi Bill:
> >
> > Thanks for the enlightment. Appreciate it very much. Actually I use both
> > method and -like you- haven't found any problem. It's just feel nice to
> > know what to consider while deciding what to code.
> >
> > WBR,
> > Willianto
> >
>
>

swdev2
Wed Mar 12 12:44:53 CDT 2008

Hullo Mr. W -
Ya - like I said about last night - I was brain dead.

The ? usage maps the data type as well, at the right place, right time,
adding wot it needs.
Mondo Regards [Bill]
--
===================
William Sanders / EFG VFP / mySql / MS-SQL
www.efgroup.net/vfpwebhosting
www.terrafox.net www.viasqlserver.net

"Stefan Wuebbe" <stefan.wuebbe@gmx.de> wrote in message
news:uQtQiqAhIHA.5820@TK2MSFTNGP04.phx.gbl...
>
> "Willianto" <willianto@deletetelkom.net> wrote in message
> news:uRDPvC$gIHA.4320@TK2MSFTNGP06.phx.gbl...
> > Hi Bill:
> >
> > Thanks for the enlightment. Appreciate it very much. Actually I use both
> > method and -like you- haven't found any problem. It's just feel nice to
> > know what to consider while deciding what to code.
> Salut Willianto -
>
> I agree with the first Bill statement - in practice there is no difference
> in speed whether you use the questionmark syntax or try to put
> the actual value into the SPT string directly.
> But the former way has the big advantage to do "ODBC mapping" for
> you, e.g. replace ' single quotes with an '' escape character or transform
> boolean .T. to true or DateTime() to GetDate() etc.
>
>
> hth
> -Stefan
>
>
>
> --
> |\_/| ------ ProLib - programmers liberty -----------------
> (.. ) Our MVPs and MCPs make the Fox run....
> - / See us at www.prolib.de or www.AFPages.de
> -----------------------------------------------------------
>

Fred
Wed Mar 12 20:29:44 CDT 2008

I've never seen it, but I have to code against it happening.

--
Fred
Microsoft Visual FoxPro MVP


"swdev2" <wsanders@dotnetconversions.bob.com> wrote in message
news:u9ts49FhIHA.536@TK2MSFTNGP06.phx.gbl...
> Heya Mr. T -
>
> SQL Injection with VFP ?
> Well, I can see how it could happen. EEK.
> Have you run across this?
>
> Mondo Regards [Bill]
>
> --
> ===================
> William Sanders / EFG VFP / mySql / MS-SQL
> www.efgroup.net/vfpwebhosting
> www.terrafox.net www.viasqlserver.net
>
> "Fred Taylor" <ftaylor@mvps.org!REMOVE> wrote in message
> news:e62KczDhIHA.536@TK2MSFTNGP06.phx.gbl...
>> Actually, the statement that uses the "?" is safer as it won't allow SQL
>> injection to occur. When the statement is "built-up" using a variable,
> that
>> variable's content can be compromised to include additional SQL
>> statements
>> that may allow things to occur that were not intended.
>>
>> --
>> Fred
>> Microsoft Visual FoxPro MVP
>>
>>
>> "Willianto" <willianto@deletetelkom.net> wrote in message
>> news:uRDPvC$gIHA.4320@TK2MSFTNGP06.phx.gbl...
>> > Hi Bill:
>> >
>> > Thanks for the enlightment. Appreciate it very much. Actually I use
>> > both
>> > method and -like you- haven't found any problem. It's just feel nice to
>> > know what to consider while deciding what to code.
>> >
>> > WBR,
>> > Willianto
>> >
>>
>>
>
>

Willianto
Wed Mar 12 22:14:28 CDT 2008

Aha... 'VFP's SQL Injection to SQL RDBMS'... Now I have a new sophisticated
vocabulary :D
Thanks for chime in Fred

WBR,
Willianto
"Fred Taylor" <ftaylor@mvps.org!REMOVE> wrote in message
news:e62KczDhIHA.536@TK2MSFTNGP06.phx.gbl...
> Actually, the statement that uses the "?" is safer as it won't allow SQL
> injection to occur. When the statement is "built-up" using a variable,
> that variable's content can be compromised to include additional SQL
> statements that may allow things to occur that were not intended.
>
> --
> Fred
> Microsoft Visual FoxPro MVP
>
>
> "Willianto" <willianto@deletetelkom.net> wrote in message
> news:uRDPvC$gIHA.4320@TK2MSFTNGP06.phx.gbl...
>> Hi Bill:
>>
>> Thanks for the enlightment. Appreciate it very much. Actually I use both
>> method and -like you- haven't found any problem. It's just feel nice to
>> know what to consider while deciding what to code.
>>
>> WBR,
>> Willianto
>>
>
>