ODBC: Passing embedded quotes to stored procedures 
Author Message
 ODBC: Passing embedded quotes to stored procedures

I want to pass strings containing embedded single quotes (apostrophes) to a
stored procedure in an SQL Server database using ODBC.

How do I do this?  In an SQL script, you can 'double-up' the embedded quote to
get a single one in the string, but this doesn't seem to work via ODBC.

(By 'doubling up' the quote, I mean using two single-quote characters, not a
double-quote character.)

Example:

Run this SQL script using 'isql' to create and invoke a procedure:

        use dbname

        create proc TestProc(

        )
        as
        begin

        end
        go

        exec TestProc 'Bob''s Diner'
        go

The script produces this output:

 ------------------------------
 Bob's Diner

Note that the embedded quote is 'doubled' in the exec statement.

Now try executing the following SQL from MS Query:

        {Call TestProc('Bob''s Diner')}

The script produces this output:

        Bob''s Diner

The 'doubled up' quote gets passed as two quotes.  If you try just one,

        {Call TestProc('Bob's Diner')}

you get a syntax error (of course).

You can get the same results by passing the SQL "call" statement to
CRecordSet.Open() in C++.

        bResult = CMyRecordSet.Open(
                        CRecordset::forwardOnly,
                         "{Call TestProc('Bob''s Diner')}",
                        CRecordset::readOnly
                        );

Any ideas?

Any help would be greatly appreciated.

Lorcan



Sat, 25 Sep 1999 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. odbc and stored procedures

2. Stored Procedure's Fetch Fails Via ODBC

3. Calling a store procedure with ODBC

4. ODBC + Oracle + MFC + Stored Procedure

5. ODBC Stored Procedure and varchar argument

6. MFC ODBC classes and Stored Procedures

7. HowTo: ODBC and Stored Procedures

8. Empty output parameters when executing stored procedure through ODBC

9. ODBC & Stored Procedures

10. store procedure (ODBC)

11. Stored Procedure's Fetch Fails Via ODBC

12. ODBC and stored procedures

 

 
Powered by phpBB® Forum Software