Using wwBusiness with SQL Server data

wwBusiness supports SQL Server natively for all the implemented base methods, so you can switch data sources on the fly as long as the SQL tables exist.

To set up for SQL Server Operation:

SQL Server Configuration
Make sure you run the CONSOLE and run the Create SQL Server Tables option to create the needed stored procedures.

Actually you just need one (you can run these using the SQL Query Analyzer):

CREATE PROCEDURE sp_ww_NewID
  @cName char(30),
  @nRetval int OUTPUT
AS
UPDATE wws_Id 
  SET id = id + 1,
      @nRetval = id + 1
WHERE TableName = @cName
GO

The wizard will build the wws_id table for you and add the appropriate values to it as needed. Be sure to run the Wizard to handle this for you for each of your business objects. Alternately you can create the table manually and add one record for each business object table. The structure of the table is:

CREATE TABLE [dbo].[wws_id] (
               	[tablename] [char] (20) NOT NULL ,
               	[id] [int] NOT NULL ,
               	[pkfield] [char] (25) NOT NULL ,
               	[pkfieldtyp] [char] (1) NOT NULL ,
               	[pkwidth] [int] NOT NULL
               ) ON [PRIMARY]

Upsizing your data
Hey, that's your responsibilility. Create a SQL Server database first.The FoxPro upsizing Wizard is actually quite good at this or you can of course create your tables on the server manually. Remember that you need to have an integer PK field. For more info on these steps see the SQL Server documentation and the docs for VFP's upsizing Wizard.

Setting up the business object
If you're creating a new business object run the Business Object Wizard to set up the business object.

For the Fox data paths just put in the filename if you won't have fox data files. Check the SQL data option and put in a connection string such as:

driver={sql server};server=(local);database=YourDataBase;uid=sa;pwd=;

The name of the ID table doesn't matter for the SQL backend as it always uses wws_id per the stored procedure.
If you don't have Fox data uncheck the Fox Data box.

When the wizard runs it will create the ID table if one doesn't exist already, create an entry for your table that you are mapping to the business object and set the PK count to the highest value found in that table. It will also set the tablename, connection string and set the datamode to run in SQL Server (2).

Test operation
Assuming your database has data in it you should now be able to run against your SQL data with the following commands:

DO wconnect
oDev = NEWOBJECT("wwDevRegistry","test.vcx")
? oDev.Query()

This should return data from you SQL Server.

Handling SQL access in your Web application
This works and is nice and easy, but I would recommend you use an explicit connection. The code above will set a connection and reset the connection when the object goes out of scope. You can persist a connection by using an explicit oSQL object reference:

oSQL = CREATE("wwSQL")
oSQL.Connect("driver={sql server};server=(local);database=wwDeveloper;uid=sa;pwd=;")

oDev = NEWOBJECT("wwDevRegistry","test.vcx")
oDev.SetSQLObject(oSQL)
? oDev.Query()

oCust = NEWOBJECT("wwCustomer","test.vcx")
oCust.SetSQLObject(oSQL)
? oCust.Query()
? oDev.Query()

In this example both objects share the same connection.

In a Web Connection application you probably want to declare your oSQL member and attach it to the Server object. You can do this in the SetServerProperties method:

*** Web Store - SQL Version
#IF WWSTORE_USE_SQL_TABLES
  THIS.owwStoreSQL = CREATEOBJECT("wwSQL")
  IF !THIS.owwStoreSQL.Connect(THIS.oConfig.owwStore.cSQLConnection)
     THIS.owwStoreSQL = .NULL.
     WAIT WINDOW TIMEOUT 5 "Web Store SQL Connection failed to load!"
  ENDIF
#ENDIF

In your Process methods you can then do:

oCust = NEWOBJECT("wwCustomer","test.vcx")
oCust.SetSQLObject(Server.owwStoreSQL)

The connection remains alive across hits in this scenario, because the server object stays alive indefinitely.


© West Wind Technologies, 1996-2018 • Updated: 09/29/07
Comment or report problem with topic