Question Solve problem on Progress 9 SQL INSERT on 2 tables

André Ficken

New Member
This is a question for Progress 9.1e SQL.
This is what I need to get done through an ODBC connection to a Progress 9.1e database.

- insert a record into a mastertable with the highest transaction number + 1 (eg. MAX(trans) + 1)
- feed the transnumber back into the calling program.
- insert a record into a detailtable with the same transaction number but starting on MAX(line) + 1
- feed linenumber back into the calling program too.

I have had a look at triggers/stored procedures and a bunch of variations of the INSERT INTO but after a full day of trying, I have not been able to resolve this challenge yet...

Anyone who could help me out here, or in the right direction?? You help is much appreciated!!!
Thanks!!
 

André Ficken

New Member
Hello Tamhas,

I totally agree with you on that, but here is my situation: I am working from windows mobile devices via ODBC and I have to work with that.
Besides that, the company that is using this will be phasing out Progress in favor of SQLserver in the coming 2-3 years.
At this time the switch to SQLserver is not in the planning yet for this part of the system but there will also be no investment done to move to OpenEdge for example.

I have got to get the best solution based on what I have. If there is no solution available or the solution is not robust enough, we might consider moving to SQL server anyway, since we
already have the Progress Dataserver for SQL in working order for other parts of the system.

Thanks anyway. André
 

TomBascom

Curmudgeon
The odds of that conversion actually happening are pretty low. I've long since lost count of the number of people who say "We're moving away from Progress so..." as an excuse for not paying maintenance and then upgrading but somehow never actually do so. Meanwhile their ancient, obsolete and unsupported releases get even older and less supported... and who do they blame when things go wrong?

In any event... I'm not sure I understand your problem. Why is it hard? Is there some unstated and hidden requirement such as "this must all be done with a single SQL statement"? Otherwise it seems pretty simple, get the last record, add 1, insert a new record, handle errors if someone else beat you to it.
 

André Ficken

New Member
Hi Tom, I can tell you that the plans are really happening en excuting as we write. In 2 other sites of this company this migration is taking place right now and in 1 of them Progress is extensively used.
The current situation at this stage is that I do exactly what you proposed. Get last number used, add 1 and insert it with that number. The challenge is that there are about 25 users competing to get that last number.
So the chance of getting beaten to it, happens on a regular basis. I know that because we monitor errors occurring. The other thing is, that the error handling and locking on the mobile devices is very poor. For the Progress clients I keep a transaction scope en locking around the find and insert for the PK and do an update for the rest of the columns. This keeps the clients straight and working fine. The mobile devices that connect through ODBC seem to be messing it up. There are quit a lot of transactions from both Progress clients as well as mobile devices and they seem to be concentrated at certain periods during the day. It's a 24/7 shop as well..... So I am really looking for some ideas to find a solution for the mobile devices.
 

TomBascom

Curmudgeon
I don't have any magic that will fix that. But my SQL capabilities are pretty poor -- someone else might have something to offer now that the requirement is a bit clearer.
 

André Ficken

New Member
Thanks for your replies sofar. My 4GL skills and SQL skills are not too bad, but this is pretty specific stuff.
To resolve this issue I was looking for 2 stored procedures on the SQL side. The first would create a header record and assign as PK the last used number + 1. This new number needs to be set as 'out' parameter, as well as an errorcode when the process fails. The second procedure creates a detail record based on the transaction number created by the other procedure and returns a new line number and an errorcode. On the 4GL side I have this already in place and that was much easier to do.... Unfortunately I have no experience on storedprocedures with Progress SQL, so it will have to be a steep learning curve if I need to get this on my own. If you know anyone that could give me some examples or help, that would be very much appreciated.
 
Top