On 2008.04.17, Rajesh Nair <[EMAIL PROTECTED]> wrote:
> Here are the steps to replicate the problem
This is great, thanks!
> 1. you need a table with some multibyte characters in it.
>
> You can do this either by
>
> 1.1.1 create table multibytetest (value nvarchar(200))
I just came across this Microsoft KB article:
Description of storing UTF-8 data in SQL Server
http://support.microsoft.com/kb/232580
In it, it says:
| Store the actual UTF-8 data on the server using the
| BINARY/VARBINARY/IMAGE columns. Storing UTF-8 data on SQL Server
| means that you can not use SQL Server to sort or find ranges of
| these values as if the data were valid character data. The types
| of operations on columns containing UTF-8 data that would not
| return expected results include "ORDER BY", greater-than ">" and
| less-than "<" comparisons, and the built-in SQL Server string
| manipulation functions such as SUBSTRING().
|
| However, equality comparisons, will work as long as the strings
| being compared are equivalent at a byte level. Note that if you
| store UTF-8 data in SQL Server you should not use character
| columns (CHAR/NCHAR/VARCHAR and so forth). UTF-8 is not valid
| character data to SQL Server, and by storing non-character data in
| character columns you risk encountering problems such as the
| issues discussed in the following Microsoft Knowledge Base
| articles:
Does this have any impact on your test scenario? Should the "value"
column be VARBINARY rather than NVARCHAR?
> 1.1.2 Pasting some Chinese characters via SQL Server Management
> Studio Express
I used this, which I hope is equivalent to your Java program:
INSERT INTO multibytetest (value)
SELECT CHAR(230) + CHAR(144) + CHAR(156) + CHAR(231) + CHAR(139) +
CHAR(144) + CHAR(231) + CHAR(132) + CHAR(166) + CHAR(231) +
CHAR(130) + CHAR(185) + CHAR(229) + CHAR(155) + CHAR(190) +
CHAR(230) + CHAR(150) + CHAR(176) + CHAR(233) + CHAR(151) +
CHAR(187)
Would you mind confirming if this inserts data identical to what your
Java program does? Thanks.
> Expected result is for this adp to print the Chinese characters
> Actual result is it prints ? in place of chinese characters
>
> You can wireshark to sniff the packet between AOLServer and SQLServer
> to see the 2 login requests being made with the 2nd request not
> setting the COLLATION header. I suspect it to be the culprit.
Okay, I think I've got everything set up such that I can reproduce this
(if you confirm that my INSERT statement above inserts the same data as
your test).
Sniffing the network, even before looking at the multiple login request
issue, I can see that the TDS response coming back from SQL Server
contains the correct bytes (0xe6, 0x90, 0x9c, 0xe7, 0x8b ...) so the
translation is happening somewhere in the client-side TDS layer.
I think the core of the problem lies with how nsfreetds bubbles up the
data back to AOLserver, see:
335 if (tds_convert(GET_TDS(handle)->tds_ctx, ctype,
336 (const TDS_CHAR *) src, (unsigned) srclen,
337 SYBVARCHAR, &dres) < 0) {
338 continue;
339 }
I had never tested nor needed binary support, so this wasn't an issue
for me. Also, since nsdb returns values in an Ns_Set, and Ns_Set isn't
binary-safe (it relies on NULL-terminated strings!) ... I took the
"safe" path of ignoring binary support, anyhow.
Actually, digging further, it appears that when connecting to SQL Server
with the TDS 4.2 protocol, it ignores the client character set and
the server picks "iso_1" ... I forced use of TDS 8.0 and now the client
charset is being honored.
So, I'm making progress ... but, I'm not sure what the desired output is
supposed to look like. Can you take a screenshot of the glyphs rendered
correctly and email it to me?
As a test, I went to www.google.cn (Google China) and their page is
returned with charset=GB2312. I took the link at the bottom of the
page, the "About Google" link, and used that as my test data:
INSERT INTO multibytetest (value)
SELECT 'Google ' +
CHAR(180) + CHAR(243) + CHAR(200) + CHAR(171)
I'm inserting the GB2312-encoded data into SQL Server. I told AOLserver
that I'm emitting GB2312 data:
ns_section "ns/mimetypes"
ns_param .adp "text/html; charset=GB2312"
In my ADP, I do this:
ns_adp_puts ... [encoding convertfrom gb2312 [ns_set value $row $i]]
This is because I know the data coming back from the database are
bytes which represent characters that are GB2312-encoded.
After forcing the TDS 8.0 protocol, it all seems to work as expected.
Here are screenshots, the first is of the links from google.cn where I
took the test data from:
http://dossy.org/images/google-cn-links.png
The second screenshot is how my test ADP page rendered the data as
returned from the database via nsfreetds:
http://dossy.org/images/aolserver-nsfreetds-gb2312.png
A match! So, it appears that I can get binary data back from SQL Server
through nsfreetds, now.
I'm attaching a diff that contains all the changes I've made to
nsfreetds.c so far. You can apply it and test if you'd like. There's
still a crash bug in DbClose(), so don't be surprised if you run into
that. However, I'd like to know if, after applying the patch, you can
get binary data back from SQL Server or not.
--
Dossy Shiobara | [EMAIL PROTECTED] | http://dossy.org/
Panoptic Computer Network | http://panoptic.com/
"He realized the fastest way to change is to laugh at your own
folly -- then you can let go and quickly move on." (p. 70)
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
Index: nsfreetds.c
===================================================================
RCS file: /cvsroot/aolserver/nsfreetds/nsfreetds.c,v
retrieving revision 1.7
diff -u -r1.7 nsfreetds.c
--- nsfreetds.c 27 Aug 2004 19:39:44 -0000 1.7
+++ nsfreetds.c 17 Apr 2008 17:29:31 -0000
@@ -59,7 +59,7 @@
#include <langinfo.h>
#define GET_TDS(handle) ((TDSSOCKET *) handle->connection)
-#define GET_TDS_RESULTS(handle) ((TDSRESULTINFO *) GET_TDS(handle)->res_info)
+#define GET_TDS_RESULTS(handle) ((TDSRESULTINFO *)
GET_TDS(handle)->current_results)
/* Common system headers */
@@ -91,8 +91,8 @@
static Ns_TclInterpInitProc AddCmds;
long Ns_FreeTDS_Rows_Affected(Ns_DbHandle *handle);
-int Ns_FreeTDS_Msg_Handler(TDSCONTEXT *ctx, TDSSOCKET *tds, TDSMSGINFO *msg);
-int Ns_FreeTDS_Err_Handler(TDSCONTEXT *ctx, TDSSOCKET *tds, TDSMSGINFO *msg);
+int Ns_FreeTDS_Msg_Handler(TDSCONTEXT *ctx, TDSSOCKET *tds, TDSMESSAGE *msg);
+int Ns_FreeTDS_Err_Handler(TDSCONTEXT *ctx, TDSSOCKET *tds, TDSMESSAGE *msg);
static Ns_DbProc dbProcs[] = {
{ DbFn_ServerInit, DbServerInit },
@@ -157,7 +157,7 @@
TDSLOGIN *login;
TDSCONTEXT *context;
TDSSOCKET *tds;
- TDSCONNECTINFO *connect_info;
+ TDSCONNECTION *connect_info;
const char *locale = NULL;
char *charset = NULL;
void *ctxArray[2];
@@ -177,7 +177,7 @@
return NS_ERROR;
}
- context = tds_alloc_context();
+ context = tds_alloc_context(NULL);
if (!context) {
Ns_Log(Notice, "nsfreetds: DbOpen(%s): tds_alloc_context() failed.",
handle->datasource);
@@ -202,6 +202,7 @@
charset = "iso_1";
}
+ tds_set_version(login, 8, 0); /* FIXME: Use ns_param tdsversion */
tds_set_app(login, DbName());
tds_set_library(login, "TDS-Library");
@@ -210,7 +211,7 @@
tds_set_passwd(login, handle->password);
tds_set_host(login, "myhost"); /* FIXME: aolserver instance name? */
- tds_set_charset(login, charset);
+ tds_set_client_charset(login, charset);
tds_set_language(login, "us_english");
tds_set_packet(login, 512);
@@ -226,12 +227,12 @@
connect_info = tds_read_config_info(NULL, login, context->locale);
if (!connect_info || tds_connect(tds, connect_info) == TDS_FAIL) {
- tds_free_connect(connect_info);
+ tds_free_connection(connect_info);
Ns_Log(Notice, "nsfreetds: DbOpen(%s): tds_connect() failed.",
handle->datasource);
return NS_ERROR;
}
- tds_free_connect(connect_info);
+ tds_free_connection(connect_info);
ctxArray[0] = login;
ctxArray[1] = context;
@@ -246,16 +247,12 @@
static int
DbClose(Ns_DbHandle *handle)
{
- void **ctxArray;
+ void **ctxArray = (void **) handle->context;
if (handle->verbose) {
Ns_Log(Notice, "nsfreetds: DbClose(%s) called.", handle->datasource);
}
- DbCancel(handle);
-
- ctxArray = (void **) handle->context;
-
tds_free_socket(GET_TDS(handle));
tds_free_login((TDSLOGIN *) ctxArray[0]);
tds_free_context((TDSCONTEXT *) ctxArray[1]);
@@ -272,11 +269,11 @@
int i, numcols;
TDS_INT rowtype;
TDS_INT computeid;
- TDSCOLINFO *col;
+ TDSCOLUMN *col;
int ctype;
CONV_RESULT dres;
- unsigned char *src;
- TDS_INT srclen;
+ unsigned char *src, *dest;
+ TDS_INT srclen, destlen;
if (handle->verbose)
Ns_Log(Notice, "nsfreetds: DbGetRow(%s) called.", handle->datasource);
@@ -304,41 +301,53 @@
goto error;
}
- rc = tds_process_row_tokens(GET_TDS(handle), &rowtype, &computeid);
+ rc = tds_process_tokens(GET_TDS(handle), &rowtype, &computeid,
+ TDS_STOPAT_ROWFMT | TDS_RETURN_DONE | TDS_RETURN_ROW |
+ TDS_RETURN_COMPUTE);
+
if (rc == TDS_FAIL) {
- Ns_Log(Error, "nsfreetds: DbGetRow(%s): tds_process_row_tokens()
returned TDS_FAIL", handle->datasource);
+ Ns_Log(Error, "nsfreetds: DbGetRow(%s): tds_process_row_tokens()
returned TDS_FAIL.", handle->datasource);
goto error;
- } else if (rc != TDS_SUCCEED && rc != TDS_NO_MORE_ROWS) {
- Ns_Log(Error, "nsfreetds: DbGetRow(%s): tds_process_row_tokens()
unexpected return %d", handle->datasource, rc);
+ } else if (rc != TDS_SUCCEED && rc != TDS_NO_MORE_RESULTS) {
+ Ns_Log(Error, "nsfreetds: DbGetRow(%s): tds_process_row_tokens()
unexpected return: %d", handle->datasource, rc);
goto error;
}
-
- if (rc == TDS_NO_MORE_ROWS || !GET_TDS(handle)->res_info) {
+ if (rc == TDS_NO_MORE_RESULTS || !GET_TDS_RESULTS(handle) ||
+ (rowtype != TDS_ROW_RESULT && rowtype != TDS_COMPUTE_RESULT)) {
handle->statement = NULL;
handle->fetchingRows = 0;
return NS_END_DATA;
}
-
+
for (i = 0; i < numcols; i++) {
- if (tds_get_null(GET_TDS_RESULTS(handle)->current_row, i)) {
+ col = GET_TDS_RESULTS(handle)->columns[i];
+ if (col->column_cur_size < 0) {
Ns_SetPutValue(row, i, "");
continue;
}
- col = GET_TDS_RESULTS(handle)->columns[i];
ctype = tds_get_conversion_type(col->column_type, col->column_size);
-
src = &(GET_TDS_RESULTS(handle)->current_row[col->column_offset]);
if (is_blob_type(col->column_type))
- src = (unsigned char*) ((TDSBLOBINFO *) src)->textvalue;
+ src = (unsigned char *) ((TDSBLOB *) src)->textvalue;
srclen = col->column_cur_size;
-
- if (tds_convert(GET_TDS(handle)->tds_ctx, ctype, (TDS_CHAR*) src,
(unsigned) srclen, SYBVARCHAR, &dres) < 0) {
+
+ // Ns_Log(Notice, "nsfreetds: DbGetRow(%s): src = {%s}",
handle->datasource, src);
+ if ((destlen = tds_convert(GET_TDS(handle)->tds_ctx, ctype,
+ (const TDS_CHAR *) src, (unsigned) srclen,
+ SYBVARCHAR, &dres)) < 0) {
+ // Ns_Log(Notice, "nsfreetds: DbGetRow(%s):
tds_convert(column_type = 0x%x, ctype = 0x%x) < 0 (%d)", handle->datasource,
col->column_type, ctype, destlen);
continue;
}
- Ns_SetPutValue(row, i, dres.c);
- free(dres.c);
+ dest = (unsigned char *) malloc(destlen + 1);
+ memcpy(dest, src, destlen);
+ dest[destlen] = '\0';
+
+ // Ns_Log(Notice, "nsfreetds: DbGetRow(%s): tds_convert(ctype = 0x%x)
= {%s}", handle->datasource, ctype, dest);
+ Ns_SetPutValue(row, i, (char *) dest);
+ free(dest);
+ // free(dres.c);
}
if (handle->verbose)
@@ -347,6 +356,7 @@
return NS_OK;
}
+
static int
DbFlush(Ns_DbHandle *handle)
{
@@ -378,8 +388,9 @@
static int
DbExec(Ns_DbHandle *handle, char *sql)
{
- int rc, status;
- TDS_INT resulttype;
+ int rc, status;
+ TDS_INT resulttype;
+ TDS_INT computeid;
assert(handle != NULL);
assert(sql != NULL);
@@ -395,8 +406,13 @@
/*
* Flush previous query in case there was one.
*/
+
DbFlush(handle);
+ /*
+ * Execute SQL query.
+ */
+
rc = tds_submit_query(GET_TDS(handle), sql);
if (rc != TDS_SUCCEED) {
Ns_Log(Error, "nsfreetds: DbExec(%s): tds_submit_query failed (%d).",
@@ -405,18 +421,22 @@
}
/*
+ * Process query response.
+ *
* FIXME: There can be multiple result sets. Currently, we
* are fetching only the first.
*/
- rc = tds_process_result_tokens(GET_TDS(handle), &resulttype);
+
+ rc = tds_process_tokens(GET_TDS(handle), &resulttype, &computeid,
+ TDS_TOKEN_RESULTS);
+
if (rc == TDS_FAIL) {
- Ns_Log(Error, "nsfreetds: DbExec(%s): tds_process_result_tokens()
returned TDS_FAIL.", handle->datasource);
+ Ns_Log(Error, "nsfreetds: DbExec(%s): tds_process_tokens() returned
TDS_FAIL.", handle->datasource);
return NS_ERROR;
} else if (rc != TDS_SUCCEED && rc != TDS_NO_MORE_RESULTS) {
- Ns_Log(Error, "nsfreetds: DbExec(%s): tds_process_result_tokens()
unexpected return.", handle->datasource);
+ Ns_Log(Error, "nsfreetds: DbExec(%s): tds_process_tokens() unexpected
return: %d", handle->datasource, rc);
return NS_ERROR;
}
-
if (rc == TDS_NO_MORE_RESULTS) {
handle->statement = NULL;
handle->fetchingRows = 0;
@@ -439,7 +459,7 @@
static Ns_Set *
DbBindRow(Ns_DbHandle *handle)
{
- unsigned int i, numcols;
+ unsigned int i, numcols;
if (handle->verbose)
Ns_Log(Notice, "nsfreetds: DbBindRow(%s) called.", handle->datasource);
@@ -448,11 +468,11 @@
if (handle->verbose)
Ns_Log(Notice, "nsfreetds: DbBindRow(%s, numcols) = %u",
- handle->datasource, numcols);
+ handle->datasource, numcols);
for (i = 0; i < numcols; i++) {
Ns_SetPut((Ns_Set *) handle->row,
- GET_TDS_RESULTS(handle)->columns[i]->column_name, NULL);
+ GET_TDS_RESULTS(handle)->columns[i]->column_name, NULL);
}
return (Ns_Set *) handle->row;
@@ -491,12 +511,12 @@
/* ************************************************************ */
int
-Ns_FreeTDS_Err_Handler(TDSCONTEXT *ctx, TDSSOCKET *tds, TDSMSGINFO *msg)
+Ns_FreeTDS_Err_Handler(TDSCONTEXT *ctx, TDSSOCKET *tds, TDSMESSAGE *msg)
{
Ns_DbHandle *handle = (Ns_DbHandle *) tds->parent;
Ns_Log(Error, "nsfreetds: Ns_FreeTDS_Err_Handler(%s): ERR(%u:%u) %s",
- handle->datasource, msg->msg_number, msg->line_number, msg->message);
+ handle->datasource, NULL, msg->line_number, msg->message);
Ns_DbSetException(handle, "NSDB", msg->message);
@@ -504,7 +524,7 @@
}
int
-Ns_FreeTDS_Msg_Handler(TDSCONTEXT *ctx, TDSSOCKET *tds, TDSMSGINFO *msg)
+Ns_FreeTDS_Msg_Handler(TDSCONTEXT *ctx, TDSSOCKET *tds, TDSMESSAGE *msg)
{
Ns_DbHandle *handle = (Ns_DbHandle *) tds->parent;
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
