BookmarkSubscribeRSS Feed
AndreasEM
Fluorite | Level 6

I'm updating some data on Oracle via SAS Add-in in Excel (version 7.14). The SAS environment is 9.4. I noticed that insert ("Create New Records") and delete ("Delete Records") happens directly, that is before I leave "Edit mode". For update however, I need to leave Edit mode for the commit to happen. Does this make sense?

5 REPLIES 5
Patrick
Opal | Level 21

@AndreasEM

I'd say intuitively one would expect a consistent behavior and if there is a commit in AMO then my expectation would be that nothing gets commited before I push the button.

 

When does the actual commit for insert and delete happen? Right when you move to another row or only when you leave the sheet without commiting? Are you checking directly on the data base - i.e. change something via AMO and then query the table from another SAS session or with SQL Developer and test what's actually in Oracle?

 

And the next question would be: Is the behavior you observe anywhere documented? If not then I'd raise a SAS TechSupport track for this one.

 

Tom
Super User Tom
Super User

Sounds like a reasonable implementation to me. If you are in an EDIT mode then you could be making changes to many variables and/or observations.  For example you might want to change two variables for the same observation, say the status and effective date. You wouldn't want it to commit in the middle after you had only changed one value.

 

But if you are deleting whole observations or inserting whole observations then why not have those operations take effect immediately?

 

Patrick
Opal | Level 21

@Tom

"But if you are deleting whole observations or inserting whole observations then why not have those operations take effect immediately?"

Because SAS AMO has a COMMIT "button" so why should anything commit before you commit? 

AndreasEM
Fluorite | Level 6

Thanks for your replies! I've found that the commit button doesn't do anything at all when updating on Oracle.

To answer some of your previous questions, the "delete records" happens immediately when I click it in Excel, I check this with SQL Developer direclty on the DB. The same goes for "Create New Records", it immediately creates a new record with only *null*-values (this table has no triggers).

When updating the new record however, I can click "Commit" how many times I want, nothing happens on Oracle. I need to click "End Edit", when I do this there seems to be an autocommit that updates the table.

I will check the documentation and in case there's a discrepancy I'll file a track.

AndreasEM
Fluorite | Level 6

I was a bit quick in my latest reply stating that the commit button doesn't do anything. It commits, it seems, but not all the way to Oracle as the insert / delete. You need to "End Edit" for the commit to happen on Oracle. Maybe this is not as bad as I first thought, but maybe a bit inconsistent.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 894 views
  • 0 likes
  • 3 in conversation