SQL Programming
Problem reading column value from a table
Gravatar is a globally recognized avatar based on your email address. Problem reading column value from a table
  Michele
  All
  Mar 11, 2020 @ 02:03am

I'm having problems reading with foxpro data from a SQL Server 2016 table. I can read the values in all fields, but in some characters field i have a space beteween every letter

In SQL Server database management i can see al data normally. For example i have a field called description that contains this string "This string"

When i open this table in foxpro with a remote view this field is a Memo field and the value is "T h i s s t r i n g"

I have already read data from SQL server database and i never had this problem....

Used both SQL Server Native client 11 or the generic SQL server odbc driver

Some suggests

Gravatar is a globally recognized avatar based on your email address. re: Problem reading column value from a table
  Richard Kaye
  Michele
  Mar 11, 2020 @ 10:28am

This is a database encoding issue. Check out the VFP help topic on the STRCONV function.

Gravatar is a globally recognized avatar based on your email address. re: Problem reading column value from a table
  Michele
  Richard Kaye
  Mar 11, 2020 @ 10:43am

Solved with StrConv(StrConv(cString, 6),2)

Thanks

Gravatar is a globally recognized avatar based on your email address. re: Problem reading column value from a table
  Rick Strahl
  Michele
  Mar 11, 2020 @ 12:46pm

What you're seeing is content from a Unicode field. If the data in SQL Server is defined as nchar(x) or nvarchar(x) it'll come back to FoxPro as Unicode (0x00 between characters).

You can STRCONV() but that's a pain in the ass to do. The right way to fix this is to set your connection to support Unicode characters from SQL and auto-convert (both ways).

You can use SYS(987,.T.) to enable the auto-mapping. Only works in VFP 9.

Here's what wwSQL does with this:

************************************************************************
*  EnableUnicodeToAnsiMapping
****************************************
***  Function:
***    Assume:
***      Pass:
***    Return:
************************************************************************
FUNCTION EnableUnicodeToAnsiMapping(llSet)

IF wwVFPVERSION > 8
	IF PCOUNT()=0
		llSet = .T.
	ENDIF

	SYS(987,llSet)
ELSE
	ERROR "Unicode mapping is supported only in VFP 9.0"
ENDIF

ENDFUNC
*   EnableUnicodeToAnsiMapping

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Problem reading column value from a table
  Michele
  Rick Strahl
  Mar 12, 2020 @ 01:22am

Perfect! It works fine.

Thanks to all

© 1996-2020