Tutorials to .com

Tutorials to .com » Software » Vfp » VFP and SQL SERVER like to do with C / S system

VFP and SQL SERVER like to do with C / S system

Print View , by: iSee ,Total views: 35 ,Word Count: 5479 ,Date: Sat, 18 Apr 2009 Time: 6:07 PM


What is 1.CLIENT/SERVER in the end?

C / S are 2-TIER system, suitable for small and medium-sized applications. Large-scale systems generally use a 3-TIER.
For example stand-alone database system (such as VFP), equivalent to the former shop in the Treasury. What the store needs may find themselves to the Treasury, the Treasury Management is also to carry out your own. And c / S system, shops and libraries are relatively independent, there is a specialized warehouse management (data SERVER), the store needs, in accordance with the procedures to the list
Treasury management staff to operate them.

Therefore we can see that C++ / S advantages: support for multi-user; more effective data management, data security and more reliable; long-range use of data.
If you are using stand-alone course, there is not much need to use C / S a.

VFP itself if C / S system, the effect is not very clear, because the data management function vfp is not very strong, for example, need to unlock the locking operation procedures. I say that VFP / SQL SERVER system.

2. SQL SERVER

SQL SERVER Microsoft released RDBMS (relational database management system), ORACLE, INFOMIX, POWERBUILD, are also similar systems. They equivalent to the warehouse management system, but not just data management functions. Microsoft's database product functionality and scale from small to large order, followed by: ACCESS, VFP, SQL SERVER.

Slightly larger system, SQL SERVER is a need for special management, and this is the DBA (DATABASE ADMINISTRATOR) the position. Now on the talent market in North America, DBA work better than the work of programmers to find more. Wages slightly higher than the average programmer, the work of a number of strong stability.

SQL SERVER is a large software system can be installed on a dedicated data SERVER on NT, but also in the Personal Edition can be installed on WIN95/98, mainly for the convenience of our programmers to test these. It has so few major features: database management and maintenance, user / security management, data release / conversion.

3. With VFP / SQL SERVER to do C / S system

VFP and SQL SERVER should be compared with the perfect, but because vb is Microsoft's mainstream products (VB has always been Microsoft, foxpro is later bought raw stepmother), so Microsoft has never been highly recommended or publicity VFP, but VFP database unique technologies into their products the other: SQL SERVER, ODBC, OLE DB / ADO. recent VFP even in VISUAL STUDIO independent from it.

VFP and SQL SERVER exchanges, can be three kinds of ways:
A. Control B. ADO Remote View C. SPT (SQL PASS THROUGH)

Three kinds of methods have advantages and disadvantages, and I am the first three kinds of the most preferred. Long-range view and ADO in VFP and SQL SERVER the middle of a level, and this level is equivalent to a black box, you do not know how to operate their data in the end only in accordance with the rules they set, the use of
The corresponding command will be. Anyway, I as a programmer, I like to know the procedures for doing each step, so I like to use SPT, also from the wizard to create a form do not have a category of statements.

SPT technology is through the VFP functions SQLCONNECT () to establish a connection and SQL SERVER, and then SQLEXEC () function to perform the SQL commands sent to SQL SERVER up implementation,
The operation of all the data through these SQL commands are carried out. Every detail by their own code to control.

4. VFP data promotion

VFP database, you can directly own VFP Upsizing Wizard to SQL SERVER, but must be on all the database tables, free table can not be promoted directly, while in fact only be promoted up the table, the database can not be where to SQL SERVER. feel strange. may be promoted to the index or the relationship between you.

Promotion to VFP data to SQL SERVER, we must first create the corresponding SQL SERVER database. SQL SERVER management is through the Enterprise MANAGER (EM) to the. Through its establishment of databases, each database can contain data tables, user / user groups, views, stored procedures and so on.

As for switching to SQL SERVER, the original VFP program is certainly a need to amend, but also relatively large changes.


***************
VFP and SQL SERVER like to do with C / S system (b) - dynamic set ODBC connection

1. Setting ODBC Data Source

VFP and SQL SERVER connection is through ODBC or OLE DB (ADO) to the. Can open the control panel to set up ODBC data sources. However, many cases, we hope to set up in the program dynamic data sources, one can not go to the machine for each user manually set, and secondly in order to data security, the use of after, so that the ODBC data source deleted .

The following example is the use of API functions to set and delete ODBC data sources.
Function name is SQLConfigDataSource, of which the second parameter is the number of (1 - 2 - Revision 3 - deleted)

DECLARE INTEGER SQLConfigDataSource IN odbccp32 INTEGER, INTEGER, STRING, STRING

lnWindowHandle = 0
lcODBCDriver = 'SQL Server' & & DRIVER type
lcODBCName = 'SharedData' & & data source name
lcODBCDesc = 'Shared Data Source' & & data source description
lcODBCServer = 'DEVSQL' & & SQL SERVER name
lcODBCDatabase = 'Shared' & & want to connect to the database name

** First attempt to revise the existing ODBC, if does not exist, return 0.
lreturn = SQLConfigDataSource (lnWindowHandle, 2, lcODBCDriver,;
'DSN =' + lcODBCName + CHR (0);
+ 'Description =' + lcODBCDesc + CHR (0);
+ 'Server =' + lcODBCServer + CHR (0);
+ 'Database =' + lcODBCDatabase + CHR (0))
IF lreturn = 0 & & does not exist

** Add a new ODBC
lreturn = SQLConfigDataSource (lnWindowHandle, 1, lcODBCDriver,;
'DSN =' + lcODBCName + CHR (0);
+ 'Description =' + lcODBCDesc + CHR (0);
+ 'Server =' + lcODBCServer + CHR (0);
+ 'Database =' + lcODBCDatabase + CHR (0))
IF lreturn = 0 & & failures
MessageBox ( 'failed to add ODBC data sources', 16,' BUFFER ')
ENDIF
ENDIF

2. Delete ODBC data sources

** Exhausted, you can delete the form DESTROY incident ODBC. If you do not want to repeat the set parameters, these parameters can be added to the form as an attribute.

DECLARE INTEGER SQLConfigDataSource IN odbccp32 INTEGER, INTEGER, STRING, STRING

lnWindowHandle = 0
lcODBCDriver = 'SQL Server'
lcODBCName = 'SharedData'
lcODBCDesc = 'Shared Data Source'
lcODBCServer = 'DEVSQL'
lcODBCDatabase = 'Shared'

** Amended, or the existence of its
lreturn = SQLConfigDataSource (lnWindowHandle,;
2, lcODBCDriver,;
'DSN =' + lcODBCName + CHR (0);
+ 'Description =' + lcODBCDesc + CHR (0);
+ 'Server =' + lcODBCServer + CHR (0);
+ 'Database =' + lcODBCDatabase + CHR (0))
IF lreturn = 1 & & ODBC exists, delete it
lreturn = SQLConfigDataSource (lnWindowHandle,;
3, lcODBCDriver,;
'DSN =' + lcODBCName + CHR (0);
+ 'Description =' + lcODBCDesc + CHR (0);
+ 'Server =' + lcODBCServer + CHR (0);
+ 'Database =' + lcODBCDatabase + CHR (0))
IF lreturn = 0
MessageBox ( 'Delete failed ODBC source, 16,' BUFFER ')
ENDIF
ENDIF

** Clear the DLL
CLEAR DLLS

3. From VFP to connect to SQL SERVER

lnHandle = SQLConnect ( "SharedData", "username", "password")
If lnHandle> 0 & & connected
** Data from the library (for example, be from the EMP table department number is'01 'staff)
lnReturn = SQLExec (lnHandle, "Select * from Emp Where cDept ='01 '", "CursorEmp")
If lnReturn> 0 & & running a successful
Browse
Else & & failure
& & Error handling
EndIf
Else & & connection failure
MessageBox ( "Connect SQL SERVER failure", 16, "BUFFER")
EndIf

** Spent connected, it is best to close immediately, the connection is very valuable resources, Microsoft is charging by the number of connections and each connection will increase the administrative burden of SQL SERVER
= SQLDisconnect (lnHandle)

***************
(Continued) (see these posts must be attached)

SPT technology used to update data through SQL commands, SQL commands must comply with the ANSI or T-SQL (Microsoft TRANSACTION SQL), as ordered by VFP's SQLEXEC () function to the SQL SERVER to implement, grammar must comply with the SQL SERVER rules, rather than the rules of VFP. Those who are not familiar with the most likely error is a function of the VFP to send to the SQL SERVER run, resulting in the total error. Most of the two functions are different.

For example, ALLTRIM () is VFP's, SQL SERVER is the LTRIM () and RTRIM ()
VFP date () boundaries, but do not recognize SQL SERVER years, we must use single quotation marks.

If the SQL command used in VFP program variables, variables must be added before the question mark "?"
For example, to update a table field

lcLName = "Zhang"
lcFName = "San"
lnReturn = SQLEXEC (connection handle, "Update Emp Set cLName =? lcName, cFName =? lcFName Where cEmpNo ='733000 '")

If the return value lnReturn> 0, the update was successful

Insert records one by one, or modify records, only a record of each operation.
For example, temporary tables should be recorded in the TmpEmp added to the EMP table SQL SERVER, you must use the cycle

Select TmpEmp
Scan
lnReturn = SQLEXEC (connection handle, "Insert Into Emp (cEmpNo, cLName, cFName) values (? TmpEmp.cEmpNo,? cTmpEmp.cLName,? cTmpEmp.cFName)"
EndScan

*** To get a table on SQL SERVER structure, there are two methods, namely, self-running SQL SERVER stored procedure system. First, run a SELECT command.

For example, SQL SERVER from the EMP table structure to be

lnReturn = SQLEXEC (connection handle, "Select Top 0 * From Emp", "TmpEmp")

The results are returned to the temporary table is read-only, read and write in order to become available, little change needs to be done:

Select 0
Use DBF ( "TmpEmp") Again Alias Emp
Use In TmpEmp

Now is the temporary table of the EMP can be a read and write.

* Note "Select Top 0 From Emp" command in VFP is the wrong order, but can be implemented in SQL SERVER.


SPT and the use of view compared to the advantage of you every step clearly what they are doing, to know why the command to be wrong. The disadvantage is that you need to write an order to know more about the syntax of SQL SERVER.


***************
VFP and SQL SERVER like to do with C / S system (e) - SQL SERVER from VFP to control transaction processing and locking

** Establishing a connection with SQL SERVER
lnHandle = SQLConnect ( "ODBC Data Source", "username", "password")

If lnHandle> 0

& & Set to manual transaction processing model, code to control
= SQLSETPROP (lnSQL_Hand, 'Transactions', 2)

** The following command in EMPPAY selected from the records and add to the exclusive table lock (TABLELOCKX), until the end of the transaction (HOLDLOCK)

ln1 = SQLEXEC (lnHandle, "SELECT * FROM Emppay WITH (TABLOCKX, HOLDLOCK)", "Emppay")

** Implementation of other orders, such as the updating of data
ln2 = SQLEXEC (lnHandle, "Update PayTotal Set ......")
...
...

** If all orders are carried out correctly, then
= SQLCOMMIT (lnHandle)

*** If you want to give up the entire transaction processing, using
= SQLROLLBACK (lnHandle)

*** Close the connection, the Panel automatically end, locks are untied
= SQLDISCONNECT (lnHandle)

Else
**** Connection failed
EndIf

*** SQLSETPROP () function
VFP function that is used to set the attributes of the current connection. For example, transaction processing manual top.
There is also a commonly used attribute is DISPLOGIN, control whether to display the attributes of the login form SQL SERVER

The first three parameters: 1 - show the login form, if the login information (username, password) incomplete 2 - Login form shows the total 3 - do not show the login form

For example:
= SQLSETPROP (lnHandle, "DISPLOGIN", 1)

When using the SPT technology, in order to reduce the number of server connections to save the burden, the need to frequently connect and disconnect SQL SERVER, sometimes you do not want to let each user login to connect, when user first time login, you can keep the name and password up after the connection can be automatically logged.


***************
VFP and SQL SERVER like to do with C / S system (6) - SQL SERVER index

As VFP programmers, we know the importance of the index on the table, a good indexing process can be greatly shortened the time to read the data.
SQL SERVER index data table for example, the same is very important. You can be a simple test.

1. Index test

SQL SERVER procedure from the group or the ENTERPRISE MANAGER Open TOOLS menu QUERY ANALYZER tools.
SERVER to connect to you, select the QUERY menu CURRENT CONNECTION OPTIONS (current connectivity options),
SHOW STATS TIME and to SHOW STATS I / O two options ticked, then OK. These two options will tell you the order of time and endurance running.
If you use the SQL command in a number of forms, can also be selected on the QUERY menu SHOW EXECUTION PLAN, it will tell you the order form at Calvary the time.

Election of a proper size of the index table, and then the implementation of a SELECT command, you will see the running time.
For example, I used the table CHQ, nearly 400,000 records, the implementation of the following orders, the return of more than 5000 records

select * from Chq where date> ='02 / 01/2001 'and date <='02 / 28/2001'

Shows the situation of the time and read and write:
Table 'chq'. Scan count 1, logical reads 13145, physical reads 0, read-ahead reads 13152.
SQL Server Execution Times: CPU time = 5781 ms, elapsed time = 23526 ms.

CHQ table and then I DATE field on the index, and then run the same SELECT, received the following results:
Table 'chq'. Scan count 1, logical reads 3965, physical reads 14, read-ahead reads 0.
SQL Server Execution Times: CPU time = 704 ms, elapsed time = 6432 ms.

Compare the two results can be seen, after indexing, logic to read and write and significantly reduced the number of pre-reading and writing, taking up CPU time reduced from 5.781 seconds to 0.704 seconds, with a total running time down to 23.526 seconds from 6.432 seconds to run more complex if the command , the effect will become more pronounced.

2. To understand the index SQL SERVER

SQL SERVER data files and index files are stored in units of pages, each page is 8K. Equivalent to the size of the disk is divided into 8K blocks to store the data block as a unit.
Understand this is very important, it can help you understand the contents of the following.

SQL SERVER index 2 categories
One is CLUSTERED (physical index), each table can only have one record stored on disk when in full accordance with the order of the physical index.
The other is NONCLUSTERED (the logic of the index), record the order stored in the index table.

No matter what types of indexes, as long as they can reach the end, the index expression (including the field) the more simple and more effective.

Field as a physical index of what the most appropriate?

There are two principles: First, in accordance with the scope to select the records, which fields the most effective, and the other is in the record to write to disk will not be caused by hot (HOT SPOT).
Hot spot refers to a large number of disk read and write took place in the same area, causing I / O bottleneck effect, reducing the operating speed.

We take a look at examples of how to use these principles.
If there is a document table that contains these fields: document number, document date, amount, sales locations, other data have been.
Which it is suitable to be a physical index? General idea is not to repeat the value of the field (equivalent to VFP's primary key).

a. document its use, in fact, this is not the appropriate choice. because very few documents in accordance with the scope to select the records, such as you rarely use the order number to look up documents to a value greater than or less than the record. but also in the input also relatively easy to produce hot spots, because the inserted records are generally added to the end table, when the importation of a large number of users at the same time, we will have hot spots. documents not in conformity with its two principles above.

b. documents with the date, seems to be a more appropriate choice. because often need to select the record date range, but also because a large number of users at the same time the importation of the latest documents in the disk caused by hot spots.

c. with sales locations, this should be the most appropriate candidates for the physical index. because we will keep records in accordance with the location of choice, and inserted at the same time is unlikely to record sales from the same location, to avoid the hot spots.

Of course, different circumstances, your choice may be different, for example, you do not have tens of thousands of users do not need to be considered on the hot issues. But we have to remember that there is only the physical value of the index field is not the best.

3. FillFactor select the appropriate

In the SQL-CREATE INDEX command, there is a FILLFACTOR option, this option is on the efficiency index is a great effect.
FILLFACTOR indexing means, each page filled with the level of stored data, such as 100%, said index file to fill each page, the implied value is 0 (100%).

If you often need to insert a table record, select the appropriate FILLFACTOR very important. If you always use 100%, when you insert a new record, as are full page, on the need for paging operations (the current page is divided into two), frequent paging operation will take up server resources and time, but also reduce the efficiency of index files. FILLFACTOR if your not 100%, for example, 80 percent set in the insert a new record, as a result of the current page there is space, you can directly do not need to join the page. Of course, FILLFACTOR too low will reduce efficiency, since that case, the number of pages on the more, search the data used in a long time and occupy more space.

If it is read-only table, or insert a few records of the table, with the best 100% FILLFACTOR.

Insert a new record for the regular table, the need for periodic reconstruction of the index because the index files with the increase in the number of pages, efficiency will become increasingly low. Because all the index tree structure, when an increase in the following pages there is no law, the connection will be social upheaval, the reconstruction of the index tree structure can be re-finishing.

4. Index Analysis Tool

QUERY ANALYZER in the QUERY menu, there are options PERFORM INDEX ANALYSIS.
In the importation of your order, choose this, SQL SERVER command on your analysis, and then tell you what kind of index of the best on the current command.
You can program the commands used here on an analysis of the index to find the most appropriate.


Visual FoxPro Tutorial Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.