$testString = "The NULL character should be escaped \0 !";

• The INSERT was successful, or


• The INSERT was successful but the string was truncated at the \0.

quote:

---

ado_mssql error: [102: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'The NULL character should be escaped '.] in EXECUTE("INSERT INTO g2_PluginParameterMap (g_pluginType, g_pluginId, g_itemId, g_parameterName, g_parameterValue) VALUES ('module','unitTestModule',1,'test19476','The NULL character should be escaped !')")

---

$rs = mssql_query("insert into testtbl (col1) values ('$testString')");

quote:

---

Warning: mssql_query(): message: Unclosed quotation mark after the character string 'The NULL character should be escaped '. (severity 15) in C:\MyServer\testMsSqlInsertNull.php on line 24
PHP Warning: mssql_query(): message: Incorrect syntax near 'The NULL character should be escaped '. (severity 15) in C:\MyServer\testMsSqlInsertNull.php on line 24

---

• I have very little control over the Gallery SQL or application logic. My understanding is that this should work as-is.


• The intent of this test script is NOT to insert a string with a null... the intent is to verify that the INSERT works as-is either with- or without truncation of the string.

quote:

---

Originally posted by Srinika

May be something to do with PHP

I tried the following in Query Analyzer and the results did not truncated at \0

Create table #t (aa varchar(10))
insert into #t values('ab \0 !')
Select * from #t
drop table #t


Srinika

---

quote:

---

1> insert into testtbl values ('The NULL character should be escaped ' + char(0) + ' !')
2> go
insert into testtbl values ('The NULL character should be escaped ' + char(0) + ' !')


(1 rows affected)
1> select * from testtbl
2> go
select * from testtbl

col1

----------------------------------------------------------------------------------------------------
The NULL character should be escaped


(1 rows affected)

---

quote:

---

Originally posted by Peso

SQL NULL is not the same as SQL CHAR(0).

Use a replacement function in your php code to convert text CHAR(0) to text 'NULL' before executing the query.


Almost every programming language [by Microsoft] treats CHAR(0) as string terminator.



Peter Larsson
Helsingborg, Sweden

---

quote:

---

Originally posted by Birdman

Are you saying that SQL Server will not accept the backslash syntax "\0" without the CHAR() wrapper?

---

quote:

---

1> -- Concatenated strings
2> insert into testtbl values ('The NULL character should be escaped ' + \0 + ' !')
3> go
insert into testtbl values ('The NULL character should be escaped ' + \0 + ' !')


Msg 235, Level 16, State 1, Server CS288290-A\SQLEXPRESS, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.
1> -- Single quotes
2> insert into testtbl values ('The NULL character should be escaped \0 !')
3> go
insert into testtbl values ('The NULL character should be escaped \0 !')


(1 rows affected)
1> -- Double quotes
2> insert into testtbl values ("The NULL character should be escaped \0 !")
3> go
insert into testtbl values ("The NULL character should be escaped \0 !")


(1 rows affected)
1> select * from testtbl
2> go
select * from testtbl

col1

----------------------------------------------------------------------------------------------------
The NULL character should be escaped \0 !

The NULL character should be escaped \0 !


(2 rows affected)

---

quote:

---

Originally posted by Peso

quote:

---

Originally posted by Birdman

Are you saying that SQL Server will not accept the backslash syntax "\0" without the CHAR() wrapper?

---

No, I don't say that.

SQL Server accepts \0 just fine, but the ODBC SQL driver doesn't. May be that's because the driver is written in C/C++?

Somewhere after the query is sent from your application but before the query reaches SQL Server, the query string is somehow converted.


Peter Larsson
Helsingborg, Sweden

---

quote:

---

Originally posted by jen

do you have a number of user messages incorporated in your apps?

if yes, why not create a table for the messages then call the column values instead of passing them from your apps as strings?

just a suggestion though

--------------------
keeping it simple...

---