Состояние sql 23000 код ошибки 104

Ошибка вставки новой записи [РЕШЕНО]

Автор GOYa, 20 мая 2010, 11:14

0 Пользователи и 1 гость просматривают эту тему.

I know what the error means, and why it’s occurring, meaning this is not a duplicate of the many topics covering State 23000. I’m using PDO to insert a duplicate record into a table that has a compound PK. I did this on purpose, in order to automatically cancel the insertion of any duplicated records in to this specific table.

The error, SQLSTATE[23000]: Integrity constraint violation, is killing the script. Is there a way to set the warn level for this particular error, to literally tell it to shut up and carry on?

I don’t understand the purpose of having compound PKs if we can’t allow them to work for us?

I’m not very experienced with DB administration so you’ll have to forgive me if I come across a bit ‘it should work if I hit it with a hammer’.

Table Structure:

CREATE TABLE `cart` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `userid` int(10) NOT NULL,
 `itemid` int(10) NOT NULL,
 UNIQUE KEY `id` (`id`),
 UNIQUE KEY `unique_records` (`userid`,`itemid`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

Note the compound PK called ‘unique records’.

so I have this table:

| id | userid | itemid |
|  1 |    175 |     12 |

And I execute this query:

    $insertItem = $db->query("
                              INSERT INTO cart (userid, itemid) 
                              VALUES (:userid, :itemid), 
                              array("userid"=>"175", "itemid"=>"12")
                            ");

Executing this query prompts this error: SQLSTATE[23000]: Integrity constraint violation.

I fully understand why I’m getting this error — I just don’t understand why it’s killing my submission? I thought it should just keep working ignoring the fact that a few records weren’t inserted because they were duplicates?

Advice appreciated!

I know what the error means, and why it’s occurring, meaning this is not a duplicate of the many topics covering State 23000. I’m using PDO to insert a duplicate record into a table that has a compound PK. I did this on purpose, in order to automatically cancel the insertion of any duplicated records in to this specific table.

The error, SQLSTATE[23000]: Integrity constraint violation, is killing the script. Is there a way to set the warn level for this particular error, to literally tell it to shut up and carry on?

I don’t understand the purpose of having compound PKs if we can’t allow them to work for us?

I’m not very experienced with DB administration so you’ll have to forgive me if I come across a bit ‘it should work if I hit it with a hammer’.

Table Structure:

CREATE TABLE `cart` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `userid` int(10) NOT NULL,
 `itemid` int(10) NOT NULL,
 UNIQUE KEY `id` (`id`),
 UNIQUE KEY `unique_records` (`userid`,`itemid`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

Note the compound PK called ‘unique records’.

so I have this table:

| id | userid | itemid |
|  1 |    175 |     12 |

And I execute this query:

    $insertItem = $db->query("
                              INSERT INTO cart (userid, itemid) 
                              VALUES (:userid, :itemid), 
                              array("userid"=>"175", "itemid"=>"12")
                            ");

Executing this query prompts this error: SQLSTATE[23000]: Integrity constraint violation.

I fully understand why I’m getting this error — I just don’t understand why it’s killing my submission? I thought it should just keep working ignoring the fact that a few records weren’t inserted because they were duplicates?

Advice appreciated!

Have you used Set Term before and after this code?

All commands in Firebird must be terminated with a semi-colon. If you want to create a stored procedure you need to be able to distinguish between the terminating semi-colon from the semi-colons inside the stored procedure.

Something like this:

SET TERM ^ ;

CREATE PROCEDURE SP_NYANSAT(
        FORNAVN VARCHAR(30),
        EFTERNAVN VARCHAR(30),
        ADRESSE VARCHAR(50),
        POSTNUMMER CHAR(4),
        TELEFONNUMMER CHAR(8),
        EMAIL VARCHAR(50))
    AS
    DECLARE VARIABLE ID INTEGER;
    BEGIN
      ID = GEN_ID(GEN_ANSAT_ID,1);
      INSERT INTO MYTABLE (ID, FORNAVN, EFTERNAVN, ADRESSE, POSTNUMMER, TELEFONNUMMER, EMAIL) VALUES (:ID, :FORNAVN, :EFTERNAVN, :ADRESSE, :POSTNUMMER, :TELEFONNUMMER, :EMAIL);
    END
    ^

SET TERM ; ^ 

Please notice how the declaration of the stored procedure is terminated with ^, thus ending the statement. After the declaration you also restore the terminating semi-colon.

On a side note, I would recommend to copy firebird.msg to the location the error you get tells you so you can see what is really happening.

EDIT:

If you wish you can check this link. There you can find a lot of information regarding Firebird + IBExpress, including SET TERM (page 81).

EDIT 2:

Just tried at home with IBExperts + Firebird and I had no problem creating the stored procedure. My guess is you are trying to do one of the following things:

• You have opened an SQL editor and are trying to compile the code directly. That will not work because IBExperts thinks you are trying to run DSQL sentences. Stored procedures are created with PSQL sentences.

• You are trying to use the «New procedure» utility (check buttons in the upper right side of the main menu) and pasted the whole code into the editor. That will not work because in that editor you only have to put the body code. Stored procedure name is set in a field on the upper right side of the window you opened. Parameters and variables are introduced by using the «Insert Parameter/Variable» button on the left side above the code editor. The SET TERM sentences are created automatically by IBExperts. You can check the resulting code in the DDL tab.

HTH