Escape Characters in Strings

#11

Erland Sommarskog wrote:

Quote:

Bob Barrows (reb01501 (AT) NOSPAMyahoo (DOT) com) writes:
Very bizarre. Why not use ADO's innate ability to pass parameter
values? I see from you're next message that you are using vbscript.
So, assuming the procedure above returns no records, and that your
opened connection variable is called "cn", the vbscript to execute
the above procedure would be:

cn.ExampleProc "abc",1,2,3

Explanation: ADO (2.5 and higher) allows stored procedures to be
treated as if they are methods of the connection object, allowing
the parameter values to passed as if you were plassing arguments to
a builtin method. No need to worry about escaping delimiters, etc.

Egads! I didn't know of that one. Unfortunately, I don't have an VB
environment here at home, so I cannot try it.

But how does it work under the covers? I would guess that it runs
.Refresh under the covers, but I found in the MDAC Books Online that
it says: "ADO will make a 'best guess' of parameter types.".

I don't believe it runs Refresh, I've never seen any evidence of it when
using Profiler.
Under the covers, a command object is created (similarly as to when the
connection's Execute method is called) and parameter objects are appended,
typed based on that "best guess", which uses the types (or subtypes given
vbscript's restriction to Variant datatypes) of the passed values in making
that guess. Yes, theoretically, the best guess could be wrong, but in my
experience it never has been - at worst, an implicit conversion might be
required. Of course, my environment has always been very simple. And as long
as you properly type your VB objects, the chance of a bad guess that
requires more than a conversion from varchar to nvarchar, or float to
integer is slim.

#12

Bob Barrows (reb01501 (AT) NOSPAMyahoo (DOT) com) writes:

Quote:

Under the covers, a command object is created (similarly as to when the
connection's Execute method is called) and parameter objects are
appended, typed based on that "best guess", which uses the types (or
subtypes given vbscript's restriction to Variant datatypes) of the
passed values in making that guess. Yes, theoretically, the best guess
could be wrong, but in my experience it never has been - at worst, an
implicit conversion might be required. Of course, my environment has
always been very simple. And as long as you properly type your VB
objects, the chance of a bad guess that requires more than a conversion
from varchar to nvarchar, or float to integer is slim.

The area that would make me the most worried are dates, particularly if you
have them in string fields. If they are passed as (n)varchar, conversion
happens in SQL Server which can give a headache.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#13

Erland Sommarskog wrote:

Quote:

Bob Barrows (reb01501 (AT) NOSPAMyahoo (DOT) com) writes:
Under the covers, a command object is created (similarly as to when
the connection's Execute method is called) and parameter objects are
appended, typed based on that "best guess", which uses the types (or
subtypes given vbscript's restriction to Variant datatypes) of the
passed values in making that guess. Yes, theoretically, the best
guess could be wrong, but in my experience it never has been - at
worst, an implicit conversion might be required. Of course, my
environment has always been very simple. And as long as you properly
type your VB objects, the chance of a bad guess that requires more
than a conversion from varchar to nvarchar, or float to integer is
slim.

The area that would make me the most worried are dates, particularly
if you have them in string fields. If they are passed as (n)varchar,
conversion happens in SQL Server which can give a headache.

Right. They need to be passed as dates, either as the result of using
CDate() or Dateserial() or literal dates (delimited with hash marks in
vb/vbscript).

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Escape Characters in Strings

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Escape Characters in Strings - 08-23-2012 , 05:22 PM

Re: Escape Characters in Strings - 08-24-2012 , 02:40 AM

Re: Escape Characters in Strings - 08-24-2012 , 05:16 AM