UPDATING PROBLEM IN SQL TABLES

[farzad321]

Hi to every one
I want to change my local table of accounting application into SQL .
I make a Database and connection in it via odbc and also make a remote view for retrieve data from sql.
I can read any data from sql very fine and no problem.
But when I need to update some data, every thing confusing .
Example
We have one table with one million record in sql. we have sales number field that is not unique.
when i open a remote view for sales number = 100 , I get cursor with 60 records. after that I copy this data to temporary table for editing.
then user make a change and do some edit on it.
Now how can i update this cursor .
when i try to delete all record in remote view , I get error connection busy.
when i try to tableupdate() i get error
I make a remote view updatable with all field but I most miss somethings.
maybe I have not uniqe field or timestamp field. I dont know.
I need your help.
Thank you.

 

[Chris Miller]

For any remote data you need primary keys (unique fields) in every record, that's true.

That you get a "connection busy" error when you delete records points out that you have record buffering. So deleting a record and leaving it causes an implicit tableupdate of the deleted record, it's also deleted in SQL Server.

I think you deleted the remote view records because your idea is to copy the remote view data into a table for editing and then copy the edited data back into the remote view by deleting everything and appending the edited data. Am I right with that guess? Well, this does not work at all this way.

With views you work by editing them and pushing edits back by tableupdate. Most often you'll use table buffering on the view to have full control about the moment of saving (or committing) the data by a tableupdate.

Chriss

 

[farzad321]

hi Chris
Yes you are right. I do it in old way because my user maybe need to save and maybe do not save his work. Do you think I must work on view and all edit on it. but if user want to cancel what i must do ?
also I dont a uniqe field. can i use a field with time stamp instead ?
unfortunately i am absent in my work place for two days and can not see your answer. but thank you very much.

 

[Chris Miller]

Hello Farzad,

if user want to cancel what i must do ?

Well, that's easy, either not do anything at all or TABLEREVERT. Done on all rows this puts back the view to what it was and enables requery.

can i use a field with time stamp instead?

I think you mean rowversion (

http://www.sqlines.com/sql-server/d...OWVERSION and,future version of SQL Server .)

No, that won't work. And if you mean a datetime, it's not guaranteed to be unique.

So to begin working with remote data you need to introduce actual primary key fields. It does not pay to work with any provisional fields just very likely to be unique, you'll just regret it.

Chriss

 

[tomk3]

Hello,

On connection busy this may help : You can set the view to fetch all records , see view - extended options - check both "all"
60s seems to be long, do you have an index in SQL on sales number ?

Regards
tom

 

[farzad321]

Thank you to all friends, Now i thik understand how do edit records. First open a remote cursor and make a table buffering . then user make some edit, delete and append. after that if user need to save i must do TABLEUPDATE() and everything's updated, and if no need to save I just close remote view and we have no any change in SQL ?
Just one question ,Realy i need to unique field for this exact updating in sql ? How is it going without this unique field?
[highlight #FCE94F]IF we need to use a unique field , how can make that[/highlight]? A serial number ? what is data type? binary or integer. what is the correct an best way.
please explain me.
sorry for Poor English.

 

[Chris Miller]

First, in summary of all the following explanations, what you should do, is this:

ALTER yourtable ADD COLUMN GUID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID();
ALTER TABLE yourtable ADD CONSTRAINT PK_yourtable PRIMARY KEY NONCLUSTERED (GUID);

You might be able to do that in one ALTER TABLE, I'm not sure you can specify a field you also create in the same alteration in the constraint part. But I'm sure it works in two steps.

Now you can specify GUID as the primary key field name in CURSORSETPROP.

Aside from that uniqueidentifier type, an automatically increasing integer also is used. An integer field with automatic sequence is an INT IDENTITY(1,1) field.

If you worked without primary keys so far you will also not have used actual relationships of tables with foreign keys, so that's another miss in your database conception. If you introduce that concept now it would be best you make yourself familiar with actual database concepts that are absolutely fundamental to designing a good database. Adding primary keys to all tables is just a starting point of enhancing your database design.

To clarify, that doesn't say anything about the business value of your data, only the technical level, but in general, you will have it much easier with any data processing if you know about database design principles under the main roof of the term "database normalization".

Just a little background knowledge you should have about the key types: If you decide for integers, the identity(1,1) clause will make it a readonly field. The values are 100% generated by the server, so any concurrency of adding records from different clients will be queued up by the database service process, executed sequentially and no records with same numbers are generated, as could be the case, if two clients compute MAX+1 based on the same current MAX value. Well, that's of course a nice aspect to save you from that harm, but it also means you can't use preliminary keys in your buffered views and that's important if you want to work on table hierarchies as simple as order and orderitems are already. The orderitems will point at their order via an orderid field and you can only know that value after TABELUPDATE of the order table. Worse than that, even after TABLEUPDATE your view record will not get the actual int id generated by storing the record and so to know what is the orderid you will need to requery or work with @@IDENTITY to know the key.

In short, it is easier to work with uniqueidentifier keys, as you also can write to such fields and generate keys on clients, too. With as simple code as:

oGUID = CreateObject("scriptlet.typelib")
cGUID = substr( oGUID.GUID, 2, 36 )

And that's the reason I started with the code to add GUID field and key constraint.

Chriss

 

[farzad321]

Thank you chriss,
I was insert a GUID into my sql table and make primary key on it.Now this field is full and until now every thing is okay.
but still when open a remote view with 50 records and make a change on some record, my records was doubled and not working properly ,

1- i have a unique field
2- make a primary key
3- rebuild remote view in my database and make updatable all fields

4-open remote view alias suny
5- = CURSORSETPROP('Buffering', 5, 'suny' )
6- make some edit ,delete,append
7- myresult = TABLEUPDATE(.T.)
i dont see any error but my records was doubled.!!
what is my mistake
please show me
with best regards
farzad

 

[Chris Miller]

Point 6, what did you append? Just a few blank records?

At first, I would add the GUID to the updatable fields (click the check under the pencil symbol) and whenever you append a blank record, put in a GUID into the GUID field using the code I gave you earlier:

oGUID = CreateObject("scriptlet.typelib")
cGUID = substr( oGUID.GUID, 2, 36 )

Also notice: Everything you append gets the status of being new records. If you append data from the table this effectively duplicates it, as TABLEUPDATE inserts appended records, it doesn't update them.

Chriss