LyndonOHRC
Programmer
I have data stored in dbase III+ file format. The customer still adds and edits these data with a legacy clipper application. Changing to another database it is not an option.
What I need to do is write some small modules that will add/edit/delete these records using ColdFusion as the customers needs browser client assess to these new routines.
I've loaded the stelsDFB driver on the web server and it works fine except this one problem. The supported SQL syntax does not have the Recno() function that dbase and clipper uses for a primary key. Since the database has no auto increment data type I can't find a way to have a primary key.
I'm stuck with the ancient design where the most important tables only have the, RECNO() function result, as the primary key. Thus, how do I delete or update a specific row?
The SQL syntax does have the ROWNUM() function when selecting data but it always returns a null result if you try to filter with it.
select rownum() as recno, lname, zipcode
from myTable
returns the record number just fine but
select rownum() as recno, lname, zipcode
from myTable
where rownum() <= 10
returns an empty set and does not return an error.
additionally
Delete from myTable
Where rownum() = 5
deletes no records and terminates without error; as it would if there were no records where where rownum()=5. Of course there is a row 5.
Any ideas on how to emulate a primary key would be much appreciated!
Lyndon
---People Remember about 10% of what you say ---They never forget how you made them feel. Covey
What I need to do is write some small modules that will add/edit/delete these records using ColdFusion as the customers needs browser client assess to these new routines.
I've loaded the stelsDFB driver on the web server and it works fine except this one problem. The supported SQL syntax does not have the Recno() function that dbase and clipper uses for a primary key. Since the database has no auto increment data type I can't find a way to have a primary key.
I'm stuck with the ancient design where the most important tables only have the, RECNO() function result, as the primary key. Thus, how do I delete or update a specific row?
The SQL syntax does have the ROWNUM() function when selecting data but it always returns a null result if you try to filter with it.
select rownum() as recno, lname, zipcode
from myTable
returns the record number just fine but
select rownum() as recno, lname, zipcode
from myTable
where rownum() <= 10
returns an empty set and does not return an error.
additionally
Delete from myTable
Where rownum() = 5
deletes no records and terminates without error; as it would if there were no records where where rownum()=5. Of course there is a row 5.
Any ideas on how to emulate a primary key would be much appreciated!
Lyndon
---People Remember about 10% of what you say ---They never forget how you made them feel. Covey