cannot insert null error

[rbragg]

$queryInsertParent4Student = "
INSERT into schema.STUDENT (parent_num)
SELECT parent_num
FROM schema.PARENT
WHERE email = '" . $_SESSION['pMail'] . "'";
Why does the above give me this error:

ORA-01400: cannot insert NULL

The session is tested and set AND there IS a parent_num for this email in the db? Huh, I think I'm just being silly here. Thanks.

[TJ111]

phpIt's because you are calling it like two seperate queries. If I'm interpreting this right, you need to set the VALUE of the INSERT using information pulled from another field. If that's the case, you'd want something like this:

$queryInsertParent4Student = "
INSERT into schema.STUDENT (parent_num)
VALUES (
SELECT parent_num
FROM schema.PARENT
WHERE email = '" . $_SESSION['pMail'] . "')";

[chazzy]

$queryInsertParent4Student = "
INSERT into schema.STUDENT (parent_num)
SELECT parent_num
FROM schema.PARENT
WHERE email = '" . $_SESSION['pMail'] . "'";
Why does the above give me this error:

ORA-01400: cannot insert NULL


Simple, that's not the line that's giving you an error. it's the line after it that's running the query.

As for why, why not try to debug it? Maybe add this line after that line to see what's actually going on.

$queryInsertParent4Student = "
INSERT into schema.STUDENT (parent_num)
SELECT parent_num
FROM schema.PARENT
WHERE email = '" . $_SESSION['pMail'] . "'";
print("The query is: ".$queryInsertParent4Student );

[rbragg]

TJ111, I've read in some manuals that you can actually replace the values function with a select statement.

Hi Chazzy! Right now I've resorted to SQL Plus and running the hard coded query there:


INSERT INTO schema.STUDENT (parent_num)
SELECT parent_num
FROM schema.PARENT
WHERE email = 'censored@email.com';

censored@email.com IS in my PARENT table. SQL Plus gives me:

ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCHEMA"."STUDENT"."ID")

ID is my primary key and we both know that, of course, the primary key can't be null. The problem is, I'm NOT trying to insert into ID ... only parent_num. *scratches head*

[TJ111]

I know you can, but you have to use the 'VALUES' keyword as I did in my example.

[rbragg]

Here is some documentation about REPLACING values with a select statement:

http://www.techonthenet.com/sql/insert.php
http://www.1keydata.com/sql/sqlinsert.html
http://www.sqlteam.com/article/using-select-to-insert-records

If you try to use the VALUES keyword, you will receive a missing expression error. ;)

[TJ111]

Hmm, my apologies. Lack of coffee = off the mark lol. Just for debugging, do this and see what it returns.

$queryInsertParent4Student = "
SELECT parent_num
FROM schema.PARENT
WHERE email = '" . $_SESSION['pMail'] . "'";

//assuming MySQL, change it for whatever
$result = mysql_query($queryInsertParent4Student) or die (mysql_error());
print_r(mysql_fetch_assoc($result));
die();

[rbragg]

No problem. :) Remember that I'm in an Oracle server environment. We have error reporting turned off. I pasted the error found on Oracle Enterprise Manager above. Please read my reply to Chazzy as this gives a lot of insight to the error.

[TJ111]

Just a few things I can think of, I'm no expert on Oracle DB (though it shouldn't be that much of a difference). Is the id column set to suto_increment? What happens if you 'USE schema; INSERT INTO STUDENTS ...'?

[rbragg]

Is the id column set to suto_increment?
Yes, it is. However, that shouldn't matter because I'm not trying to insert into the ID column. I'm only trying to insert into the parent_num column.

[chazzy]

Yes, it is. However, that shouldn't matter because I'm not trying to insert into the ID column. I'm only trying to insert into the parent_num column.

Are you using a sequence + trigger to set the id value?

[rbragg]

Yes, it is an auto incremented sequence.

[rbragg]

Oh dear - I just realized the problem and I'm a little embarrassed! :o Obviously, my goal is to update the student table ... not insert. Inserting assumes that a new record is being added and requires the primary key. :o

So, anyway ... Now I'm trying to figure out if it is possible to update with a select. Would this work?

$queryInsertParent4Student = "
UPDATE schema.STUDENT
SET parent_num =
(SELECT parent_num
FROM schema.PARENT
WHERE email = '" . $_SESSION['pMail'] . "')
WHERE id = " . $_SESSION['id'];

[rbragg]

That did, indeed,:D work.