Visual Foxpro Book Recommendations?

All,

Can anyone recommend a good book for learning VFP 6.0 from scratch I've looked at SPECIAL ADDITION USING VISUAL FOXPRO 6 but it's not really helping me so far. All I am trying to do is create a simple .exe that will read amd update some records from a .dbf and insert those records into a MS SQL table. All the books on Amazon have gotten terrible reviews and MSDN has little to no info on doing this. I don't need any forms or reports as the .exe I want to build would act more like a trigger than anything else. Even a simple tutorial on this would probably suffice if I could find it!

Any info would be appreciated. Thanks!

Answer this question

Visual Foxpro Book Recommendations?

miki lior
Andy,

No, it's not the SQL login. When I run the .exe it opens a Foxpro window with nothing in it, and it closes when the inserts are done. (see attached).

Picture

Thanks!
dustinto
>>So question is, how can Foxpro tell the difference between my phoneCreated which is a .dbf file ( or really a recordset from the queried .dbf) and my phoneInserted which would be the SQL table

It can't. You cannot do this sort of query across platforms.

What you would have to do would be to scan through the result set of the VFP query and then construct an Insert statement for the SQL back end for those records that meet your criteria. Assuming you have a connection handle for the SQL database, this would do it:

SELECT date, last, first, company ;
FROM mydbf ;
WHERE status = "X" ;
INTO CURSOR temp

SELECT temp
GO TOP
lcInsert = "INSERT INTO sqltable (date, last, first, company ) VALUES ("
SCAN
   *** Add the date value as a string...
   lcSql = lcInsert + DTOS( temp.date ) + ","
   *** Add the name fields (watch for embedded ')
   lcSql = lcSql + "'" + temp.last + "',"
   lcSql = lcSql + "'" + temp.first + "',"
   *** And the company - don't forget the parenthesis
   lcSql = lcSql + "'" + temp.company + "')"
   *** Now send the insert
   lnRes = SQLEXEC( lnConnHandle, lcSql )
   IF lnRes < 1
     *** Insert failed!!!
   ENDIF
ENDSCAN
kwing
I think I found it...

http://support.microsoft.com/default.aspx scid=kb;en-us;145845

States I need to add a line to the Config.fpw
SYSMENU = OFF

Will this command work in the main.prg Guess I will try it later today..
Desmond Green
The way FoxPro "tells the difference between a DBF and a SQL Server table" as you put it is that you "talk" to a DBF directly:

Use phoneCreated
SELECT SELECT date, last, first, company ;
FROM phoneCreated ;
WHERE status = 'X' ;
INTO CURSOR PhonesWithX

Now you have a temporary query result set in the alias called PhonesWithX. You can manipulate it just like a DBF. You can BROWSE, edit, replace, whatever.

To "talk" to a remote table e.g. SQL Server using SPT as your example, you use functions like SQLEXEC()

Example:

LOCAL lnHandle, lcSQL, lnResult
lnHandle = SQLSTRINGCONNECT("<connection string here>")
IF lnHandle < 0
   * error
   * use AERRORS() to determine what ODBC information
   * we get on what happenned
   RETURN .F.
ENDIF
lcSQL = "INSERT INTO PhoneInserted (date, last, first, company) " +;
   "VALUES ("+TRANSFORM(PhonesWithX.date))+",'" +;
   PhonesWithX.last + "','" +;
PhonesWithX.first + "','" +;
PhonesWithX..company + "')"
lnResult = SQLEXEC(lnHandle, lcSQL)
IF lnResult < 1
   * error
   * deal with it here
ENDIF

As per using just a main.prg Yes it is fine. Or you instantiate a form from your main.prg. This form asks the questions (input) from the user andhas methods and properties to do the job.

Runtimes Yes, you do need to distribute Runtimes with your EXE. The runtimes depend on yoir VFP version you use.

See for example:
http://fox.wikis.com/wc.dll Wiki~VFP7RuntimeFiles

In VFP 6 or before you use the distribution wizard (in tools menu|Wizards.
In VFP 7 and above you use the provideed Install Shield Express program, a more powerful installer. In either case, the setup created will include the needed runtimes.

HTH
tshad
Also, adding to my previous message, there is a brand new series of downloadable videos Foxcasts (videos and source) by Craig Boyd.

Check it out at:
http://www.sweetpotatosoftware.com/SPSBlog/PermaLink,guid,2baba34c-ebd6-46a3-aabe-35a4d7348014.aspx
Anthony Wong - MSFT
Most good books out there are not for beginners (i.e. they assume you know the basics).

The best source for books is http://www.hentzenwerke.com

If all you are looking for at this point is what you mention in your message, just look at the help file and samples for Client/Server design.

In VFP6 (a very old version) you have two main choices:
- SQL Pass Through (SPT) (ODBC)
- Remote Views (ODBC)

You could also use OleDB and ADO but that may be complicating the issue a little as SPT is the easiest IMO.

For SPT, look at the help file (or in MSDN Library Online) for:

SQLConnect() or SQLStringConnect()
SQLEXEC()
SQLDisconnect()

These functions will allow you to pass a SQL command diretly to the back-end database (e.g. "Insert into", "Select From", etc.)

Remote Views are very similar to Local Views in VFP but pointing to a connection to an external database (e.g. SQL Server) instead fo to a local DBF. Yhe RV is just a SQL Select statenment that creates a VFP Cursor (i.e. a temporary table in memory). The connection information is saved in the VFP DataBase (*.DBC)

To compile an EXE you have to create a VFP Project. Look in the help files for CREATE PROJECT.

If you have specific questions when you get stuck, ask here and we'll try to help you.

Note: In the current version (VFP9 there are more choices, like Cursor Adapter) but that won't work in VFP6.
alexbartell
Alex,

Crystal clear now. I'll play with it tonight and see what I can come up with. Thanks again for your assistance with this. You've probably saved me weeks worth of frustration trying to learn how to do this without any decent literature available to do so. I'll let you know how it turns out.
Scoob
Alex,

Stuck, but much closer...Here is what I have come up with so far....
I've had no problem figuring out how to use the SQLCONNECT and SQLEXEC statements to query the MSSQL tables. Now, I am a lot cloudy on how to create and insert a recordset from the .dbf.

Here's the flow:

1. Query myDbf for records with status = 'X'
If none, then done. If yes,
2. Insert those records to MSSQL table, and
3. Change status of .dbf to status = 'Y'

In SQL, the Query & Insert would like something like this....

INSERT phoneInserted (date, last, first, company)
SELECT date, last, first, company FROM phoneCreated
WHERE status = 'X'"

So question is, how can Foxpro tell the difference between my phoneCreated which is a .dbf file ( or really a recordset from the queried .dbf) and my phoneInserted which would be the SQL table

I found a way to create a .csv or .txt file from the .dbf query and then insert that in to SQL but it seemed like a lot of run-around. Hopefully you have a better suggestion.

Besides that, I also wonder if creating the coding this in my main.prg is good practice Also, will the .exe be portable without a runtime and/or any of the other files except the .dbf I read somewhere that the .exe's require the runtime files..Or maybe I was mistaken.

Thanks!
DRHY
>> First, can the form that pops up when the .exe is run be suppressed

Which form is this The Login Dialog for SQL Server If so you need to set the "DispLogin" property of the connection - See SQLSETPROP() [and use a connection handle of 0 to set the value as default]

However, the fact that the dialog is appearing at all suggests that your login parameters are not correct (at least, not from SQL Server's point of view).

>> Second, can OLEDB be used as the string provider

Yes, but that will return an ADO recordset and since VFP does not handle ADO directly, it is generally less useful. Better to use ODBC and get a native VFP cursor.

>> why I need to use a path to the .dbf considering

The project stores a relative path which merely allows the code to find the table by prepending it to the file name when you call it from the project. It does NOT get compiled into the EXE (that would stop the EXE from working unless you maintained identical relative paths for Development and Runtime). So you need to set the path for the EXE. You don't have specify it in the file name though, just ensure that VFP has a path set that includes the data directory:

SET PATH TO (HOME(0) + ";C:\temp")
marvind
Andy,

This worked like a charm. You and Alex have been VERY helpful. A few small questions:

First, can the form that pops up when the .exe is run be suppressed And Second, can OLEDB be used as the string provider
Last, I am not understanding why I need to use a path to the .dbf considering I have added it to the Data - Free Tables section of the project manager. I would have thought that the compiler would figure that out!

Here is my code...

**********************************************************
Use c:\temp\phone
SELECT date, last, first, company ;
FROM phone ;
WHERE status = 'A' ;
INTO CURSOR PhoneDBF

LOCAL lnHandle, lcSQL, lnResult, lcDSNLess

lcDSNLess="Driver={SQL Server};Server=(local);Database=IB;Uid=sa;Pwd="

*This one below does not work, only pops up the DSN box wanting me to fill in the *info...I've tried it a few different ways but no-go.

*lcDSNLess="Provider=sqloledb;Data Source=(local);Initial Catalog=IB;User Id=sa;Password="

lnHandle = SQLSTRINGCONNECT(lcDSNLess)
IF lnHandle < 0
   * error
   * use AERRORS() to determine what ODBC information
   * we get on what happenned
   RETURN .F.
ENDIF
lcInsert = "INSERT INTO CallLog (date, last, first, company ) VALUES ("
SCAN
   lcSql = lcInsert + "'" + PhoneDBF.date + "',"
   lcSql = lcSql + "'" + PhoneDBF.last + "',"
   lcSql = lcSql + "'" + PhoneDBF.first + "',"
   lcSql = lcSql + "'" + PhoneDBF.company + "')"
   lnResult = SQLEXEC( lnHandle, lcSql )
   IF lnResult < 1
     *** Insert failed!!!
   ENDIF
ENDSCAN

***********************************************************

The rest of the code was fine once I figured out you need to add the SCAN and ENDSCAN lines to loop through the query set. I was little baffled after trying Alex's version that was only adding one record. I think some error handling will be a good addition so I will do some reading up on that.

Anyway, thanks once again. Looking forward to your repsonse.

J.H.
PKlammer
Thanks Alex, I will give it a shot and see what I can find. A connection and a recordset is all I really need. I'll let you know if I have any questions about it after I read up a little...

Cheers!
Visual Foxpro Book Recommendations?

Answer this question