Insert multiple records with a single call

J

joebananas

New Member
I can't believe this hasn't been asked before but I've searched and haven't found anything on this topic.

I'm just starting with OpenEdge 10.0B03 and I'd like to insert multiple records with a single insert statement like I do in MySQL

In MySQL I can do the following,

Insert Into Tablename (RecID, Data1, Data2) Values
(1, "abc", "1a2"),
(2, "bcd", "1b3"),
(3, "cde", "1c4"),
(4, "def", "1d5");

Is there any way to do the something equivalent in Progress?
 
Are you using the 4gl or SQL?

If you are using the 4Gl then the simple answer is "no". Certainly not in a single statement. The less simple answer is "maybe" - if the point is to insert multiple records within the scope of a single transaction rather than a single statement then the answer becomes "yes". Something like this:

Code: 
do for customer transaction:
  create customer.
  assign
    customer.name = "name1"
    customer.custNum = 1
  .
  create customer.
  assign
    customer.name = "name2"
    customer.custNum = 2
  .
end.

If you are using SQL make sure that you are using SQL-92 and not the embedded SQL-89 syntax that is kind of sort of alongside the 4GL. That way lies endless frustration,
 
Thanks for the reply Tom,

I using SQL but I don't understand what you mean by "make sure that you are using SQL-92 and not the embedded SQL-89 syntax". How do I specify which syntax to use? I'm writing a VB app that connects to an existing Progress system. I use OpenEdge Architect to test my SQL statements before I put them into VB.

Thanks for the help.
 
Well it is possible to write sql statements within the 4GL. Those statements are deprecated, but often confused with a real sql client.

Do you know that normal 4GL database trigger don't fire when you update a record through sql?

I assume you test the queries with the db navigator in OE architect.

Casper.
 
Yes, I'm using the db navigator in OE architect.

Please excuse me ignorance but I don't understand what your response has to do with my question.
 
Okay, the more digging I do the more frustrated I get. I think I found out that I'm not using ESQL and I am using SQL-92. But I have yet to be able to find a way to insert multiple rows in a single insert statement.

Does anyone have anything that can help me?
 
Please excuse me ignorance but I don't understand what your response has to do with my question.
Click to expand...

Well you asked:
I using SQL but I don't understand what you mean by "make sure that you are using SQL-92 and not the embedded SQL-89 syntax". How do I specify which syntax to use?
Click to expand...

and I tried to explain this.:-)

Back to your question:
the syntax for insert is:
Code: 
[LEFT]INSERT INTO customer (cust_no, name, street, city, state)
VALUES
(1001, 'RALPH', '#10 Columbia Street', 'New York', 'NY') ;[/LEFT]
 
[LEFT]or [/LEFT]
 
[LEFT]INSERT INTO neworders (order_no, product, qty)
SELECT order_no, product, qty
FROM orders[/LEFT]
WHERE order_date = SYSDATE ;

According to the sql reference:
The VALUES (...) form for specifying the column values inserts one row into the table.​
The query expression form inserts all the rows from the query results.

So it seems to be possible if you can make a query to retrieve the needed values.

HTH,

Casper.
 
Most of what I know about SQL can be summed up as "don't" ;)

If you are using VB to write your code then you are using SQL-92.

Casper's point about triggers is simple -- the db has two personalities. 4GL and SQL-92. Neither side respects the other's db triggers.

This is important because you are doing something very unusual -- you're writing code that uses SQL-92 to insert data. Most SQL-92 access to Progress databases is for reporting purposes. That isn't usually a big problem because read triggers are rare.

If I were the DBA supporting your application (or the Architect in charge of it) I'd be looking very suspiciously at your project. While it is possible to use SQL-92 to update a Progress db I personally do not feel that it is a good idea. I would far rather have you talking to the data access layer of a properly tiered architecture than directly coding INSERT statements. IMHO you're likely to cause all sorts of unanticipated locking, scalability and data integrity problems with your approach.
 
Thanks for the responses guys.

The INSERT...SELECT syntax only works if you have data in an existing table. If the user is entering new data in my app, specifically line items of an order, then the data doesn't exist in another table yet. The line item records are of all of the same type of data and they all apply to this order so it seems appropriate to insert them all at the same time. Inserting one at a time is very slow over slow WAN links. Inserting in a single call is much faster.

I am the DBA supporting the application and the Architect in charge of it so if there's a better way to handle this then I'm all ears. Is it possible to talk to the "data access layer" from an external app? If so how? I'm trying to convert an existing app to run on a new Progress based system. While I understand that eventually I'll rewrite this app in 4gl, I just don't have the time right now to go through the learning curve.

Thanks for the responses. I really appreciate the help. I'm under the gun to get this done and the pressure is intense.
 
To give architectural suggestions, we would need to know a little more about what is there and what skill sets are available. My first instinct on limited data would be to connect the two applications via a messaging system. Sonic, of course, is designed for this sort of thing, but I have also done simple things in the past using just sockets. You might need to consider some help on the ABL side, if you don't yet have any skills there, but I think you would be way ahead of the game compared to trying to beat your way there with SQL. Depending on the setup, AppServer might be another possibility, but messaging would be my first choice ... unless you tell us more and it points to something else.
 
You must log in or register to reply here.
Back
Top