Insert or replace does not work

Asked

Viewed 75 times

1

The command

INSERT OR REPLACE INTO SYSVAL ( CDDEVICE, DTVALINI, DTVALFIM, 
SALDO, REALTIME, VALIDADO ) VALUES( '34322910071833580','31-08-2016',
'31-08-2016','10','63714560','S')

works smoothly on Sqlitestudio, but does not work when run by the application on Android.

Although it does not cause any error, the table remains empty.

I changed the insertion code for something like this:

        Boolean result = false;
        ContentValues values = new ContentValues();
        values.put("CDDEVICE", valores[0].toString());
        values.put("DTVALINI", valores[1].toString());
        values.put("DTVALFIM", valores[2].toString());
        values.put("SALDO", valores[3].toString());
        values.put("REALTIME", valores[4].toString());
        values.put("VALIDADO", valores[5].toString());


        try{
            db.insertOrThrow("SYSVAL", null, values);
            result = true;
        }
        catch(Exception e)
        {
            Log.e("DB ERROR", e.toString());
            e.printStackTrace();
        }

And then it worked just fine.

Could someone tell me why the first command isn’t working?

The structure of the SYSVAL table is as follows:

sqlString = "CREATE TABLE IF NOT EXISTS SYSVAL (" +
                    "    CDDEVICE   VARCHAR (25) NOT NULL," +
                    "    DTVALINI   DATE," +
                    "    DTVALFIM   DATE," +
                    "    SALDO      INT," +
                    "    REALTIME   NUMERIC(15)," +
                    "    VALIDADO   VARCHAR(1)," +
                    "    CONSTRAINT SYSVAL_PK PRIMARY KEY ( CDDEVICE )  "+
                    ");";

I’m running the command that doesn’t work that way:

 public Boolean executacomando( SQLiteDatabase db, String comando  )
    {
        Boolean result = false;

        try {
            db.rawQuery( comando,  null );
            result = true;
        }
        catch (Exception e)
        {
            Log.e("DB Error", e.toString());
            e.printStackTrace();
        }

        return result;
    }

The method returns true normally, but Insert does not happen.

android sqlite

  • The method insertOrThrow() is not equivalent to INSERT OR REPLACE INTO

  • He posted insertOrThrow() as test and demonstrate that by this method it works, @ramaral

  • @Reginaldorigo, post the table structure. Post the DDL of the table.

  • @Danielomine if there is a record with the same primary key it will give error.

  • yes.. needs to have a single primary key.. but he comments that in sqlitestudio works.. So it may be that the test environment structure is different from the one in the app.. difficult to determine without the details

  • You have to show how you used the INSERT OR REPLACE INTO in the code.

  • Thank you for your time. I made the requested changes.

  • The code to create the table in Sqlitestudio is the same one I’m using to create the table in the application.

  • I don’t know why it doesn’t work, but the Sqlitedatabase method is equivalent to INSERT OR REPLACE INTO is replace

  • It would really be interesting if there is UPSERT in sqlite. = D

  • I think I figured out where I’m eating ball.

  • It worked. I changed the code that was wrong.

Show 7 more comments

1 answer

1

To execute the command INSERT OR REPLACE INTO we should use db.execSQL( comando ) and not db.rawQuery( comando, null ); like this in the code.

Browser other questions tagged android sqlite

You are not signed in. Login or sign up in order to post.