afaik last version of rddado is not working in harbour (after ole modification)
rddsql is best choice
AFAIK RDDSQL talk directly with the sql language
this is message post by Mindaugas Kavaliauskas on this list
Code:
===================================================================
Simple SQL Interface for Harbour
1. Introduction
Simple SQL interface implements accessing SQL query result via RDD
interface. It is not intended to be replacement for "transparent" move of
DBFCDX application to SQL world.
I want to discuss this in more detail. Many current RDDs for SQL servers
(ex. SQLRDD from xHarbour.com) tries to make a feeling you are working with
DBF file, but not with SQL database. SQL server does not support many
features, ex. RECNO(), deleted flag, file locks, record locks. These RDDs
are emulating these features to make feeling of DBF. DELETED() function is
emulated by creating additional table columns to store delete flag. Some
"hidden system" tables are used to register locking operations and emulate
record and file locks in DBF style. The idea of SQL query is also lost. If
you do a simple loop
DBUSEAREA(, "select * from my_table")
DO WHILE ! EOF()
somefunc( FIELD->some_sql_field )
DBSKIP()
ENDDO
RDD usualy will read SQL rows in portions, let's say 100 records per query.
So, hidden queries are generated. If you are using indexes these queries
are really complicated. Let's have index on FIELD1 + STR(FIELD2). A seek to
value cValue1 + STR(nValue2) will generate a query like:
SELECT * FROM my_table
WHERE (FIELD1 == cValue1 and FIELD2 >= nValue2) or FIELD1 > cValue1
ORDER BY FIELD1, FIELD2, _RECNO
LIMIT 100
After evaluation of first 100 cached records, next query will be generated:
SELECT * FROM my_table
WHERE (FIELD1 == cLastField1 and FIELD2 == nLastValue2 and
_RECNO > nLastRecno) or
(FIELD1 == cLastField1 and FIELD2 > nLastValue2) or
FIELD1 > cLastValue1
ORDER BY FIELD1, FIELD2, _RECNO
LIMIT 100
To optimize these queries the SQL index expresion should be
"FIELD1,FIELD2,_RECNO", but not "FIELD1,FIELD2" as written in INDEX ON
command.
"Simple SQL interface" is too long to repeat every time I want to
address this library. I'll also use acronym "SSI" to address it.
The idea of SSI is different. It does not make hidden queries. All
queries should be made explicitly by programmer. SSI gives access to query
result via RDD interface, it does not tries to emulate DBF and be
"plug-and-play" solution for DBF to SQL migration. If you do
DBUSEAREA(, "select * from my_table")
all query (it could contain millions of records!) will be cached.
The features of SSI approach are:
- It's possible to access SQL database of other applications. Other
applications usualy does not follow agreement of "plug-and-play" SQL drivers
about additional DELETED column, _RECNO in the end of index expression, etc.
Access of SQL database of other applications is sometimes not possible.
- It's query oriented. That means a simple DO WHILE ! EOF() loop will iterate
each records once and only once. This is not true for "plug-and-play" SQL
drivers, if indexing is used. Just like in the case of loop over DBF file.
It is not guaranteed that all records are included! Yes! If key value of the
first record in index is changed to be the last record in index during the
phase of record processing, DO WHILE ! EOF() loop will iterate only this
single records even if the database contains millions of records. Your sould
do FLOCK() on DBF to guarantee the records are not changed. Do you use FLOCK()
before readonly DO WHILE ! EOF() loops? :)
2. Architecture
+-------------+
| |
| SQLMIX RDD |
| |
+-------------+
| ^
V |
+-------------+ +---------+
| |--->| |
| SQLBASE RDD | | SDD |
| |<---| |
+-------------+ +---------+
SQLBASE RDD implements basic functionality for accessing SQL query result
via RDD interface. This RDD could be used, if indexing of query result is not
necessary or all indexing is done by SQL server (by using ORDER BY clause).
SQLMIX RDD implements indexing of query result. This indexing is not
related to SQL server ORDER BY clause. SQLMIX do indexing of the query on the
client side.
SDD is acronym for Sql Database Driver. RDD is used to implement access
of different database formats like DBF, SDF, etc. SDD is used to implement
access of different SQL databases. Every SQL server (MySQL, PostgreSQL, etc.)
has a corresponding SDD. SDD driver implements a specific part of data
exchange interface between SQLBASE and SQL server.
A few additional functions are also implemented, ex. HB_SQLCONNECT().
Usualy these functions are just a shorter version of corresponding RDDINFO()
call.
3. Modifying database
SSI presents a query result via RDD interface and generates no hidden
SQL queries. So, how database can be changed? Does DBAPPEND() and FIELDPUT()
works, or is it readonly SQL interface?
DBAPPEND(), FIELDPUT() and other similiar functions work on cached query
result, i.e. query can be appended by new rows and field values can be
changed, but SQL database is not changed. DBCREATE() function can also be
used to create an "empty query result" but no table is created on SQL server.
So, SSI can also be used as implementation of "array RDD".
The programmer must call SQL command explicitly to modify SQL tables.
SSI provides a method to detect which cached rows was changed or appended.
===================================================================
2010/5/12 Qatan <supo...@tribalbrasil.com>:
> Hello Don Lowenstein,
>
> I think this is the same one I found in \examples\rddado of Harbour from
> SVN, right?
> I'm testing it to get information from a MDB access database from a old
> system my friend is using and needs some extra features like sending email
> automatically to the clients, etc...
> What is your experience with RDDADO? Seems interesting and simple but I
> heard it was not being updated anymore, maybe because it is stable. Do you
> anything about it that you could share with us?
> I tried also \contrib\hbodbc but didn't work (probably my fault).
> I am discovering the RDDSQL "SQLMIX". Seems interesting and easy to use.
> Do you know anything about it?
> Thanks for your suggestion.
> Regards,
>
> Qatan
>
> ----- Original Message ----- From: "Don Lowenstein"
> <don.lowenst...@laapc.com>
> To: "'Users of the Harbour compiler'" <harbour-users@harbour-project.org>
> Sent: Wednesday, 12 de May de 2010 11:29
> Subject: RE: [Harbour-users] Re: DBF Fast text search / SQLite
>
>
>> Check out ADO in this support link ( Fivewin support forum) from Antonio
>> Linares
>>
>> http://forums.fivetechsupport.com/viewforum.php?f=3
>>
>> search on ADO and ADORDD
>>
>>
>>
>>
>> -----Original Message-----
>> From: harbour-users-boun...@harbour-project.org
>> [mailto:harbour-users-boun...@harbour-project.org] On Behalf Of Qatan
>> Sent: Wednesday, May 12, 2010 8:11 AM
>> To: Users of the Harbour compiler
>> Subject: Re: [Harbour-users] Re: DBF Fast text search / SQLite
>>
>> Hello Angel,
>>
>> Thanks for your reply.
>> Faster access, easy querying method and the opportunity to learn SQL
>> without the trouble of setting up an server and administer that are
>> sufficient arguments for me to be encouraged about at least try SQLite.
>> I was reading the SQLite docs and I was impressed by the possibilities
>> but I will have to test it to know the reality of all that.
>> I'm a bit surprised because I was expecting something even simpler from
>> SQLite but seems that there is no way to do anything in SQL without
>> sweating
>>
>> the brow. I am trying to do a very simple application but it is not that
>> easy yet for me... I just want to create/open a DB, search for records,
>> add
>> more, edit some, delete some, do some searches, show data on screen and
>> print, this is mostly what a common database application would do - seems
>> pretty simple (in DBF terms) but in SQLite is a challenge for me since I
>> am
>> not familiar with SQL culture and syntax.
>> I plan to switch to something more robust like PostgreSQL / MySQL /
>> Firebird that will minimize the corruption problems but seems to be a long
>> journey yet.
>> SQLite will be a good start. If I can do something useful with it I will
>>
>> certainly be able to start using PostgreSQL.
>> Thanks for your help.
>> Anyone's else comments are also very welcome!
>>
>> Regards,
>>
>> Qatan
>>
>> ----- Original Message ----- From: "Angel Pais" <amigo...@adinet.com.uy>
>> To: <harbour-users@harbour-project.org>
>> Sent: Monday, 10 de May de 2010 15:29
>> Subject: [Harbour-users] Re: DBF Fast text search / SQLite
>>
>>
>>> There's only 2 options (mostly)
>>>
>>> 1) peer to peer acces. Either DBF or SQLite. Corruption expure is the
>>> same
>>>
>>> 2) Client server access. Here you divide your corruption risk between
>>> client machines and your server.
>>>
>>> That's it !
>>>
>>> NetIO allows you to work in mode 1, or in mode 2 if using RPC and you
>>> split your program logic.
>>>
>>> Changing DBF in favour of SQlite, changes nothing if avoiding corruption
>>> is your goal.
>>>
>>> Sqlite has other advantages like multi languaje support, faster access,
>>> easier querying method. But it has nothing to do with corruption risks.
>>>
>>> HTH
>>> Angel
--
Massimo Belgrano
_______________________________________________
Harbour-users mailing list (attachment size limit: 40KB)
Harbour-users@harbour-project.org
http://lists.harbour-project.org/mailman/listinfo/harbour-users
