Step 8 - Switching the app to SQL Server Data

So now this little applet is more or less complete, so now lets move the app to SQL Server, shall we? ( you can use MSDE if you don't have SQL Server - MSDE ships with VFP7 and can be downloaded from the Microsoft Web Site)

The first step in this excercise is to upsize the data to SQL Server using the Visual FoxPro Upsizing Wizard. Please see the VFP documentation on upsizing a VFP database to SQL Server for more details - this outline only gives you rough steps.

Start by adding the wwDevRegistry table to a temporary database container (it's a free table)
Make sure you have a SQL Server Connection setup either by creating a Connection in VFP
or by creating an ODBC datasource that you can connect to.
Click on the Tools|Wizards|Upsizing Wizard menu option to start the upsizing Wizard.
Choose the tables to upsize and select any other options that the Wizard pops up.
Create a New database and call it wwDeveloper.
Step through the rest of the Wizard and click Finish to upsize

This has created a new database for you and upsized the single wwdevRegistry table from our installation to the server.

The next step is to set up Web Connection's SQL Server support:

Start the Management Console with DO CONSOLE
Select the Create SQL Server Tables option
Select Add tables and Stored Procedures to existing database and type wwDeveloper for the database name.
Provide a connection string, which defaults to wwDeveloper with username of sa and no password. Change
the string if this is not correct.
Click Finish.

This creates several new tables and a few stored procedures that are used by Web Connection. The wwBusiness specific features handled by this operation are creation of a sp_ww_NewID stored procedure which is used to create PKs for our business objects.

Next we need to add SQL Server support to our business object. To do this:

Start the Management Console with DO CONSOLE
Click on the Create New Business Object
The settings from the previous build operation should still be in place.
Note that your path for the tables is probably .\wwdemo instead of .\wwDevRegistry.
Now set the SQL Server flag and add a connection string.
IMPORTANT: Make sure the generate class is checked - don't worry it won't override your class it'll just update the properties.
Click Go to complete the generation.
Exit the Wizard and MODI CLASS cDeveloper of wwdeveloper if it isn't already popped up.
Notice that the SQL connectstring is filled in. Change the nDataMode property to 2 for SQL Server.

This operation created a wws_id table in your SQL Server database. If you look at the table you'll find a single record in it with wwDevRegistry and an ID number for it set to the next highest number of the PK that will be assigned.

Congratulations you've fully upsized this table and are ready to use your business object. Let's check it out! First though you might want to rename or move your wwDevRegistry table to something else to make sure you notice when you're not accessing the SQL data. The best thing to do is create a small program and type in the following:

DO WCONNECT
SET CLASSLIB TO wwDeveloper addit

oDev = CREATEOBJECT("cDeveloper")
odev.Query

BROWSE

RETURN

Congrats! You've just queried some data from SQL Server. Let's update a single record:

oDev = CREATEOBJECT("cDeveloper")
odev.Query

? oDev.Load(3)
? oDev.oData.Company && West Wind Technologies

oDev.oData.Company = "East Wind Technologies"
oDev.Save()

oDev.Query()
BROWSE

And let's add a new record to make sure the PK generation works too:

oDev = CREATEOBJECT("cDeveloper")
odev.Query

oDev.New()

oDev.oData.Name = "Whil Hentzen"
oDev.oData.Company = "Hentzenwerke"
oDev.oData.City = "Milwaukee"
oDev.oData.Services = REPLICATE("Books are good food!",25)
IF !oDev.Validate()
   ? oDev.cErrorMsg
   RETURN
ENDIF

oDev.Save()

oDev.Query()

BROWSE

RETURN

Try this without the replicate first, then again with it. Now if you do this a few times you'll notice that Hentzenwerke is getting in there again and again - not all that cool. You can add some logic to the Validate() method that deals with this:

*** Check if already existing
IF THIS.nUpdateMode = 2  && Only do this on New entries
   lnResult = THIS.Query("select pk from " + THIS.cFileName + ;
                         " where company='" + THIS.oData.Company + "' AND name='" + THIS.oData.Name + "'")
   IF lnResult > 0
      lcErrors = lcErrors + "This entry exists already. Pk: " + TRANSFORM(pk) + CHR(13)
   ENDIF   
ENDIF

Delete all the new the entries we just created:

oDev = CREATEOBJECT("cDeveloper")
oDev.Execute("delete from " + oDev.cFileName + " where company='Hentzenwerke'")

And then re-run the code we had before. Now only a single entry gets added.

Ok so the business object works. Let's set up our application to use this SQL Server table setup. Just start 'er up and let's go.

How about that? Isn't that pretty slick? The application works as is. If you followed the examples here the code should run without any changes now. I say *should* because SQL compatibility will not usually be this easy because of differring SQL syntax. In this example the queries are kept very simple and use Like instead of = and so on to provide proximity matches etc. In real world applications some SQL Statements will likely require bracketing between Fox and SQL versions. Still keep in mind that all record level operations will be performed automatically for you so they will always work as is without changes. Only SQL statements that use special syntax will require bracketing.

What's bracketing? Inside of the business object you can do things like this

IF THIS.nDataMode = 2 && SQL Server
   RETURN THIS.Query(  ... custom SQL here... )
ELSE
   ... Custom Fox SQL here...
   RETURN _Tally
ENDIF

You can look at the wwBusiness class to get an idea what's involved in bracketing.

Dealing with Connections

I've oversimplified the porting issues too, because in this example here we're using a canned static connection string that's written into the object. This works because it happens to match the database connection settings, but most likely this value will be dynamic. Furthermore, as the app runs now, each time a business object is created a new connection is opened for that object. If you have 10 or 20 business objects running in a single request you're wasting a lot of resources on the SQL connections.

First to specify a connection string manually you'd do:

oDev = CREATEOBJECT("cDeveloper")
oDev.cConnectString = "driver={sql server};server=(local);database=wwdeveloper;uid=sa;pwd=;"
oDev.nDataMode = 2

oDev.Open()  && Open the connection - not required with anyting but Execute()
oDev.Execute(..SQL..)

But to really do this right we don't want to set connection strings each time. We need to use a single connection and share it around. We do this with a wwSQL object. Like this:

oSQL = CREATEOBJECT("wwSQL")
oDev = CREATEOBJECT("cDeveloper")
oSQL.Connect( oDev.cConnectString) 
oDev.SetSQLObject(oSQL)

oDev.Query()
BROWSE NOWAIT

oDev2 = CREATEOBJECT("cDeveloper")
oDev2.SetSqlObject(oSQL)
oDev2.cSQLCursor = "TQuery2"

oDev.Query("select company, name")
BROWSE NOWAIT

Both of these queries are using the same SQL connection contained in the wwSQL object reference.

So, in order to use the SQL application we need a connection and we need to make it permanent in the Web Connection server. To do so let's add it to the WebDemoServer object (as an oDPSQL object) and initialize it in SetServerProperties:

THIS.AddProperty("oDPSQL", CREATEOBJECT("wwSQL"))
IF !THIS.oDPSQL.Connect(THIS.oConfig.oDevProcess.cSQLConnectString)
   MESSAGEBOX("Couldn't connect to SQL Service. Check your SQL Connect string in the INI file.",48,"Web Connection")
   CANCEL
ENDIF

Notice the cSQLString property on the Config object which requires one more change in WebDemoMain on the bottom:

*** Configuration class for the DevProcess Process class
DEFINE CLASS DevProcessConfig as wwConfig

cHTMLPagePath = "D:\WestWind\DevProcess\"
cDATAPath = ""
cSQLConnectString = "{sql server};server=(local);database=wwdeveloper;uid=sa;pwd=;"
ENDDEFINE

Add the connectstring there. It'll be written to the webDemo.ini file where you can change it as needed.

[Devprocess]
Datapath=
Htmlpagepath=D:\WestWind\DevProcess\
Sqlconnectstring=driver={sql server};server=(local);database=wwdeveloper;uid=sa;pwd=;

Ok, that sets up the SQL Connection. Now to use the SQL object in our code. If you want to switch back and forth I suggest you set up a flag for it - WWC_USE_DPSQL and add it to wconnect_override.h like so:

#DEFINE WWC_USE_DPSQL  .T.

Now we need to tell our business objects to use the SQL connection. This means finding all places where the cDeveloper object is used and adding code like this:

loDev = CREATEOBJECT("cDeveloper")
#IF WWC_USE_DPSQL
loDev.SetSQLObject(Server.oDPSql)
#ENDIF

Actually the #IF stuff is optional - if oDPSQL is NULL the connection isn't set and the datamode is not switched to SQL, but if you're not running SQL based there's no need to waste the overhead in the extra method call. So now it's a search and replace operation to find cDeveloper references and add the code above for each. To make sure you catch all of these change the default connection string in the cDeveloper class to an invalid string. If you start now without calling SetSQLObject now a connection dialog will pop up to let you know you have more work to do <bg>...

It's a good idea to plan ahead and put that code in as you build your app right from the beginning. It's much easier at design time than at maintenance time later on as I've done here...