I figured it's time to ask another, possibly dumb question. I have a transactional procedure that is supposed to take 7 total columns and insert them into the database. I wanted to use this procedure so I could encapsulate the query in the transaction as well as cut down on some application code.

The parent is table1. Table1 will have one row per "aop_id", the PK. Table2 can have many rows associated with the corresponding row in table 1.

Here is an example of the data that needs to be stored.

table1
name
address
city
aop_id


table2
aop_id
phone
phone_type

Is there a way to loop the data in table2 using just SQL? Maybe a WHILE or FOREACH type of loop?

you mean a loop in MySQL to insert multiple rows? a loop isn't really necessary

all you need is the only looping you need to do is looping over your form fields in the input form, to construct the SQL statement

Well, that is great news, Rudy. I thought I would need to write a complex statement to handle this. I'm still a bit unclear on how to implement this because my procedure call looks like this:

For table1
CALL insertSubscription('Tom', '1234 Main St', 'Los Angeles', 41);

Then for table2 we still have the following 3 pieces of data that still need to stored. Without a loop of some sort, how will the database know how many times to iterate and when it should stop?

My brain is thinking like a programmer. Before I can enter anything into the database, I'd have to loop over the data and for each row, issue an insert statement. I think this is why it is a bit confusing for me because I don't see that in your sample code.

Will it matter that I have combined both of these insert queries into a single proc or should they be separated?

okay, number one, the database does not iterate, and number two, all it needs to process is an INSERT statement

so i'm not sure i understand what it is that you don't understand

did you see my INSERT statement for table2? did you see how it inserts 4 rows of data? without a loop?

yes, you do have to loop over the data, for as many times as you want rows inserted (in my example, 4 rows)

but there is only one INSERT statement, not four

you ~could~ do it with four separate INSERT statements, but that would be less efficient


no, a single proc is better


helps?

In your example, you provided 4 rows of data so I'm making the assumption that these rows were predetermined. If I were doing this in a programming language, I would loop over the user supplied data and and at the same time, feed those results into an insert statement. I have no way of knowing how many selections the user may have made and is why I use a loop. The loop stops when the data stops and quits issuing an insert statement to the database.

In this example, the user may choose 4 phone types or may only choose 1 but not less. Your SQL doesn't appear to loop so I was just confused as to how the database would know the total number of rows to insert.

The only other way I could see this working is by looping in my programming language and instead of issuing an insert statement like I normally would, I would just loop the procedure call instead. Yes?

Yes, overall it does help.

slightly wrong assumption -- they were constructed from four fields on the input form

as i said, yes, you have to "loop over" the form fields

becasue the INSERT statement is constructed by your procedure, one row at a time, as your procedure loops over the form fields

if the looping over the form fields discovers that there is only one filled in, then the INSERT statement, when it is finished being constructed, has only one row's worth of data in the VALUES clause

at the end of figuring out how many rows of dta there are, the INSERT statement -- remember, there's ony one of these -- is submitted

maybe what's confusing you is that you wrote your procedure to accept only one field, so you have to call it four times?

let me suggest that you look at my original INSERT statement, and imagine how you would construct it if there were four sets of data to be inserted

imagine that you were only going to submit the INSERT statement once, but that it could carry a variable number of rows of data, and you had to figure out how many of these rows to submit before you actually submitted anything

think like a programmer

Actually, I wrote the procedure to accept 7 fields. I was under the impression though that when writing a procedure, that the database was doing the looping. I say this because when I was at that horrific state job, there were procedures written that looped and inserted data at the same time.

Here is how I would handle that.

loop the data with my application code. and while the application code is looping over the data, the procedure would be inserting the data, one row for every row found, once for every loop where data was present.

[loop begin]
CALL myProc(var1, var2,var3,var4,var5,var6,var7);
[loop end]

In the proc, I would put the first 5 variables into the parent table INSERT statement and the remaining 3 in the child table INSERT. As the application code loop iterated over the data, the database would insert the current row.

Like an array?? If this is possible, this would be the way to go. Is it??

no

you're right to loop over the data, but not about inserting the data for evey row

during the loop, you just "collect" that data and use it to build the INSERT statement

you said there has to be at least one set of data to insert

so after the first loop, the INSERT statement looks like this --

INSERT INTO table2
( aop_id, phone, phone_type )
VALUES
( 42, '416 555-0937', 'home' )

then you loop again, and after the second loop, the INSERT statement looks like this --

INSERT INTO table2
( aop_id, phone, phone_type )
VALUES
( 42, '416 555-0937', 'home' )
,( 42, '647 555-1234', 'cell' )

notice you still haven't submitted it to the database yet

after the third loop, it looks like this --

INSERT INTO table2
( aop_id, phone, phone_type )
VALUES
( 42, '416 555-0937', 'home' )
,( 42, '647 555-1234', 'cell' )
,( 42, '905 555-6789', 'bar' )

and after the fourth loop, it looks like this --

INSERT INTO table2
( aop_id, phone, phone_type )
VALUES
( 42, '416 555-0937', 'home' )
,( 42, '647 555-1234', 'cell' )
,( 42, '905 555-6789', 'bar' )
,( 42, '212 555-1234', 'fax' )

then you realize there is no more data, so the looping ends

and now you finally submit the INSERT statement to the databas

there is only one INSERT statement, not four


take a look at this --

INSERT INTO table2
( aop_id, phone, phone_type )
VALUES
( 42, '416 555-0937', 'home' )
,( 42, '647 555-1234', 'cell' )
,( 42, '905 555-6789', 'bar' )
,( 42, '212 555-1234', 'fax' )

if you call the stuff in the VALUES clause an array, then the answer is yes

Thanks Rudy. That is a much better way of doing it.

I just tried to insert 4 rows and 3 of them failed because of the unique constraint. I am going to have to take your advice and prepare the data and INSERT statement before it goes over to the database. Sheesh.. nothing is easy anymore. Well, besides my ex wife, of course.

EDIT:

Rudy, let me ask you another question if I may.. Say I wanted to count these rows of data before sending them over to the database and then pass in that number to the database. Is there a way that I could compare the total number of executed inserts against the number that I calculated outside the database? ...and if not, throw an exception or something??

thanks

and since i ended up putting so much time and sweat into my explanation, perhaps you can tell me where i was deficient, why it took me so long to explain the concept, and, most importantly, how you would explain it to another developer so that it would be easier to understand than my effort

i mean, go back and look at post #2, now that you understand it, and explain why this didn't work for you right off the bat

Rudy, there is still something wrong and admittedly, I still don't understand. I thought I did last night but this still isn't working and seems impossible the way you are suggesting that I do this. Please let me state the question again because I know that I am not good at explaining exactly what I need sometimes.

I have a stored procedure that encapsulates two insert statements for two related tables. The first table is the parent to the second table. The child table PK references the parent table PK. The parent table can have 1 and only 1 row per customer. The child table however can have 1 or many associated rows to each row in the parent table.

In order to successfully enter data into both tables, keeping in mind the constraint, I must enter data into table1 and then into table 2. I fail to see how, when these two insert statements are encapsulated by a single SP that I am going to enter more than 1 row into table2. Because of the encapsulation of both insert statements, I am forced to provide the same information for table1 when inserting more then one row into table2. This fails.

Am I wrong??

sadly, yes

the problem lies entirely and completely within the scope of your programming logic inside the stored procedure

which of course you have not shared (yet), and which i will be unlikely to help you with as i have limited experience with stored procedure commands

i do not understand what you mean by "encapsulation" -- maybe the trouble lies here

there is absolutely no reason why a stored procedure couldn't issue the multi-valued INSERT statement (example as above, for 4 rows at once)

simply said, from a database perspective, your application logic inside the stored procedure is faulty

I understand. Maybe the logic is flawed after all. If you see anything that is not right, please let me know. The only possible way that I can see to successfully enter 4 rows of data into the phone table is to take out the phone insert statement from this SP and put it into its own SP and then call it for every row that needs to be entered.

What I meant by encapsulation was just that both of these INSERTs are contained within the same SP.

Just for reference, the PK for both of these tables is the phone column. This service is tied to their phone so phone seemed like the best way to go.

this stored proc does not allow you to pass in all phone numbers

you are hamstrung from the get-go

NOOOOOOOOooooo............!!!!!!


could you please show the context for the CALL

in other words, i want to see how you enter multiple phone numbers for a single user

Yes, I was leaning in that direction my own self.

I'm really glad that you think it is best not to remove the phone INSERT from the SP and put it into its own SP. It feels right to leave them together and I just need to figure out how to enter multiple rows of phone numbers without having the transaction fail. This was why I asked initially asked about a loop in mysql. I thought I could loop the phone and phone type for each user and be done with it.

This is the context of the call that I would use:

As for entering multiple phone numbers, in this configuration, it is not possible because it violates the key constraint for the user table. I cannot see any other way to handle this without removing the phone and phone type from this SP and putting it into its own SP. Again though, I don't think that is the "correct" way of doing it.

Thanks for helping me, Rudy.