INSERT with SQLEXEC() problem... 1

[drumsticks]

Hello,

I'm attempting to insert an image from a memo (binary) field to a SQL Server image field using SQLEXEC()

I can't figure out why the record will not insert.
When I take out the binary fields 'itpic' and '?mtpic' from the insert statement, the code works.
I'm sure I'm missing something.

SELECT curNEW
SCAN
SCATTER MEMVAR
*SQLPREPARE(lnConnHandle, 'INSERT INTO testtable ' + ;
'(cfiles, cfolders, itpic) VALUES (?cfiles, ?cfolders, ?mtpic)')

SQLEXEC(lnConnHandle)
ENDSCAN

Regards,
Drumsticks

 

[Mike Lewis]

Drumsticks,

Try changing the data types of the itpic and mtpic fields within your VFP cursor. If they are General fields, change them to memos. It is more reliable to update a SQL Server "binary" field (i.e TEXT or IMAGE) from a memo field.

Also, when faced with this sort of problem, it helps to take out the SQLPREPARE() and replace it with a straight SQLEXEC() -- just pass the connection handle and the SQL statement to be executed. I'm not saying that's the cause of your problem, but it does simplify the code and help focus on the point where it is going wrong.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-consult.com)

 

[drumsticks]

Thank you for your reply,

The fields in the VFP cursor is already memo(binary).

I took out the SQLPREPARE as you suggested and only used the SQLEXEC with just the connhandle and sql statement, however, it still did not insert the data into the SQL server table.

I checked to make sure the SQLEXEC() worked fine by excluding the binary fields from the SQL statement, everything worked fine.

Any more ideas?

Regards,
Drumsticks

 

[Mike Lewis]

Drumstick,

What are the data types of the fields in question in the SQL Server database?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-consult.com)

 

[drumsticks]

image or binary field in SQL Server DB.

I've tried using either field, but receive the identical symptom.

 

[Mike Lewis]

Drumsticks,

Hmm. I can't see any reason for this not to work.

I recently wrote a program which updates an image field from a binary file within VFP. It worked OK, but I did by setting up an updateable remote view into the server table, rather than sending an INSERT. In case you want to try to do the same thing, the steps are as follows:

- Create a remote view into your Testtable table.

- Make it completely updateable.

- If you browse the view, you will see that the binary fields show up as Generals. After closing the view in the view designer, use DBSETPROP() to change the data types of these fields to memo or binary memo.

- Open the view.

- Read the binary data into one of these memo fields (for example, using FILETOSTR() and then REPLACE).

- Either call TABLEUPDATE() to commit the change, or move the record pointer, or close the view. Either way, that will send the update to the server.

That's how I did it, and it worked OK. But it would be better if you could find out why your own approach doesn't work. Sorry I can't throw any more light on that.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-consult.com)

 

[drumsticks]

Mike,

You're a good man. Thank you for your help.
I will try your approach.

I've also done reading on the internet and found that it won't really work the way I'm attempting it. Instead, I've seen an example, one just like your approach, and an example to use ADODB as another approach, as a work-around for this dilemma.

I think VFP9 has resolved this issue by adding an image field, as well as a couple other fields, like varchar, to help mend the SQL passthrough woes. I won't be getting VFP9 for a little while, but at least it is reassuring to know VFP is working hard to be great.

Regards,
Ryan Hinton

 

[Mike Lewis]

Ryan,

Yes, I think your're right about the new data types in VFP 9.0. Varchars certainly help to avoid the problem with padded spaces, and I think blobs should make it easier to update image fields, although I haven't tried that myself yet.

Anyway, I hope you get it working. Let's us know if you have any more questions.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-consult.com)

 

[wgcs]

I noticed that your code is :

SCATTER MEMVAR
SQLPREPARE(lnConnHandle, 'INSERT INTO testtable ' + ;
      '(cfiles, cfolders, itpic) VALUES (?cfiles, ?cfolders, ?mtpic)')

I assume that the current table has fields cfiles, cfolders and mtpic...

I don't remember for general fields, but I know the SCATTER MEMVAR is not doing anything for memo fields... which could add to your problem ( I don't know if the parameter "?cmtpic" will look in the current table for cmtpic.... )

If you want the memo field in memory, you want to "SCATTER MEMVAR MEMO"

- Bill

Get the best answers to your questions -- See FAQ481-4875.