Sorry, I'm trying to familiarise myself with progress. I usually just hack away at something until I'm comfortable with it but I'm looking to update a live server and I can't find the information I need so I thought it would be better to ask. I got cold feet because I don't like looking like an idiot and thought I could find what I needed somewhere else.
I need to insert deleted records back into a Progress database, I'm not overly familiar with it but I'm comfortable with Microsoft SQL and since working with progress some OpenSQL. I'm using SSIS to bring the data out of a backup remove the rows I need and I'm looking to insert it back in to the progress database using SSIS but I'm not convinced that this is the way to do it.
I'm really asking if this is a legitimate way of doing this or if there is a progres tool\progress commands that I should be using. Apologies for posting and not being clear and the responses are appreciated
My understanding from your original post, before it was edited, was that you had a user who deleted a few dozen records from a DB and you needed to restore them, and that you had a backup of the database as at the time those records existed. If I am misremembering that, please correct me.
I think the most straightforward approach is to restore that backup somewhere, export the needed records to a .d file (Progress' ASCII dump/load file format) using the ABL command EXPORT, and then use either the ABL or the Data Dictionary tool to load that .d file's contents into the table that needs the data. I assume you know the key values of the missing records. If not, you could dump the data from that table from each of the two databases and diff the results.
Rob that is what happened, the first time I'm having to deal with a situation like this. If it was MSSQL I would be happy to restore the data but because I've been using MS Management Studio to work with progress in train and test environments where it's less of a risk I keep holding off using it to deal with live.
With live I want to do it through progress tools but I can't find one that gives me a way to work easily. I can use the data administration tool but to export the data with what looks like a where clause I have to extract to DIF, SYLK or TEXT.
Is there a tool that gives me a MS Management Studio environment where I can run an export from the backup to a d file, view the d file without having to open it in notepad, import the d file back into the live database.
The progress database I'm working with has very few constraints, the ODBC connection was provided by the supplier, in my mind it's just a data set if I did use MS Management Studio and the user interface picks up the data and allows a user to view, update and delete the records then using this method is ok.
That's my thoughts because I'm comfortable using MS Management Studio and I'm trying to justify it but I don't want any issues six months from now that turn out to be my fault so I would prefer to use the progress tools.
I know the key values but when you talk about exporting to a d file what method are you using. I don't care if it's as simple as a notepad environment I just need to see the full query on paper, run it against the database, view the results to see if it's what I'm expecting, load it in and then check the table/user interface.
I can't speak to any Microsoft tooling but if I were doing this with the good old 4gl I think I would write something like this and run it against the restored backup:
output to "order.d".
for each order no-lock where order.order-date = 12/1/2017:
That would give me a file called order.d with all of the order records with an order-date of 12/1/2017. I'm guessing that there is a relatively simple way to do something equivalent with SQL but SQL never fails to surprise me.
Given that file I would review it and edit it if need be. I would then import it into the "live" database like this:
Surely SQL has some equivalent way to insert data from a file?
(The 4gl code above is only "lightly tested" and it is possible that your environment has things like CREATE triggers or dictionary VALIDATE statements defined that might cause problems... on the bright side the SQL engine doesn't know anything about 4gl triggers so they won't get in your way if you use SQL instead of good old 4gl.)
Verify what software you have licensed for Progress. If you are on windows, I think there is a showcfg icon in the OpenEdge or Progress folder, on *NIX systems you can just type showcfg. If you have a 4gl dev license or possible some other license you might be able to write some basic 4gl code to insert the records. If you only have a db license, then the steps you go through to insert the data will be different.
I really appreciate the help guys I really do, thank you for responding to this post. I just can't figure out what what progress is, is it a database is it a programing language. It might be a simplistic view of a database but once you have data in a table an application can pick that data up.
At this point I feel that I'm over thinking this problem writing data into a table isn't going to change the table structure. Whatever method I use the only thing that could cause a problem is if a record doesn't conform to the constraints on that field. Since I'm copying data back in that was already in there this isn't an issue.
It's an application-development platform, with many optional components. Not all components are present in all environments, as CJ alluded to.
If we're going to nitpick, OpenEdge is the name of the platform and Progress is the company that makes it. Though it can be confusing because in 2003 and earlier, Progress was the name of the company and the platform so many people still use "Progress" to describe both.
The components include a relational database (officially called "OpenEdge RDBMS"), a programming language ("ABL", or "Advanced Business Language"; called "4GL" in 2003 and prior), various runtime clients that can connect to the database and run compiled ABL code, and various development products that include compilation capability (e.g. "4GL Development System", "OpenEdge Studio", "Progress Developer Studio for OpenEdge" which is an Eclipse-based IDE, formerly called "OpenEdge Architect", and "WebSpeed Workshop"). If you have a development license installed, it imbues the runtime with the ability to not only run pre-compiled code (object files with ".r" extension) but also to do compile-on-the-fly of source files (typically .p or .w files). This is sometimes also referred to as doing "session compiles" or "running uncompiled code".
This is why CJ asked which products you have installed. If you have some kind of product license that includes a compiler, you can just open an editor and write code similar to what Tom showed you above, and run it directly in the editor. If you don't have a compiler installed, you can only run compiled code (.r files).
Correct. You just need to dump the records you need from the source database, using the EXPORT statement, based on whatever record-selection logic is appropriate. Then it's a simple matter to import it into the database from which the records were deleted. Assuming no one has already tried to recreate those records in the meantime or otherwise reuse any deleted unique keys, you shouldn't hit any constraint violations when you load the data.
Thanks for the explanation, it's been a bit of a crash course so you've made it a lot clearer for me. I took a look and I do have an OE Studio license and a compiler so it seems like I can run code.
I've probably got another day to test a few things before I commit so I'll try and create a few p files to export and then import the data.
I'm still quite keen to use SSIS and the ODBC connection not so much for this situation but because it reduces my learning curve. I've found a few posts\articles that seem to suggest with the new SSIS 2012 ODBC connections that it is possible so I was wondering if there was a table check command I could run or any other checks I could run to try and test this method.
I don't want to push this. I have an answer and thanks to everyone that responded, I'm just thinking if I can test the ODBC method in some way it might of interest to someone else.
If you have OE Studio installed on your Windows box then you have the OE SQL driver you need. You should be able to create a system DSN to connect to your backup database, the one that contains the data you need. Then you can use the tools and language syntax you are familiar with to extract the records you need to a flat file.
Then repeat the process, creating a DSN for the database that needs the data, connect, and load the records into the table.
Note: an OpenEdge database isn't *necessarily* configured for SQL connectivity. It must be configured to allow connections for SQL clients, and you must have a user with the appropriate table permissions (at least SELECT on the source db; SELECT and INSERT on the target).
We have full ODBC connectivity, I can read and write to the database using integration services at this point but i'm trying to make sure there are no issues with this method.
I found a lot of information today checked the collation of both Progress iso8859-1 and SQL latin but I could only find documentation about using ABL to connect to progress and SQL databases or reading from progress to MSSQL not from MSSQL to Progress.
Spent some time looking at the ABL syntax as well so I'm getting more comfortable with this.
If anyone has any information about writing from MSSQL to progress or SSIS and Progress I would really like to hear about it but at this point it seems safer to use ABL so I'm not converting from CHAR to VARCHAR and back again.
Also if anyone uses SSIS to flat file and into progress any information about delimiters or things to watch out for would be useful.
Once again thanks for the responses, I'll post the method I eventually use just so there's a conclusion to this.