SQL Table nvarchar to FoxPro field

[seckner]

Please be patient with the noob.. I'm querying an SQL Server table where a few badly needed fields are coming into FoxPro as memo fields. The SQL field type is nvarchar, size is 255. I've been trying to figure out how to get them from memo to what they are which are file names - 'file_20060522_102827_XQKTyQ_0.resources'

I don't know how to clean it up coming from the SQL Table to my cursor. I tried playing around with the memo field and it seems each line in the memo field is exactly 1 letter (line1=f, line2=i, ect)

I'm SO confused....

 

[Mike Lewis]

Not quite sure I've understood the problem. Are you saying that you are receiving the data a double-byte characters (so that only every second character is visible in VFP)?

If that's right, you need to cast the data into a standard varchar rather than an nvarchar. To do so, modify the command you are sending to SQL Server, something like this:

SELECT CAST(MyFileName AS Varchar(254)) AS MyFileName ....

This should also ensure that the field ends up as a character field in FoxPro, rather than a memo.

If I've misunderstood the question, my apologies.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[seckner]

Thank you! My fault completely for not explaining clearly. I'm still trying to wrap my head around FoxPro's terminology. I'm doing a "Select *" in FoxPro against an SQL table to get it into FoxPro. This works except:

If I open the table on the SQL Server I see that the field named "Entry" has a file name stored in it.

When I open the new, just created FoxPro cursor that the field named "Entry" is now a memo field holding the file name instead of just being the file name.

So, how do I either: 1) get the Entry field into FoxPro as not a memo field OR 2) how do I change the FoxPro memo field to show the file name it's holding.

I hope I did better this time.

 

[JRB-Bldr]

Mike's suggestion above will most likely do the job for you.

But you can always try different queries in SQL Server and check the results before you move those queries into your VFP application.

Try what Mike suggests above.

And/or try something as simple as:

SELECT LEFT(Entry,200) AS Entry 
FROM SQLTable

If either of these results in what you need then attempt the same thing in your VFP code.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Well, you're mixing things, and you don't start with the easiest situation.

1. vfp char fields are limited to 254 chars, you're one off of that, so you get a memo field.

2. a browse in foxpro shows each memo as a field displaying "memo" if empty and "Memo", if filled, clicking on it a popup shows the value. Nevertheless doing ? alias.entry does show the content of the field.

3. you're querying a nvarchar, which is a unicode varchar field in sql server. In vfp there is no unicode string type. vfp displays values in the ANSI codepage 1252 in most cases, your local version might vary depending on the windows language.

Additional the default for querying unicode is not to translate the data, we have SYS(987) for that. Call SYS(987,.T.) before querying the data and you get a memo with an ANSI string.

Bye, Olaf.

 

[Mike Lewis]

Seckner,

So, how do I either: 1) get the Entry field into FoxPro as not a memo field OR 2) how do I change the FoxPro memo field to show the file name it's holding.

I hope I did better this time.

Yes, it is indeed clearer now.

JRB-Bldr and Olaf have given you the answer. To summarise, all you need to do is to ensure that the field is 254 characters or less in the FoxPro table. If it is more than that, VFP will treat is as a memo.

My original suggestion will achieve that, as will JRB's suggestion for using the LEFT() function.

But, in any case, none of this is really necessary. Even if you do have a memo field, you can still use it to get the filename. It's only in the Browse window that you see the "Memo" rather than the actual text. And, as Olaf pointed out, you only need to double-click on it (or, in VFP 9, hover the mouse over it), to see the filename.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[seckner]

I think the greatest thing about this board is two-fold. First, a new-be can come and ask questions without being made to feel, well, like a new-be. I don't have to be 'afraid' of misspeaking. Second, and more important I think, is I don't just get an answer. I get an solution, an explanation of what will work and why so I can learn instead of just copying some random code. Thanks to all of you! You guys and gals are the best!