Converting DBFCDX to SQL (ADO)

[cdmmaui]

Hello Everyone,

I finally made the move from DBFCDX to SQL via ADO and XBROWSE and I am having a few issues but I am sure they are easy to correct. I believe they are simple fundamental data handling differences.

1. I open application by connecting to SQL and listing data for a specific table, this part is ok, however I need to be able to allow user to enter criteria then refresh the already opened XBROWSE to new data

2. I want to connect to SQL get a column value then increase that column value by one, I am using table and column as a counter to handle a receipt number

3. I want to connect to SQL and insert, update or delete data (I got this part) then I need to refresh XBROWSE with new data

If someone could provide a basic layout of the process of connecting to SQL, listing data in XBROWSE, performing a SQL statement, then refreshing the XBROWSE I would truly be appreciative!!

Here are sample code I tried to use.

IF nRecordId>0
TRY
oSqlUpd:=TOleAuto():New("ADODB.Recordset")
CATCH
MsgWait( "It seems that your PC does not have MDAC installed OR MDAC is corrupted.")
RETURN (.F.)
END

cSqlUpd := "SELECT * FROM capsonic_receive WHERE id=" + LTRIM(STR(nRecordId,9))
TRY
oSqlUpd:Open( cSqlUpd, xSQL )
CATCH oError
MsgWait( cSqlUpd + " - " + xSQL, "Failed to Connect to the Database" )
RETURN .F.
END

// Init...
cRecvNo := LTRIM(STR(oSqlUpd:Fields( "RECEIVENO" ):Value,14))
dRecvDate := oSqlUpd:Fields( "RECEIVEDATE" ):Value
nRecvQty := oSqlUpd:Fields( "QTYRECEIVED" ):Value
cRecvPart := oSqlUpd:Fields( "PARTNUMBER" ):Value
cRecvLot := oSqlUpd:Fields( "LOTNUMBER" ):Value
cRecvPo := oSqlUpd:Fields( "PURCHASEORDER" ):Value
cRecvPoLine := oSqlUpd:Fields( "PURCHASEORDERLINE" ):Value
nRecvLabels := oSqlUpd:Fields( "LABELS" ):Value

// Close...
oSqlUpd:Close()
oSqlUpd:=NIL
ENDIF

// Delete...
IF cAction=="D"
lPass := MsgNoYes( "Are you sure you want to Cancel Receiving " + cRecvNo + "?", Ptitle )
IF ! lPass
RETURN (.T.)
ENDIF

// Delete...
cSqlUpd := "DELETE FROM capsonic_receive WHERE id="+LTRIM(STR(nRecordId,9))
TRY
oSqlUpd:=TOleAuto():New("ADODB.Recordset")
CATCH
MsgWait( "It seems that your PC does not have MDAC installed OR MDAC is corrupted.")
END
TRY
oSqlUpd:Open( cSqlUpd, xSQL )
oSqlUpd:Close()
MsgWait( "Record " + cRecvNo + " Deleted!" )
CATCH oError
MsgWait( cSqlUpd + " - " + xSQL, "Failed to Connect to the Database" )
END
RETURN (.T.)
ENDIF

[Rick Lipkin]

1. I open application by connecting to SQL and listing data for a specific table, this part is ok, however I need to be able to allow user to enter criteria then refresh the already opened XBROWSE to new data

Two ways you can do this .. either editing the row on the browse using the FASTEDIT clause

Code: Select all  Expand view  RUN

REDEFINE xBROWSE oLBX            ;
      RECORDSET oRsLabor               ;
       COLUMNS "TECH NAME",             ;
                    "ACTUAL RATE",           ;
                    "FLAT RATE",             ;
                    "DESCRIPTION",           ;
                    "TOTAL"                  ;
       COLSIZES 90,80,80,249,55         ;
       HEADERS "Tech",                  ;
               "Actual    hh:mm",       ;
               "Flat    hh:mm",         ;
               "Description",           ;
               "Total"                  ;
       ID 111 of oDlg                   ;
AUTOCOLS LINES CELL FASTEDIT
 

If you need to put some code behind the FASTEDIT columns you can use the OnPre and OnPost code blocks:

Code: Select all  Expand view  RUN

AEval( oLbx:aCols, { |o| o:nEditType := EDIT_GET } )

   // tech
   oLbx:aCols[1]:nEditType    := EDIT_LISTBOX
   oLbx:aCols[1]:aEditListTxt := aTech
   oLbx:aCols[1]:bOnPostEdit  := {|o,v| _GetPullDown( v,oLbx,oRsLabor,aEmp ) }

   // actual rate
   oLbx:aCols[2]:bOnPostEdit := {|o,v| _ActualChk( v, oRsLabor, oTotal, @nTotal, nShopRate, oLbx),;
                                     _ReCalc( oRsLabor,oTotal,@nTotal,oLbx )}

   // flat rate
   oLbx:aCols[3]:bOnPostEdit := {|o,v| _FlatChk( v, oRsLabor, oTotal, @nTotal, nShopRate, oLbx),;
                                     _ReCalc( oRsLabor,oTotal,@nTotal,oLbx )}

   // Description
   oLbx:aCols[4]:nEditType   := EDIT_GET
   oLbx:aCols[4]:bOnPreEdit  := { || If(oRsLabor:Fields("Description"):Value = " ", ,( __Keyboard( Chr( VK_HOME )))) }

   // total
   oLbx:aCols[5]:nEditType := EDIT_GET //If( oRsLabor:Fields("Flat Rate"):Value = 0,(MsgInfo("Edit None"),EDIT_NONE),EDIT_GET)
   oLbx:aCols[5]:bOnPostEdit := {|o,v|_CheckTotal(v, oRsLabor, oLbx,oBtn1,oBtn2,oBtn3 ),;
                                      _ReCalc( oRsLabor,oTotal,@nTotal,oLbx )}

   // add a new record
   oLbx:bPastEof = {|| _AddNewRow( oRsLabor,nRepairNumber,oLbx,"Y",nKey,oBtn1,oBtn2,oBtn3 )
 

Then when you are done editing you can do an oLbx:ReFresh() to make sure the line in the xBrowse is updated.

As for the rest .. we can take it one step at a time .. given you a lot to think about above ..

Rick Lipkin

[cdmmaui]

Thank you Rick, I appreciate your feedback.

I may use FASTEDIT, but we have too many data entry fields and we have to create windows/dialog boxes to handle input ad validation of fields.

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?

Thank you,

[Rick Lipkin]

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:

Code: Select all  Expand view  RUN

//------------------
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 )

Code: Select all  Expand view  RUN

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

Code: Select all  Expand view  RUN

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.

Code: Select all  Expand view  RUN

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:

Code: Select all  Expand view  RUN

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.

Code: Select all  Expand view  RUN

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:

Code: Select all  Expand view  RUN

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!

[cdmmaui]

Dear Rick,

Thank you so much, if I find myself in your neck of the woods or you find yourself in Chicago or Houston, please let me know. I owe you a dinner!

[Rick Lipkin]

Darrell

NP .. you are welcome .. One bit of good news for ADO Sql Server users .. I downloaded the Sql Express 2012 version from Microsoft and SQLOLEDB is still supported meaning native ( 32 bit ) ADO will be around for a few more years

Rick Lipkin

[lucasdebeltran]

Hello,

I am interested in ADO too.

Please, can you post a sample that adds, deletes, finds, filters, reports and xBrowses?.

I think it will be very usefull to migrate.

Thank you very much.

[Rick Lipkin]

Lucas

Here is the FWH Wiki on the subject compiled from many of our ADO friends including Enrico, Rao and myself ..

Rick Lipkin

http://wiki.fivetechsoft.com/doku.php?i ... ted_stuffs

[nageswaragunupudi]

Hello,

I am interested in ADO too.

Please, can you post a sample that adds, deletes, finds, filters, reports and xBrowses?.

I think it will be very usefull to migrate.

Thank you very much.

Please see \fwh\samples\testxbr3.prg
This shows how to use rdd, ado, etc

[nageswaragunupudi]

RecNo() --> oRecordSet:AbsolutePosition

Correction:
oRs:BookMark corresponds to RecNo() in rdd.
oRs:BookMark := uSavedBm corresponds to GoTo( uSavedBm ) in RDD
oRs:AbsolutePosition ----> OrdKeyNo()
oRs:AbsolutePosition := x ----> OrdKeyGoTo( x )

[lucasdebeltran]

Hello,

Thank you. I already checked those samples and wiki, great work, but it is missing network usage, locks, filters and reports.

Also, http://msdn.microsoft.com/library/defau ... erence.asp is not working any longer.

Mr. Rick, may I contact you by private mail?.

Thank you. Best regards

[nageswaragunupudi]

but it is missing network usage, locks, filters and reports.

The samples are network ready.

Actually XBrowse by default is fully network ready, unless the programmer overrides with his own codeblocks. XBrowse internally implements locking and unlocking where necessary.

For ADO most common locking system adopted is Optimistic Locking and it does not require placing explicit locks.

testxbr3.prg shows usage of reports also.

Also please see xbincflt.prg in samples

[Rick Lipkin]

Lucas

Rao is correct .. when you set up the recordset .. you are specifying Opportunistic locking and is handled by the database and there is nothing you have to do as a programmer.

In many ways ... ADO is much simpler to use than .dbf .. once you create the recordset object .. all you have to do is pass the oRs ( variable ) instead of using 'work areas' to your functions that manipulate the fields and data... also there is no need for record or file locking.

Just use the above Ado methods to manipulate the data .. I do not mind sharing my e-mail, however, there are probably many other programmers that can benefit from your ADO questions and there are many FW friends who can help you in this forum.

Rick Lipkin

[James Bott]

Rick,

SAYING := "SORRY ... you do not have ADMIN Rights"

SAYING := "SORRY ... before you can Delete a record ... "
SAYING += "you have to Add one first"

This an interface design issue, but my suggestion is that you can eliminate these types of messages by disabling the Delete button under these conditions. This way the user cannot perform an action that is going to get them a negative feedback criticizing them.

In many ways ... ADO is much simpler to use than .dbf .. once you create the recordset object .. all you have to do is pass the oRs ( variable ) instead of using 'work areas' to your functions that manipulate the fields and data... also there is no need for record or file locking.

While I don't disagree with this, if you use a database class (like my TData) then you don't need to use workareas or do any record or file locking either. These are built into the save() method. When the Save() method is called it checks to see if the file or record is locked, then locks it if needed, saves the data from the buffer to the record, then unlocks the file or record if it was unlocked to begin with. Oh, and you can pass around the database object too. No programming required.

I'm not knocking ADO, just pointing out the dbf use can be made simpler.

Here is another great example of what you can do with database classes. One can use a recordset class that uses the same methods and data as the database class (that works with DBFs). So you can plug in the recordset class to replace the database class. This just points out one of the great benefits of using classes and the reason for using polymophism (same names for methods and data) in the classes.

Examples:

Using a database class with DBFs:

Code: Select all  Expand view  RUN

oCustomer:= TCustomer():new()
msgInfo( oCustomer:name )
 

Using a recordset class with ADO:

Code: Select all  Expand view  RUN

oCustomer:= TCustomer():new()
msgInfo( oCustomer:name )

Well, look at that, they are indentical. It must be magic!

Here is an example of what little code you would need to use either DBF's or recordsets (assuming you have a recordset and database class):

Code: Select all  Expand view  RUN

// Example use a DBF
Class TCustomer from TDatabase
   method New()
endclass

method New() class TCustomer
   super():new(,"customer",,.t.)
return self

//---------------------------------------------------------------------------//
// Example using a Recordset
Class TCustomer from TRecordset
   method new()
endclass

method new() CLASS TCustomer
  super():new( "SELECT * FROM Customers ORDER BY CustomerID", "Microsoft.Jet.OleDB.4.0", "Northwind.mdb" )
return self
 

Then all you would need to do is plug in one or the other to switch from DBFs to SQL.

Regards,
James

[nageswaragunupudi]

Just academic discussion only.

For my personal development, I use a class derived from TDatabase, which handles both DBFs and RecordSets.
Methods and behaviour are identical.

Which is better DBF or ADO? The decision is not made on the basis of comfort of writing programs but mostly by the size of the data, organisation, number of users etc. For most corporate clients we may need to use an RDBS which they already own.

Though for last several years all my applications were using ADO (some cases ADS), I still maintain that it is far far more easier to handle DBFs than ADO particularly in multiuser environments. One may disagree but I have lots of answers