Running T-SQL command in VFP

[dickylam]

Hi,

Please help. I am using VFP as front end and use MS-SQL 2000 as back end database.

Basically by using sqlexecute command provided by VFP I can communicate with MS-SQL successfully.

However, I found that if I use insert, update command, the datatype seems to be character only. THerefore, some functions I can run, they are:-

newid()
datetime()

cast()


Is anybody knowing how to send t-sql command or functions ? Do I need to convert all variables into character type before ?

The value returned by newid() is a uniqueidentifier datatype.

 

[rgbean]

You really shouldn't post a question as a "Helpful Tip".

There are many examples on the MSDN KnowledgeBase (

http://support.microsoft.com/default.aspx).

Choose Visual FoxPro, SQLEXECUTE.

Also check out

http://www.hentzenwerke.com/catalogavailability/csvfp.htm

- "Client-Server Applications w/h Visual FoxPro and SQL Server" - try downloading the sample chapters.

Rick

 

[dickylam]

Rgbean,

Thanks for your advise. But the link you provided does not give much help. Microsoft one only return 3 results and the second one require to purchase the book in order to work details.

In fact, I have question how to use newid() which is the function of t-sql in MS SQL 2000, but sqlexecute do return -1 always. How to do ?

 

[rgbean]

You're right there weren't as many references as I remembered. A better place to start would have been on the VFP site at:

http://msdn.microsoft.com/vfoxpro/technical/articles/clientserver.asp.

I also searched the book, and the only reference to newid() was in the section on the Upsize Wizard - it doesn't handle it right. The advantage of buying the book, is that you can directly email the authors, and they have a lot experience with VFP and SQL Server!

Rick

 

[danceman]

I posted in your other request the answer to newid()

newid()
datetime()
cast()

what you need to do is setup your string inthe sqlexecute to be able to work as if your issued a command from sql server. what I mean is that if you wanted varchar

values ('this is text to insert')

number
values (9)

translate to string for fox to send

somevar = [values ('this is text to insert')]
somevar = [values ('] + foxprocharvar + [')]

somevar = [values (9)]
somevar = [values (] + alltrim(str(foxnumvar)) + [)]

as far as fox is concerned, everything has to be char
the format on the recieving end has to match what it isexpecting. you need to have a manual on the var types in sql so you can format them.
Attitude is Everything