Alternative to query within loop?

Databases
1

Hello, all,

Somewhere in another post, I had mentioned that I hate using a query within a loop to insert or update (or even select) iterative data in a database. I said that I would rather eat a bullet than do that.

Unfortunately, some databases are set up to deny chained queries within one db connection.

Is there an alternative to looped queries if the database is configured to deny chained queries? Specifically Oracle database?

V/r,

:slight_smile:

2

[quote=“WolfShade, post:1, topic:196384, full:true”]Is there an alternative to looped queries if the database is configured to deny chained queries? Specifically Oracle database?
[/quote]no idea what chained queries are, but avoiding a loop is easily done using joins

you got an example you need converted?

3

An example. Let’s say you have a form with one field for a company name, one for company address, one for city, one for state, one for zip - but you could have anywhere from 1 to 5 fields for some kind of federal ID number.

You insert the company information (without the federal ID) into one table (tableA), then you have another table for the federal ID that is associated with the server-generated UUID of the company that was just inserted (tableB).

tableA

companyUUID       companyName       address     city      st   zip
1234-asdf         Acme LLC         123 Main St  Yourtown  MN   65432

tableB

federal_id_uuid     companyUUID     federalID
4567-erty           1234-asdf       676767
4567-poiu           1234-asdf       454545
4578-yhng           1234-asdf       232323

A chained query would be something like:

{connect to database with credentials}
INSERT into tableB(federal_id_uuid, companyUUID, federalID)
VALUES(sysguid(), '1234-asdf', '676767');
INSERT into tableB(federal_id_uuid, companyUUID, federalID)
VALUES(sysguid(), '1234-asdf', '454545');
INSERT into tableB(federal_id_uuid, companyUUID, federalID)
VALUES(sysguid(), '1234-asdf', '232323');
{disconnect from database}

The semi-colons separate each individual query but it’s all done in one connection. Versus a looped query which would be:

{connect to database with credentials}
INSERT into tableB(federal_id_uuid, companyUUID, federalID)
VALUES(sysguid(), '1234-asdf', '676767')
{disconnect from database}

{connect to database with credentials}
INSERT into tableB(federal_id_uuid, companyUUID, federalID)
VALUES(sysguid(), '1234-asdf', '454545')
{disconnect from database}

{connect to database with credentials}
INSERT into tableB(federal_id_uuid, companyUUID, federalID)
VALUES(sysguid(), '1234-asdf', '232323')
{disconnect from database}

V/r,

:slight_smile:

4

If I understand you correctly, the appropriate Oracle syntax would be:

INSERT ALL 
       INTO tableB(federal_id_uuid, companyUUID, federalID) VALUES(sysguid(), '1234-asdf', '676767')
       INTO tableB(federal_id_uuid, companyUUID, federalID) VALUES(sysguid(), '1234-asdf', '454545')
       INTO tableB(federal_id_uuid, companyUUID, federalID) VALUES(sysguid(), '1234-asdf', '232323')
2 Likes
5

I will have to look up INSERT ALL. I didn’t realise you could do that. If it isn’t a chained command, that just might work!

Thanks!

:slight_smile:

6

It shouldn’t be as it’s one single operation - I’m guessing the chained restriction is an attempt to prevent sql injection? Either that or someone didn’t handle the timing/order of operations and so they put the restriction in place. But this is basically a controlled SELECT INTO syntax/form.

7

Precisely. There are all kinds of restrictions in place, all in the name of preventing SQL injection or other database-related security potential issues.

V/r,

:slight_smile:

8

I looked up INSERT ALL, and have one question that the tutorial didn’t explain.

According to this page, after the INSERT ALL you add SELECT * FROM DUAL; as the last line. Is there a reason for this?

V/r,

:slight_smile:

9

Since I haven’t used Oracle in eons, I had to look up the equivalent syntax to the statement I’ve used in SQL Server.

So I had to do another search. Apparently, the INSERT ALL requires a SELECT statement, so using the dual allows for that syntax without adding crap data you don’t want. So yes, you’ll need it, but it won’t actually do anything other than satisfy the syntax requirement.

1 Like
10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.