How to transfer VFP local table data to SQL-Server background for updates
Preface:
Explaining that there are not many VFP and SQL-Server, and the MSDN mainly introduces how VFP is commonly used by SPT with SQL-Server, connect, disconnect, execute, set an environment, etc.
I have been looking for a long time in MSDN, maybe her content is too much, I have never found an example of how to pass the VFP local table to SQL-Server for storage (except for the examples involving the view).
Why not use a view? It can be said that this is also my personal preference. I don't like the background with SQL-Server. What is the view you need to do, and for this view, I have to create a database (DBC), then the front desk is also maintained This database. I don't know if I don't understand the database (DBC), I don't understand how to maintain her. Anyway, I have seen many times because DBC inexplicably issues cause the program to implement correctly. . .
I like to dry clean nets in front desk, and this file can be run normally, and the program settings can be placed in the registry, or placed in the INI profile. The INI profile can be placed in the Windows system directory, like System.ini, Win.ini. . .
It seems to be a bit run, huh, huh, the following words are passed down. . .
There are many ways to transmit the data of the local table to the server. Of course, we have to do according to your actual needs. Although the DTS function is very powerful, it is not necessary to meet our requirements. Especially if we do a data entry screen, or when a small data update is executed, it seems to have a little small question with DTS? Oh, I don't know if the experts can use DTS to get the center thinking involved in this article. Anyway, I read the words of DTS in the night in the SQL-Server2000, or a little dizzy ~~
Let's take an example below.
Example introduction:
There is a table on the SQL-Server database, because I have named Test for test, she has 3 fields, which are automatic growth ID fields (default 0), one character's field test (varchar) ), A logical field LL (bit) (default 0).
Note: Do you think about why you use this example in this example?
The structure of the table is simple, just for the problem, the TEST field is a field that can be modified, similar to some description, name, note, title, her value is not very important. The field LL is a marker field because she is a logical type, she is like this: only when ll = 0 can modify the contents of the TEST field, and after modification, I want to set an LL = 1, The record will not be revised again. The value referring to the field TEST will no longer be modified. It seems that it is confirmed by the LL field, and it cannot be confirmed again.
The following is two-segment code running in the SQL-Server query analyzer:
Code 1:
Declare @BitError Bit
Set @ biterror = 0
Begin Transaction
Update test
Set test = getdate (),
LL = 1
WHERE ID = 13 and ll = 0
IF @@ error <> 0 or @@ rowcount <> 1 set @ biterror = 1
-
IF @ biterror = 0commit
Else
Rollback
-
Select Case @BitError
When 0 Then 'success! '
When 1 Then 'failed! '
End
AS results
Code 2:
Begin Transaction
Update test
Set test = getdate (),
LL = 1
WHERE ID = 13 and ll = 0
IF @@ error <> 0 or @@ Rowcount <> 1 goto errorhandle
Commit
SELECT 'Success' AS Results
Return
ErrorHandle:
Rollback
SELECT 'Failed' AS Results
Return
Code description:
The effect of the above code 1 and code 2 is the same, and is to update the field ID = 13 in Test, and LL = 0 records, so that the field test is equal to the current date, the value of the field LL is logical by logic.
When executed after the Update command is performed (no other system variable is also changed, no matter what system variables) @@ error and @@ rowcount, if the command is wrong, @@ error is the code that records the wrong code, no wrong @@ Error = 0. When updating the correct update, the update command should only update a record, but when updated, the log of the ID = 13 in the table has been equal to 1, and the execution of the UPDATE is inevitably violating the meaning of the previous mean, that is, it is no longer confirmed. Therefore, it will not satisfy in LL = 0 in WHERE conditions, causing a record to be updated, thereby changing system variable @@ rowcount = 0, this result is wrong, correct update should be @@ rowcount = 1 Correct!
The difference between code 1 and code 2 is not very obvious when only one record is updated, and if multiple records are updated, mainly
Update ...
IF ... set ...
with
Update ...
IF ... goto ...
The above two lines of code are actually n combined by the client cycle processing. The amount of code should be similar, and when there is an update error in the middle, the code of code 1 will continue to update because the transaction / rollback is performed in the end. The code 2's scenario will go back to the last rollback when the update error is encountered.
The above code is running directly in the background. We can put them in a variable in the VFP, and then pass this variable to the background. You can try 2 sets of codes. If you are code 1, when the code contains an error, the Test table name is wrong, so that it does not exist, then the runtime will generate errors. Code 1 will not return an error, and code 2 will return to the error generated by the background!
I tried it because there is a set @ bitError = 0 in code 1 such an assignment statement. As for the reason, I don't know.
So the scheme of code 1 is not available unless she stays during the stored procedure in the background.
However, when testing code 2, according to theory, a cursor file should be returned, the field is "success", "success" is still "failed" record, the background runs according to his own idea, can be in the VFP, what can be No, sometimes there will be errors, called "I can't return to the server"! In order to achieve the object of the above code, by rewrite the code, the more ideal answer can be obtained in the VFP to write the following code 3 (at least I feel better).
Code 3:
IF Runsql ("Begin Transaction")
Runsql ("Update Test Set Test = getdate (), LL = 1 where id = 13 and ll = 0")
Runsql ("SELECT @@ Error As Nerror, @@ rowcount as nrowcount")
IF NERROR # 0 or NROWCOUNT # 1
* - Update failed
Runsql ("rollback")
MessageBox ("Update error, or data is not updated, in short, this update failed!", 16, "Update")
Return
Else
*-- update completed
Runsql ("commit")
MessageBox ("successful update!", 64, "update")
ENDIF
ENDIF
Code description:
Runsql () in code 3 is a custom function, mainly call = sqlexec ()
The third line of code is to so able to write this because @@ error and @@ rowcount are system variables, if it is a defined or global variable, it cannot be used, it will be prompted to find the error of the variable!
Reference Code:
Call a DTS package on SQL to perform DOS mode execution:
EXEC XP_CMDSHELL 'DTSRUN / S "(local)" / u "sa" / n "DTS conversion test" / g "{aa52e887-9372-4036-a501-1e50c559d05a}"'
(Underline part is the command that can run under DOS)
At the same time, add multiple records to the server:
* - Start a transaction
IF! Runsql ("Begin Transaction")
Return .f.
ENDIF
SELECT TEMP_SELECTPERS
ShowProcessBar (Submiting_loc, Recount () 1)
SCAN
OprocessBar.showbar (Recno ())
LcPerid = Alltrim (STR (ID))
LCSQL = "INSERT INTO L_JOBS (...) Values (...)"
If Runsql (LCSQL) && performs insertion command
If Runsql ("SELECT @@ ERROR AS NERROR, @@ rowcount as nrowcount") && returns the status
IF NERROR # 0 or NROWCOUNT # 1
* - Update failed
Runsql ("Rollback") &&
MessageBox (Fail_loc, 16, Stop_loc) Showbar (Recount ("Temp_SELECTPERS") 1)
Return .f.
ENDIF
ENDIF
Else
Runsql ("rollback")
Showbar (Recount ("Temp_SELECTPERS") 1)
Return .f.
ENDIF
Endscan
Showbar (Recount ("Temp_SELECTPERS") 1)
IF Runsql ("commit")
MessageBox (Datasaved_loc, 64, Info_loc)
ENDIF
