Start by providing a connection string that lets you connect to the Master or any other existing database of your SQL Server. This is required in order to have a connection to the database and be able to create the new database.
Next specify whether you want to add the tables to an existing database or create a new one.
Once you accept the operations the SQL Server tables will be created for you and the database will be ready for operation.
You now need to manually complete the configuration to make the switch by doing the following:
Tell Web Connection to use SQL Server Store and System files
Add the following into your WCONNECT_OVERRIDE.H file by doing:
MODIFY COMMAND wconnect_override.h
and pasting:
#UNDEFINE WWC_USE_SQL_SYSTEMFILES #DEFINE WWC_USE_SQL_SYSTEMFILES .T. #UNDEFINE WWSTORE_USE_SQL_TABLES #DEFINE WWSTORE_USE_SQL_TABLES .T.
If you only want to use the store files you can leave the first flag set at .F. This will continue to use FoxPro files for the Session and Log files which works fine if you're only running a single server.
Recompile all code
Because you're changing a compiler flag with the block above you have to recompile your code.
COMPILE webstoremain COMPILE wwstore COMPILE classes\*.prg
Now you should be ready to start the store with SQL Server running.
DO WEBSTOREMAIN
Understanding what the Sql Configuration does
The Wizard simply creates or appends to an existing SQL Server database. When the WebStoreMain program was created several bracketed blocks of code were generated that determine at startup whether Fox and SQL data is used.
Specifically, operation of the store is controlled via a compiler flag in the Web application in WCONNECT.H:
#DEFINE WWSTORE_USE_SQL_TABLES .T.
and a Connection string that must be provided in the [wwStore] section of the WebStore.ini (or whatever you named your new Web store project):
[wwstore]
...
Sqlconnection=driver={sql server};server=(local);database=WestWindWebStore;uid=sa;pwd=;
This latter connection string is automatically written into the WebStore.ini file when you created the Web Store database using the same connection you used to connect to the database replacing only the database name.
Note:
You'll probably want to also configure Web Connection to use SQL Server tables for logging and Session operations so that all data is going to a central SQL Server data source. To do this set the WWC_USE_SQL_SYSTEMFILES constant in WCONNECT.H to .T. Also run the CONSOLE and the Create SQL Server Tables link to create the Web Connection system tables.
Setting up the connection in SetServerProperties
The Web Store and Web Connection both use Web Connection's wwSQL class to handle their connections to SQL server. By default both are setup to share the same SQL Connection. This is the default code generated in WebStoreServer::SetServerProperties:
*** Add persistent SQL Server Connection
#IF WWC_USE_SQL_SYSTEMFILES
THIS.AddProperty("oSQL", CREATE("wwSQL"))
IF !THIS.oSQL.Connect(THIS.oConfig.cSQLConnectString)
MESSAGEBOX("Couldn't connect to SQL Service. Check your SQL Connect string in the INI file.",48,"Web Connection")
CANCEL
ENDIF
#ENDIF
*** Web Store Dependencies
#IF WWSTORE_USE_SQL_TABLES
IF !EMPTY(THIS.oConfig.owwStore.cSQLConnection)
THIS.AddProperty("owwStoreSql",null)
IF TYPE("THIS.oSQL") = "O"
*** Share existing master connection if exists
THIS.owwStoreSql = this.oSQL
ELSE
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
*** Set default level to high perf - override where needed
this.owwStoreSql.Execute("SET TRANSACTION LEVEL READ UNCOMITTED")
ENDIF
ENDIF
#ENDIF
Note that this code dynamically adds properties to the server at runtime - oSQL and owwStoreSql which typically point at the same wwSQL object which means these two services share the same SQL connection handle.
How SQL Server integration works
All of the SQL Server integration works through the wwStore business objects. Each object has an oSQL member and a SetSQLObject() method which is used to pass/set the SQL connection of the business object. The business object methods then check the nDataMode property to see which data source is used. Switching between VFP and SQL Server tables is simply a matter of passing a properly loaded wwSQL object to the appropriate object.
It's important to understand that every object creation must pass this connection around or else you will not properly deleegate to the SQL data source - this can be especially tricky to catch, because by default the VFP tables are used and if those are still in place the application may work just fine while reading or writing data from the wrong data source.
Tip:
If you plan on running in SQL Server mode we'd recommend you remove the VFP Web store tables. Should there be a connection or datamode problem the application will fail rather than use the wrong datasource.
In the Web application you'll find code like this:
loItem = Create([WWS_CLASS_ITEM]) #IF WWSTORE_USE_SQL_TABLES loItem.SetSQLObject(Server.owwStoreSQL) #ENDIF
to pass around this reference.
The reference must also be passed to child objects. The best way to do this is to use the CreateChildObject() method which passes all the object's configuration information including the SQL object to the child object:
THIS.oCustomer = THIS.CreateChildObject("cCustomer")If you extend any of the business class methods that use other business class objects always use CreateChildObject(). Alternately you can also create the object and then explicitly call SetSqlObject() afterwards.
Differing VFP and SQL query syntax
Because of the differing syntax of SQL Server and VFP data access you might have to perform special SQL syntax bracketing and you can use the nDataMode property of the business object to do so in your client code. This applies both to explicit SQL statements issued from source code (not recommended) as well as for certain methods that include filter conditions. In these cases you can do the following:
loItem = Create([WWS_CLASS_ITEM])
#IF WWSTORE_USE_SQL_TABLES
loItem.SetSQLObject(Server.owwStoreSQL)
#ENDIF
loItem.cSQLCursor = "TItemList"
*** Get only Web items
IF loItem.nDataMode = 0
loItem.GetCategoryList(pcCategory,"sku,descript,price","!noweb")
ELSE
loItem.GetCategoryList(pcCategory,"sku,descript,price","noweb=0")
IF loItem.lError
THIS.ErrorMsg(loItem.cErrorMsg)
RETURN
ENDIF
ENDIF
In general it's a good idea to put all dataaccess code into the business object to isolate the logic, but sometimes you can gain more flexibility by exposing some direct SQL functionality directly in your code - just be aware that for VFP and SQL operation this may be more work in the end.
