Client Server Database Access the xBase Way

[xProgrammer]

Hi all

Because I need my application to run reasonably efficiently across a VPN I need to move to a client server architecture. That seemed to mean that I would have to move to an SQL based solution. That should be quite possible but whilst I can use SQL and have done so in the past (but not from xBase) I don't like its fundamental architecture. So I have been trying to think of an alternative. (I also looked into getting fast data connections but the cost here was prohibitive.)

Whilst its early days I am quite excited about the prospect of getting the performance I need in a client server architecture but using xBase both ends. I have started with some socket code and written queries to be run on the server at the request of the client and the results returned. I was concerned about the format in which to transmit the data. My initial thought was to use xml - although that involves quite some overhead both in terms of the volume of data to be transmitted as well as encoding and decoding.

But then I hit upon using a multi level array and using HB_Serialize() to encode in binary form and HB_Deserialize() to decode it. And its working very nicely! Furthermore by returning property name property value pairs in an array (actually a sub array of the return array), given that my classes have actual properties rather than simulated ones I should be able to simply call __ObjSetValueList() to update all single value properties in my calling (client) object. (This last bit has not been tested in my socket programs but is a technique I am already successfully using.)

Currently the server has a QUERY class from which I derive a class for a particular query. This may not be the best architecture. I have to look at whether QUERY should be subclassed into single record type queries and list type queries. Also whether or not particular queries should be sub classes or parameterised objects. Also if a QUERY object should be able to call other queries and incorporate their data in its return.

I don't know if anyone else is interested in pursuing such an approach but if so I'm more than happy to share ideas, code whatever. My code is developed on a Linux platform but should run with little or no change on a Windows platform.

Regards
xProgrammer

[Antonio Linares]

Doug,

You may check if there is a Linux version of Leto db server:

http://sourceforge.net/projects/letodb

[Antonio Linares]

It seems as there is a Linux version:

http://letodb.cvs.sourceforge.net/letod ... ce/server/

[xProgrammer]

Hi Antonio

Thanks for the link. Interesting, but not really what I want (I think). From my experiments to date I think I can do what I want to do without needing any C code and in a way that integrates very nicely into my application architecture. My impression is that leto passes data by position which is a little on the dangerous side in my opinion.

I'll try to explain a bit more what I want to do and then I had better get back to trying to do it.

Lets say you want to let the user pick a patient from a grid of patients with a given name. To get this information in a typical xBase application you might have code along the following lines:

Code: Select all  Expand view

   ::oDBF:SetOrder( 2 ) 
   llSuccess := ::oDBF:Seek( ::sSearch )
   ASIZE( ::aKey,    0 )
   ASIZE( ::aFName,  0 )
   ASIZE( ::aGName,  0 )
   ASIZE( ::aGender, 0 )
   ASIZE( ::aDOB,    0 )
   DO WHILE llSuccess
      ::nFound += 1
      AADD( ::aKey,    PT_KEY )
      AADD( ::aFName,  PT_NMFAMLY )
      AADD( ::aGName,  PT_NMGIVEN )
      AADD( ::aGender, PT_GENDER )
      AADD( ::aDOB,    DTOC( STOD( PT_DOB ) ) ))
      ::oDBF:Skip( 1 )
      IF ::oDBF:EOF()
         llSuccess := .F.
        ELSE
         lcTest := UPPER( PT_NMFAMLY + PT_NMGIVEN )
         IF lcTest <> ::sSearch
            llSuccess := .F.
         ENDIF
      ENDIF
   ENDDO

This runs very nicely co0nnected to a LAN. However if you are running across a lower speed data connection between sites it can be rather slow. In my case it might take around 1 second per returned patient rather than a fraction of a second even if 100 patients are returned.

The problem is not that the code is inefficient per se, it's just that

1. more data has to be transmitted / received, and

2. that is done piecemeal rather than in aggregate.

So you could use an SQL based data base back end. Your code might look more like:

Code: Select all  Expand view

oQuery := SQL_QUERY():New()
sQueryString :='SELECT PT_KEY AS sKey, PT_NMFAMLY AS sNmFamly, PT_NMGIVEN AS sNmGiven, PT_DOB as sDOB, PT_GENDER AS cGender FROM PT_PATIENT WHERE PT_NMFAMLY EQUALS "' + ::sSearch + '"'
oQuery:sSelect := sQueryString
llSuccess := oQuery:Execute()

Please pardon me if my SQL is incorrect - I haven't used SQL for a few years now. Also note that the above SQL statement above is not quite equivalent in that it doesn't allow for optionally including given name.

Whilst this approach is intrinsically less efficient (the SQL statement has to be interpreted) in practice it may or may not be more efficient locally but across a lower speed connection it should be much much faster. For the reasons given above which have all to do with where code is executed / the data transmission requirements and nothing to with SQL per se.

So if we can run the xBase code on the computer that has the database, or on one with a fast communications channel to it, we will get similar speed benefits.

But with sockets that's not hard to do!

Just for illustration consider modifying the first code example along these lines:

Code: Select all  Expand view

   ::oDBF:SetOrder( 2 ) 
   llSuccess := ::oDBF:Seek( ::sSearch )
   ASIZE( ::aRESULTS[1],    0 )
   ASIZE( ::aRESULTS[2],  0 )
   ASIZE( ::aRESULTS[3],  0 )
   ASIZE( ::aRESULTS[4], 0 )
   ASIZE( ::aRESULTS[5],    0 )
   DO WHILE llSuccess
      ::nFound += 1
      AADD( ::aRESULTS[1], PT_KEY )
      AADD( ::aRESULTS[2], PT_NMFAMLY )
      AADD( ::aRESULTS[3], PT_NMGIVEN )
      AADD( ::aRESULTS[4], PT_GENDER )
      AADD( ::aRESULTS[5], DTOC( STOD( PT_DOB ) ) ))
      ::oDBF:Skip( 1 )
      IF ::oDBF:EOF()
         llSuccess := .F.
        ELSE
         lcTest := UPPER( PT_NMFAMLY + PT_NMGIVEN )
         IF lcTest <> ::sSearch
            llSuccess := .F.
         ENDIF
      ENDIF
   ENDDO

Now we have a result set in ::aRESULTS which we can binary encode with

[/code]sEncoded := HB_Serialize( ::aRESULTS )[/code]

and transmitted back to our client and decoded with HB_DeSerialize().

Effectively we can use the same coding techniques but split where the code is executed between client and server.

Please note that we can improve greatly on the above and also that we need to transmit also success / failure /error type information. Also we can use a QUERY class on the server, effectively pre-compile our queries, cut down substantially on the amount of code that needs to be written etc.

Regards
xProgrammer

[Antonio Linares]

Doug,

yes, I see what you mean.

It seems a sort of SQL running on the server side that just returns the result and minimizes the network traffic.

[Rick Lipkin]

xProgrammer

xHarbour and Harbour has a wonderful class from Win32ole and it is a very easy way to connect to any Sql database with your FWH and xBase Code.

I wrote ( some time ago ) an ADO thread explaining the usage and syntax using ADO.

http://forums.fivetechsoft.com/viewtopi ... hlight=ado

For the past year .. I have done nothing but MS Sql server\Ado and Antonio has even done a lot of work with the free adordd.

Have a look at the link .. you can compile a single executable and use MS Sql server, connect without any client side drivers and NO odbc .. ADO uses the native 'sqloledb' Win32 built into every Windows operating system from Win98 through Vista SP1 ..

Rick Lipkin

[Antonio Linares]

Rick,

Doug just work on Linux using FiveLinux. No ADO there as far as I know, though he may use ODBC or (DLLs equivalents for) MySQL, etc.

[Rick Lipkin]

Antonio

I kinda thought he was talking about Linux .. just curious .. if you could get the connection string for Linux and Sql server and figure out the client support... it seems that your could use the same syntax and Harbour or xHarbour's win32ole class ??

Not a Linux person here so I am no expert ..

Rick

[xProgrammer]

Hi Rick

Yes Linux kind of precludes that approach although I could use mySQL or PostrgeSQL or even DB2. I do have some reservations about the SQL approach although I have used it quite heavily (Microsoft SQL Server and Sybase) in the past. I like the idea of pretty much using the type of xBase code you might write anyway but splitting it in two and running it client server mode (which is what I need) and not having to go the SQL route. When I was using SQL Server there was a serious hole in the product which Microsoft never fixed whilst I was watching - you could only get back the (system generated) key of the most recent insert - so if your transaction involved more than 1, eg if a trigger was fired, you had problems and the Microsoft workaround was horrible. I attended a Microsoft developer briefing where we were promised it would be fixed in the next release - but it wasn't in the subsequent releases I saw. Probably fixed by now. For my money SQL Server is way overpriced, has flaws and is non portable. Plus I like being master of my own destiny to the extent I can. Having said that I am happy to pay for something like FiveWin (well more FiveLinux in my case) where there is some real support.

I guess I have sort of wandered off the topic a bit! I put the topic here because I thought some others might be interested in the approach I am taking. But I'm quite happy to push on regardless. In fact to date the journey has been rather exciting. Does that make me strange? Probably. But I can live with that.

Happy coding

Doug

[hua]

...connect without any client side drivers and NO odbc .. ADO uses the native 'sqloledb' Win32 built into every Windows operating system from Win98 through Vista SP1 ..
Rick Lipkin

Rick, does the above statement applies to any SQL server or it's just true for MS SQL server?

[Antonio Linares]

Rick,

> it seems that your could use the same syntax and Harbour or xHarbour's win32ole class ??

We don't know if there is an OleAuto clone for Linux.

Also I don't know if ADO has been cloned in Linux

[Rick Lipkin]

Hua

Since Microsoft makes Sql Server .. it stands to reason they would build into their pc operating systems a native way to connect .. and they do !! .. 'sqloledb' .. and there is NO need for odbc .. no client needed at all !!

However, you will need a client for dB2, Oracle or any other sql database ..

Rick

[xProgrammer]

Hi all

It may be far from perfect but I have client server database access with xBase both ends - backend is xHarbour, front end is xHarbour / FiveLinux. Back end is successfully returning lists of records, individual records and updating records. Next step is to support inserts.

Happy programming

xProgrammer

[hua]

Hua

Since Microsoft makes Sql Server .. it stands to reason they would build into their pc operating systems a native way to connect .. and they do !! .. 'sqloledb' .. and there is NO need for odbc .. no client needed at all !!

However, you will need a client for dB2, Oracle or any other sql database ..

Rick

Thanks for the clarification Rick.

[Antonio Linares]

Doug,

Excellent!

Could you provide us some more tech details about it ? As far as I understand it, you are using sockets and you have built a server using Harbour (console mode).

Have you tested it against multiple clients simultaneously ? Are you using threads ?