How to pass a single quotation mark in a string to a table in SQL Server by using SQL passthrough

Summary

This article describes how to pass a single quotation mark in a string to a table in Microsoft SQL Server by using SQL passthrough together with a DSNless ODBC connection.

More Information

The following code example assumes that the Pubs database exists in a SQL Server data source that is named SQLSERVER. This example also assumes that the user has the correct permissions, IDs, and passwords.

This example does the following:
  • It creates a DSNless ODBC connection to the SQL Server data source. The connection is named TESTCONNECTION.
  • It creates a table that is named SINGLEQUOTE.
  • It inserts the string text into the table. A single quotation mark is embedded in the string.
*!* Start of the program
SET SAFETY OFF
CLEAR
CLOSE DATA ALL
CREATE DATABASE test

*!* DSNless Connection to a SQL Server database
*!* In the following statement, "DBMSSONCN" is the TCP/IP library name.
CREATE CONNECTION testconnection CONNSTRING ;
"driver={sql server};server=SQLSERVER;uid=UserName;pwd=StrongPassword;
database=pubs;network=dbmssocn"
xDSNLess = SQLCONNECT("testconnection")

IF xDSNLess > 0
*!**CHAR(39) is a single quotation mark or apostrophe in SQL Server.
SQLEXEC(xDSNLess, "DROP TABLE singlequote")
SQLEXEC(xDSNLess, "CREATE TABLE singlequote (singlequote char(30))")
SQLEXEC(xDSNLess, "INSERT INTO singlequote ;
VALUES ('single'+char(39)+'quotes '+char(39)+'in the '+;
char(39)+ 'string')")
SQLEXEC(xDSNLess, "SELECT * from singlequote")
SQLEXEC(xDSNLess, "DROP TABLE singlequote")
BROWSE NOWAIT
ENDIF

SQLDISCONNECT(xDSNLess)
SET SAFETY ON
*!* End of the program
Propriedades

ID do Artigo: 236126 - Última Revisão: 20 de jun de 2008 - Revisão: 1

Comentários