NWNX -- ODBC -- Duplicate entry ** SOLVED **

[Legacy_Supreme_Pizza]

               I need to check for duplicate entry errors in my script.

I'm pulling from an auto increment DB to one that isn't.

So what I need to do is have an if

if (DUPLICATE_ENTRY_ERROR)
{
     check the number of items in the item count row and add one;
     overwrite that entry with the new value.
}

I can do the guts of the script but I don't know how to check for the error.

is it as simple as

int SQL_ERROR = 0;
int SQL_SUCCESS = 1;

if (SQL_SUCCESS = 0)
               
               

               

                     Modifié par Supreme_Pizza, 29 janvier 2013 - 12:20 .
                     
                  

            

[Legacy_Squatting Monk]

               You need to use primary keys in your new table. A primary key is a column that has to be unique. If you try to insert a new record using the same primary key, the insert will fail automatically.

You can have a primary key that encompasses multiple columns in a table, in which case their combination in a given record has to be unique. For example, here's a table that stores a player's unique ID along with a variable name and a value:

CREATE TABLE pcdata (PCID    varchar(10)  NOT NULL DEFAULT '0',
                     VarName varchar(64)  NOT NULL DEFAULT '',
                     Value   varchar(255) NOT NULL DEFAULT '',
                     PRIMARY KEY (VarName, PCID)";

Since VarName and PCID are both specified as the primary key, you cannot have more than one record with the same PCID and VarName. But lots of records for the same PCID but different VarNames (or vice versa) are okay. The database handles this automatically when you do an insert statement.

Make sense?
               
               

               

                     Modifié par Squatting Monk, 25 janvier 2013 - 09:04 .
                     
                  

            

[Legacy_Supreme_Pizza]

               Absolutely it makes sense.

Thanks for the answer too.

That is the issue. My primary keys match exactly.

They are supposed to do so.

Here is the scenerio so you have a proper understanding of what we are doing here.

Grogk walks into the bank and buys some stocks. 3 @ 6.000gp and 2 @ 50.000gp

Step 1:
Collect the player login. character name, tag of item, and number of items from DB "A"

Step 2:
Place that info in DB "B" and delete them from DB "A"

Step 3:
Run some dice roll calculations on them that change the blueprint resref

Step 4: (this is where it  mucks up)
Put the new items in DB "A"

as chance would have it Grogk now has 1 stock @ 6.000, 1 @ 60.000, 1 @ 50.000 and 2 @ 9,000

Everything writes back to the DB fine except the second 9,000gp stock because it is a duplicate entry -
Exactly the same as the first... as it should be... which is fine...
Except it throws a duplicate entry error.
No problem. I am fine with that.

However I don't want Grogk to lose his hard earned GP.
So I need to catch that error and if caught then I need to read from DB "A" the count of the duplicate entry items.
(in this case it is one, we were given that info but there may be multiples in the future.)

I don't have a problem pulling out the info I need or writing the new values.

The problem I have is finding out if the query was a success.

Given:
//////////////////////////////////////////////
int SQL_ERROR = 0;
int SQL_SUCCESS = 1;
////////////////////////////////////////////
from aps_include

Can I just check with this if statement:
////////////////////////////////////////////
if (SQL_SUCCESS = 0)
                       
///////////////////////////////////////////

or is there a better way to check for a duplicate entry error

or

is there such a thing as
//////////////////////////////////////////////////////////////////////
try()
{
    run function
}
catch()
{
   do something else when function fails
}
//////////////////////////////////////////////////////////////////////////

or

SqlQuery = "INSERT INTO vult_items SET ua_string = 'ua_string'"   
string result = mysql_query(

[code]SqlQuery [/code]);
if(!result){
    fix stuff here;
}

or


is there a way to use MySQL SQLSTATE

with
SIGNAL
               
               

               

                     Modifié par Supreme_Pizza, 25 janvier 2013 - 11:51 .
                     
                  

            

[Legacy_virusman]

               You can't have multiple primary keys. There can be only one primary key, what you're suggesting is a compound key. If you use compound key, you'll have to reference rows by all columns from the key.
Alternatively, you can just create a unique index, that'll serve the same purpose and won't affect how the primary key works.
               
               

               
            

[Legacy_Squatting Monk]

               

virusman wrote...

You can't have multiple primary keys. There can be only one primary key, what you're suggesting is a compound key. 

D'oh! Fixed.

Suepreme_pizza wrote...

Everything writes back to the DB fine except the second 9,000gp stock because it is a duplicate entry -
Exactly the same as the first... as it should be... which is fine...
Except it throws a duplicate entry error.
No problem. I am fine with that.

However I don't want Grogk to lose his hard earned GP.
So I need to catch that error and if caught then I need to read from DB "A" the count of the duplicate entry items.
(in this case it is one, we were given that info but there may be multiples in the future.)

I don't have a problem pulling out the info I need or writing the new values.

The problem I have is finding out if the query was a success.

Ah, I see.

SQL_SUCCESS and SQL_ERROR are both constants. They will always equal 1 and 0, respectively. You may be able to use SQLFetch() to see if SQLExecDirect() affected any rows, but I've never used it.

You could instead use back-to-back SELECT and INSERT (or UPDATE) queries. Better solution: ON DUPLICATE KEY UPDATE.
               
               

               
            

[Legacy_Supreme_Pizza]

               Wow

Forum is dropping posts,

Thanks!

All I have to do is add that to my existing query!
               
               

               
            

[Legacy_Squatting Monk]

               No, you just replied to a different topic by accident. '>
               
               

               

                     Modifié par Squatting Monk, 26 janvier 2013 - 09:30 .