Darrell
I just wanted to understand the best way to make a SQL connection, perform SQL statement then close SQL connection. Is that the best way to handle? Or can I make one SQL connection then perform one...several...many SQL statement(s) before closing SQL connection?
I open recordsets to add\edit records in browses or dialogs .. Open them when I need them and close them when I am done .. much like I did with dbf\cdx.
I DO NOT create a connection and then use that active connection throughout the program to pass to and open recordsets. In my opinion, connections ( oCn ) should only be used to create tables, ADD\Insert or Update record(s) in ( like ) a batch mode. Again, this is my opinion and you may find others who dis-agree.
I prefer to use recordsets because they give you more control over how that information is fetched, ( e.g. local cursor, forward .. etc ) using Select statements vs connections that execute commands.
Two examples on how I would handle a delete
1) From a recordset called from xBrowse:
//------------------
Static FUNC DELDETL( oLbx,oRsCHk )
LOCAL SAYING
IF xADMIN = 'Y'
ELSE
SAYING := "SORRY ... you do not have ADMIN Rights"
MsgInfo( SAYING )
RETURN(.F.)
ENDIF
IF oRsChk:EOF
SAYING := "SORRY ... before you can Delete a record ... "
SAYING += "you have to Add one first"
MsgInfo( SAYING )
RETURN(.F.)
ENDIF
IF MsgYesNo( "Are you SURE you want to DELETE this?" )
Try
oRsChk:Delete()
Catch
MsgInfo( "ChkCode Delete Failed" )
Return(.f.)
End Try
TRY
oRsChk:MoveNext()
CATCH
END TRY
If oRsChk:eof .and. .not. oRsChk:bof
TRY
oRsChk:MoveFirst()
CATCH
END TRY
Endif
ENDIF
oLbx:ReFresh()
RETURN(NIL)
2) Deleting by connection ( execute )
oCn := CREATEOBJECT( "ADODB.Connection" )
TRY
oCn:Open( xCONNECT )
CATCH oErr
Saying := "Could not open a Connection to Database "+xSource+chr(10)
Saying += "to Delete from Table"+chr(10)
MsgInfo( Saying )
RETURN(.F.)
END TRY
cSql := "DELETE from [Users] where [UserId] = '"+cUserId+"'"
Try
oCn:Execute( cSQL )
Catch
MsgInfo( "Delete from Table USERS Failed" )
Return(.f.)
End try
oCn:CLose()
oCn := NIL
Or you can use combination of both if you have a complex join between tables
psudo code
cSql := "Select ... Left Join ..... Left join .... "
Create your recordset oRs ... in order to delete a specific row in a complex join .. you will need to use a connection to delete that specific row in that specific table .. as above. Then you can do a simple oRs:ReQuery() or better yet, close the recordset oRs:CLose and then just re-open the oRs with that same Sql statement without re-defining the oRs Variable .. just re-open the query.
This is how I work with SQL ..
Define the connection string as a Public variable .. xConnect in this code can be any SQL string for your specific provider or several .. in this example I can switch between Ms Access or Ms Sql server by evaluating xDatabase .. here xDatabase is hard-coded, but you can specify xDatabase in a .ini and easily switch between RDMS.
xDatabase := "A" // access
*xDatabase := "S" // sql server
If xDatabase = "A"
xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE := cDEFA+"\Travel.mdb"
xPASSWORD := "password"
xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD
Else
xPROVIDER := "SQLOLEDB"
xSOURCE := "RICKLIPKIN-PC\SQLEXPRESS"
xCATALOG := "TRAVEL"
xUSERID := "lipkinrm"
xPASSWORD := "richard"
xConnect := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD
Endif
Then when I want to fetch some data I wish to manipulate I create the recordset with these parameters:
oRsVendor := TOleAuto():New( "ADODB.Recordset" )
oRsVendor:CursorType := 1 // opendkeyset
oRsVendor:CursorLocation := 3 // local cache
oRsVendor:LockType := 3 // lockoportunistic
Create a Select Statement .. could be as simple as "Select * from Table" or it could be much more complex as using joins to span multiple tables to assemble the fields you wish to view or manipulate .. depending on your join complexity.
cSQL := "SELECT [UserEid], [UserId], [Password] From Avendor Where [UserId] = '"+cUserId+"'"
TRY
oRsVendor:Open(cSQL,xCONNECT )
CATCH oErr
MsgInfo( "Error in Opening Vendor table" )
RETURN(.F.)
END TRY
One thing to always keep in mind when using SQL tables .. you SHOULD ( must ) have a UNIQUE Primary Key ( usereid in the above example ) on each table and you can create a unique ( not null ) field type "counter" for simplicity.
Then use your Ado methods to manipulate your Data:
Append --> oRecordSet:AddNew()
Close --> oRecordSet:Close()
Commit --> oRecordSet:Update()
Delete --> oRecordSet:Delete()
Deleted() --> oRecordSet:Status == adRecDeleted
EOF() --> oRecordSet:EOF or oRecordSet:AbsolutePosition == -3
Field() --> oRecordSet:Fields( nField - 1 ):Name, :Value, :Type
FCount() --> oRecordSet:Fields:Count
GoTop --> oRecordSet:MoveFirst()
GoBottom --> oRecordSet:MoveLast()
Locate --> oRecordSet:Find( cFor, If( lContinue, 1, 0 ) )
Open --> oRecordSet:Open( cQuery, hConnection )
OrdListClear() --> oRecordSet:Index := ""
RecCount(), LastRec() --> oRecordSet:RecordCount
RecNo() --> oRecordSet:AbsolutePosition
Skip --> oRecordSet:Move( nToSkip )
Please forgive me if I have repeated much of the same information you already know or currently use ..
Also, I have found that you should try to use Locals for your oRs variable declarations and pass them as parameters to your various functions. If you do use Statics .. you may run into situations where you get variable creep between modules you may open twice under a MDI\Child scenario.
I hope I have answered most of your questions .. If you need additional code examples, please feel free to ask... Many of our FW friends use ADO and can help you.
Rick Lipkin
ps .. download Antonio's FiveDbu and look at how he is working thru adding ADO functionality. Neet stuff on how you can get fieldtypes, fieldnames, table names within a database and much more!
