Image data from SQL Server to VFP

[wtotten]

I may be required to extract data from a SQL Server database to my Visual Foxpro application. I use VFP 8 and VFP 9. I need some help in how to get pictures stored in a SQL Server table into a VFP table.

Thank you,
Bill

 

[TamarGranor]

Do you know how to get data from a SQL Server database generally or should we start with those basics?

If the basics, check out the SQLStringConnect() and SQLExec() functions.

Tamar

 

[wtotten]

Hi Tamar,

I have a couple of your old books.

The system has SQL Studio on it. I can do a query with it, but other than that I have no experience exporting from SQL Server, and not really any experience with it period. I can write basic SQL statements, but it's all used in VFP. I'll take any help you care to offer.

Bill

 

[Olaf Doschke]

You already had some SQL Server related questions here, and you always executed the queries in the studio only?

Besides the already mentioned SQLSringConnect and SQLExec the connection string itself is coming from

http://www.connectionstrings.com

Three lines of code:

h = SQLStringConnect("Driver={SQL Server};Server=myServer;Database=myDataBase;Trusted_Connection=Yes;") && adjust, eg (local) for the Server, if it's running on your dev computer
? SqlExec(h,"SELECT field FROM table", "myCursor")
SqlDisconnect(h)

After that the query result is in alias "myCursor". You can COPY TO a dbf.

Bye, Olaf.

 

[wtotten]

Olaf,

Would the images, which are stored as binary data in SQL Server, be stored in a General field in "myCursor"?

Bill

 

[Olaf Doschke]

That depends on settings, the driver used, the originating field type (image vs varbinary(max) vs Blob vs whatever else...), your VFP version, and certainly something more I forgot.

Why don't you try and succeed or fail, then come back with results here?

Bye, Olaf.

 

[wtotten]

The problem is that this issue is for a project I am bidding on. I can't "try" it. The customer is looking at buying a VFP app I have written and I would have to convert the data in their existing app to my app. I'm doing my upfront research to determine that I can export all their data.

My app is written in VFP 8 and I also have VFP 9 so I can use either version.

Can someone give me some more definitive instructions on what to do? I could possibly ask the potential client if I can test the export, but I would have to (a) Install VFP on their system, (b) get access to their system, (c) be able to test without looking like I'm clueless (I may have to do this with them watching me).

Bill

 

[Mike Lewis]

Bill, is this conected with your project for the humane society (that you mentioned in thread184-1727688)?

The reason I ask is that I am wondering if you have any control over how the images are stored. Is it possible for the client to store the images in a shared directory, and to store their location in the table? If so, would that solve your problem?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[wtotten]

Mike,

Yes, it is. It's a potential new client. They have a program similar to mine and they want to replace it. They are looking at using mine. The current system uses SQL Server and the animal images are stored in a SQL table. I need to know if I'll be able to export those images and then link them back to my application.

Bill

 

[Mike Lewis]

So, the images are already stored in the table? And you have no control over the way they are stored? Is that right?

If so, a possible approach would be to export the images you require to free-standing files on an ad hoc basis, as and when you need them. And to do that within SQL Server.

So, if your VFP app needs an image for Animal 123, you would get SQL Server to copy the image from Record 123 to a disk file, called, say, 123.jpg. Provided the file is placed in a directory accessible to your VFP app, you can then access it as normal.

So how do you get SQL Server to copy the image to a disk file? There are several possible approaches, including using the BCP utility, or with SQL Server Integration Services. You would write the code to do that in T-SQL, but run it from VFP via SQLEXEC().

I stress that I have never actually done this, so I can't be sure how well it would work. I offer it as something to consider and perhaps to experiment with.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[wtotten]

Mike,

Yes, they are stored in a SQL Server table and I have no control over how they were/are stored. The image, not a link, is in the field.
Bill

 

[Olaf Doschke]

Well, in the end the other app get's the pictures out of SQL Server and displays them. It wouldn't make much sense, if that wouldn't be possible. So it's doable with VFP SQL Passthrough, too. In case of data migration you won't need to do that with VFP, besides.

But still, what to use exactly strongly depends on how the pictures are stored. Since a few years ago there was the image field type, but that has gone and is replaced by Blob or Varbinary(max) or filestream and maybe some more I don't remember right now. So no, there is no general solution I could hand you to be failsafe applicable.

They should be cooperative, if they like you application better, to hand out their data or give you hands on. You don't need to be afraid of failing while they watch. It's impossible to prepara a data migration without hands on the database to migrate, no matter if images are involved or not. Especially since you have your database design and need to transform data, that's never an automatic task anyway. There are data converters, exporter/importers, but based on the thought to have 1:1 the same database schema, same tables, fields, views. It ends with stored procs anyway, as code porting is much harder than matching field types of database serverX to serverY. If they don't understand that, they might underestimate the costs of changing the application.

How large is their database? If you can hire a few studients copying over data from the forms of the old app into yours, that's also a very traditional way of data migration by data typists. That's of course no good idea, once the data has grown to a certain extent.

Bye, Olaf.

 

[wtotten]

Olaf,

Some good ideas and insights.

Thanks,
Bill