 |
VFP 愛用者社區
本討論區為 Visual Foxpro 愛用者經驗交流的地方, 請多多利用"搜尋"的功能, 先查看看有無前例可循, 如果還有不懂的再發問. 部份主題有附加檔案, 須先註冊成為社區居民才可以下載.
|
| 上一篇主題 :: 下一篇主題 |
| 發表人 |
內容 |
goto-dream
註冊時間: 2004-05-11
文章: 909
第 1 樓
|
 發表於: 星期日 九月 26, 2004 6:58 pm 文章主題: A Simple SQL Pass-thru class（轉貼） |
 |
|
http://www.universalthread.com/wconnect/wc.dll?FournierTransformation~
test.prg
| 代碼: | Local loSql, lcConnect
*------------------------------------
*-- To Modify
lcConnect = "Your DNS Name to NorthWind"
*--------------------------------------
*== Example 1
*=======================================================================
Set Procedure To sqlfunctions1 Additive
loSql = CreateObject("sqlfunctions1")
loSql.cConnectString = lcConnect
loSql.GetTable(,"select * from customers", "Customers", "CustomerID" )
Browse && Modify some data here
? TableUpdate( 2, .F., "Customers" )
loSql.GetTable(,"select * from customers", "Customers", "CustomerID" )
Browse && Your changes should still be here
USE In Customers
loSql = NULL
*=======================================================================
*== Example 2
*=======================================================================
Set Procedure To sqlfunctions1 Additive
loSql = CreateObject("sqlfunctions1")
With loSql
.cConnectString = lcConnect
.cSqlStatement = "select * from customers"
.cCursorName = "Customers"
.cKeyField = "CustomerID"
EndWith
loSql.GetTable()
Browse && Modify some data here
? TableUpdate( 2, .F., "Customers" )
loSql.GetTable()
Browse && Your changes should still be here
USE In Customers
loSql = NULL
*=======================================================================
*== Example 3 -- Using transactions
*=======================================================================
Set Procedure To sqlfunctions1 Additive
loSql = CreateObject("sqlfunctions1")
With loSql
.cConnectString = lcConnect
.cSqlStatement = "select * from customers"
.cCursorName = "Customers"
.cKeyField = "CustomerID"
EndWith
loSql.GetTable()
loSql.BeginTransaction()
Browse && Modify some data here
? TableUpdate( 2, .F., "Customers" )
loSql.RollBack()
loSql.GetTable()
Browse && Your changes were rolled back
loSql.GetTable()
Browse && Make some more changes
? TableUpdate( 2, .F., "Customers" )
loSql.Commit()
loSql.GetTable()
Browse && Changes were commited
USE In Customers
loSql = NULL
*======================================================================= |
Class
| 代碼: |
#DEFINE ERROR_NOCONNECTION -1
#DEFINE ERROR_STATEMENTFAILED -2
#DEFINE ERROR_NO_KEYFIELD -3
#DEFINE MSG_ERROR "Error"
#DEFINE MSG_ERROR_NOCONNECTION "No connection handle or connection string supplied"
#DEFINE MSG_ERROR_STATEMENTFAILED "Sql statement failed to execute"
#DEFINE MSG_ERROR_NO_KEYFIELD "No key field or invalid key field supplied"
Define Class SqlFunctions1 As Custom
cConnectString = ""
lDSN = .T.
nConnectHandle = 0
cCursorName = ""
cSqlStatement = ""
cKeyField = ""
lNoError = .F.
Function GetTable( nConnect As Integer, ;
cStatement As String, ;
cCursorName As String, ;
cKeyField As String ) As Integer
LOCAL lcAlias, lnResult, loNewCursor, lcCur, llReadOnly
lcAlias = Alias()
If VarType(nConnect) = 'N'
This.nConnectHandle = nConnect
EndIf
If VarType(cStatement) = 'C' And !Empty(cStatement)
This.cSqlStatement = cStatement
EndIf
If VarType(cCursorName) = 'C' And !Empty(cCursorName)
This.cCursorName = cCursorName
EndIf
If VarType(cKeyField) = 'C' And !Empty(cKeyField)
This.cKeyField = cKeyField
EndIf
*-- Connect
If Vartype( This.cConnectString ) = "C" And !Empty( This.cConnectString )
This.Connect()
Endif
If This.nConnectHandle <= 0
If !This.lNoError
=MessageBox( MSG_ERROR_NOCONNECTION, 16, MSG_ERROR )
Endif
Return ERROR_NOCONNECTION
EndIf
*-- Set the view parameters
IF TYPE("This.cCursorName") # 'C' OR Empty(This.cCursorName)
llReadOnly = .T.
lcTableAlias = SYS(2015)
ELSE
lcTableAlias = This.cCursorName
ENDIF
*-- Execute the view
lnResult = SqlExec(This.nConnectHandle, This.cSqlStatement, lcTableAlias)
IF lnResult > 0
=CursorSetProp("Buffering", 5)
Else
If !This.lNoError
=MessageBox( MSG_ERROR_STATEMENTFAILED, 16, MSG_ERROR )
Endif
IF !EMPTY(lcAlias)
SELECT (lcAlias)
ENDIF
Return ERROR_STATEMENTFAILED
EndIf
IF !llReadOnly
llResult = This.SetCursor( ALLTRIM(lcTableAlias) )
ELSE
llResult = .T.
ENDIF
IF Vartype( llResult ) = "L" And !llResult
USE
IF !EMPTY(lcAlias)
SELECT (lcAlias)
ENDIF
RETURN -1
ENDIF
RETURN lnResult
EndFunc
FUNCTION SetCursor( cRemoteTable As String ) As Integer
Local Array aTmpStruct(1)
Local lnFields, lcFields
lnFields = AFIELDS(aTmpStruct)
If Empty( This.cKeyField ) OR Type( This.cKeyField ) = 'U'
If !This.lNoError
=MessageBox( MSG_ERROR_NO_KEYFIELD, 16, MSG_ERROR )
Endif
Return ERROR_NO_KEYFIELD
Endif
=CursorSetProp("KeyFieldList", This.cKeyField )
=CursorSetProp("WhereType", 1)
=CursorSetProp("Tables", cRemoteTable)
=CursorSetProp("SendUpdate", .T.)
lcField = ""
FOR i = 1 TO lnFields
lcField = lcField + aTmpStruct(i,1)+' '+ cRemoteTable + '.' + aTmpStruct(i,1)+','
ENDFOR
lcField = SUBSTR(lcField,1, LEN(lcField)-1)
=CursorSetProp("UpdateNameList", lcField)
lcField = ""
FOR i = 1 TO lnFields
lcField = lcField + aTmpStruct(i,1)+','
ENDFOR
lcField = SUBSTR(lcField,1, LEN(lcField)-1)
=CursorSetProp("UpdatableFieldList", lcField)
ENDFUNC
Function Connect() As Void
*-- Return the current handle if we are connected
If This.nConnectHandle > 0
Return This.nConnectHandle
Endif
*-- Attempt to connect to the database
Local lnHandle
If !This.lDSN
*-- Use a connections string
lnHandle = SQLStringConnect( This.cConnectString )
Else
*-- Use a DSN
lnHandle = SQLConnect( This.cConnectString )
EndIf
This.nConnectHandle = lnHandle
EndFunc
Function BeginTransaction()
If This.nConnectHandle <= 0
If !This.lNoError
=MessageBox( MSG_ERROR_NOCONNECTION, 16, MSG_ERROR )
Endif
Return ERROR_NOCONNECTION
EndIf
Return SQLSetProp( This.nConnectHandle,"Transactions", 2 )
EndFunc
Function Commit()
If This.nConnectHandle <= 0
If !This.lNoError
=MessageBox( MSG_ERROR_NOCONNECTION, 16, MSG_ERROR )
Endif
Return ERROR_NOCONNECTION
EndIf
Return SQLCommit( This.nConnectHandle )
EndFunc
Function Rollback()
If This.nConnectHandle <= 0
If !This.lNoError
=MessageBox( MSG_ERROR_NOCONNECTION, 16, MSG_ERROR )
Endif
Return ERROR_NOCONNECTION
EndIf
Return SQLRollback( This.nConnectHandle )
EndFunc
Function Destroy()
If This.nConnectHandle > 0
=SQLDisconnect(This.nConnectHandle)
EndIf
EndFunc
Function Error( p1, p2, p3 )
*-- Error Code goes here
Error p1
EndFunc
EndDefine
|
_________________
福隆昌淨水有限公司--淨水器的專家,淨水器,飲水機,濾心!!
想了解更多，您可上幸福雞湯組.找尋!!丁澐瑄.老師.
愛作夢 |
|
| 回頂端 |
|
 |
nelsonchuang
註冊時間: 2003-09-04
文章: 563
來自: 臺灣
第 2 樓
|
| 發表於: 星期二 六月 21, 2005 1:50 pm 文章主題: SPT連線問題 |
|
|
請問一下!如果按照這篇文章的作法!是不是在每一個FORM裡面,我就開一個連線就好!
那SERVER只要有上百人,不就有數仟個連線?
SERVER不就很容易當了?
而且管理連線也不容易吧!
還是整個系統只要開一個連線就好?
可是這樣不就很危險?連線一斷,所以資料都斷了?
=SQLCONNECT([nStatementHandle])
有那一位可以指導一下嗎?
還是大家都怎麼作?可以說一下嗎?
_________________
大家好,請多指教 |
|
| 回頂端 |
|
|
syntech
註冊時間: 2003-05-16
文章: 4249
來自: Taipei,Taiwan
第 3 樓
|
| 發表於: 星期二 六月 21, 2005 2:32 pm 文章主題: |
|
|
你說的沒錯.
所以後來的資料連接技術才有 connection pool ,
先由connection pool 取得未使用的連線,
使用後的連線又會回收到 conntection pool
_________________
如果公司有下列困擾:
1. 找不到便宜,快速,簡易的 生產排程軟體
2. 不知道如何快速排定 採購計劃
3. 成本抓不準,自己算比軟體算有用
4. 想學習系統規劃,想找系統架構的顧問
請聯絡我們,也許我們幫得上忙 |
|
| 回頂端 |
|
|
|
|
您 無法 在這個版面發表文章
您 無法 在這個版面回覆文章
您 無法 在這個版面編輯文章
您 無法 在這個版面刪除文章
您 無法 在這個版面進行投票
您 無法 在這個版面附加檔案
您 無法 在這個版面下載檔案
|
|
常見問題
搜尋
會員列表
會員群組
會員註冊
個人資料
登入檢查您的私人訊息
登入
