Removing unknown / non visible characters from fields 2

[Steve-vfp9user]

Hello

In furtherance to this thread thread184-1724564, I am now able to create the required table.

One small issue I am having with a couple of the memo fields is that when the data is added to the table it is spaced like this (Slightly more as this thread doesn't show with proper spacing):

T H I S I S M Y S E N T E N C E

When I try to manually remove the spaces in a browse window as soon as you click on the text it disappears, albeit for the first character.

So this is what I've tried:
[ul]
[li]I created a secondary field in the table and tried to REPLACE it with the data from the field. That worked but as above, if I tried to remove the spaces, the data was hidden and only showed the first character.[/li]
[li]I also tried STRTRAN command but this had no effect[/li]
[li]As an extreme, I tried COPY TO myfile DELIMITED WITH "" WITH TAB and then imported back into a temporary table but the field data did not change.[/li]
[/ul]

Having researched this forum, I found some code that Mike Lewis had posted so I ran a test:

CLEAR

* TITLEL is the field in my table

lcLowchars = ""
FOR lnI=0 TO 31
   lcLowchars = lcLowchars+CHR(lnI)
ENDFOR

lcLowchars = lcLowchars+CHR(127)

USE MYTABLE
SCAN
   lcStripped = CHRTRAN (TITLEL, lcLowchars, "")
   IF lcStripped <> TITLEL
      WAIT "SPECIAL CHARS YES" WINDOW
   ELSE
      WAIT "SPECIAL CHARS NO" WINDOW
   ENDIF
   IF RECNO()=5
      EXIT
   ENDIF
ENDSCAN

What I did with RECNO() 4 was completely remove the data from the field and add normal text just to prove the point that the first four records contained "unknown" or hidden characters and sure enough, that's what happened.

So having come this far, my question is, if I run the above code, how can I remove any unknown or hidden characters from the text string in the field(s) or is there something I can do perhaps at the time of doing this:

lcConnstring = "Driver={SQL Server Native Client 10.0};Server=.\SQLEXPRESS;Database=OURDATA;Trusted_Connection=yes;"
m.lnConn = SQLStringConnect(lcConnstring)
SQLEXEC(m.lnConn, 'SELECT * FROM abc.ourdata','MYNEWTABLE')
SQLDISCONNECT(m.lnConn)

Thank you

Steve

 

[Olaf Doschke]

Most probably the text is stored as unicode in your sql server database. See sys(987).

http://www.west-wind.com/wconnect/weblog/ShowEntry.blog?id=608

Bye, Olaf.

 

[Steve-vfp9user]

Olaf

Appreciate the link, I'll read up on it.

Thank you

Steve

 

[Steve-vfp9user]

The way I see this Olaf, is that if the command SYS(987,.T.) is issued it takes care of the issue I've described.

I will try this tomorrow and reply with my result.

Thank you

Steve

 

[Olaf Doschke]

If the database is storing unicode,that should help, yes. Typically your SQL Server fields then will be Nchar or Nvarchar field types. There also is a general encoding of databases.

You can find out using Microsoft SQL Management Studio again. Open the table definitions, drill down to the fields and see what field types they are.

Bye, Olaf.

 

[Steve-vfp9user]

Just an update on this thread:

I have added the SYS command as Olaf suggested and this has done the trick.

[b]SYS(987,.T.)[/b]
lcConnstring = "Driver={SQL Server Native Client 10.0};Server=.\SQLEXPRESS;Database=OURDATA;Trusted_Connection=yes;"
m.lnConn = SQLStringConnect(lcConnstring)
SQLEXEC(m.lnConn, 'SELECT * FROM abc.ourdata','MYNEWTABLE')
SQLDISCONNECT(m.lnConn)

I appreciate your post on this thread Olaf.

Thank you

Steve

 

[Mike Lewis]

Steve,

Just to add one small point:

Keep in mind that [tt]SYS(987,.T.)[/tt] is global. You don't have to call it every time you get use SQLEXEC(). Call it once only, at the start of the session, and the setting will remain in force throughout.

If, for any reason, you want to cancel the setting (but I can't think why you should), call it again and pass .F. as the second parameter.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You're right, Mike, but in this code sample it's the full circle of connect, query and disconnect. It actually won't hurt to put it inside a SQL passthrough class for querying with defined settings, no matter what is currently set.

Bye, Olaf.

 

[Steve-vfp9user]

Mike / Olaf

This is part of an automated process where we have created an exe file which is triggered by Windows Scheduler. Therefore the program starts, runs and shuts down.

Good points and advice though.

Thank you

Steve

 

[Mike Lewis]

Yes, I agree that there's no harm in doing it as part of the connect - query - disconnect cycle. And, of course, putting it inside a class helps keep the whole thing self-contained.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads