Selecting varbinary(max) and varchar(max) fields with VFP

Question

Selecting varbinary(max) and varchar(max) fields with VFP

iwaldner

SSC Eights!

Points: 805
More actions
March 20, 2007 at 4:03 pm

#171797

I have a test database that contains a varbinary(max) field and a varchar(max) field.
when I do a
Select * from test where id = xx
From Visual FoxPro 9
I get the expected results if my connection string uses
'driver=SQL Server'
but these two fields return no data if I use
'driver=
SQL Native Client'
the other fields in the record come back with no problems.
Is there anything special I need to do to retrieve these types of fields?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

I am assuming it is related to VFP Varbinary is limited to 255 length, so you need BLOB instead. Try the link below for details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ww305zh2(VS.80).aspx

Kind regards,
Gift Peddie

tcorrigan5 Valued Member Points: 65 More actions · Answer 2

My experience is that VFP9 doesn't understand the varchar(MAX) definition. If you specify varchar(8000) instead it will work. Of course, 8000 is a lot less than 2GB so that could be a problem. I don't know of any other workaround.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

I don't think you can use Varchar(8000) to store image file in SQL Server 2005 because it is more ANSI compliant, it will be easier to use VPF blob because most RDBMS use blob and SQL Server all versions from 7.0 understand blob.

Kind regards,
Gift Peddie

foxpro-478195 SSC Rookie Points: 45 More actions · Answer 4

iwaldner (3/20/2007)
I have a test database that contains a varbinary(max) field and a varchar(max) field.
when I do a
Select * from test where id = xx
From Visual FoxPro 9
I get the expected results if my connection string uses
'driver=SQL
but these two fields return no data if I use
'driver=SQLNative Client'
the other fields in the record come back with no problems.

Just ran into the exact same problem and also found only the [Driver={SQL Server} ..] connection to be working. Glad that you posted it here before.

iwaldner SSC Eights! Points: 805 More actions · Answer 5

What finally made it work for me was to cast the varchar(max) field explicitly as a varbinary(max) in the select. Like this:

Select ntpk,

cast(ntNote as varbinary(max)) ntNote

From ...

and then capture it in a memo field at the VFP end.

Ilmar