Error performing Insert on an Android application: "Empty bindArgs"

Question

Explanation:

I have an android app that was made using the ADT (Android Developer Tools) I use the bench sqlite and eventually perform some operations such as insert or update and also some select's.

Problem:

When performing a particular insert after executing the .execSQL() in the bank I am taking the following error as an exception:

Empty bindArgs.

Code used:

Bank statement in onCreate():

SQLiteDatabase db_r3Att  = this.openOrCreateDatabase("r3Att.db3", SQLiteDatabase.CREATE_IF_NECESSARY, null );

Insertion code:

Cursor c = db_r3Att.rawQuery("select PEDIDO,REGISTRO,TIPOPED,NRNOTA,CLIENTE,NOME,ESPECIE,CONDICAO,OBSV1,OBSV2,TRANSMITIR,DATA,HORA,ENVIAR,SITUACAO,DBREGISTRO,EMPRESA,TROCA,SIMPLES,EBONIFICACAO,PERCDESC,VLRDESC,VLRLIQUIDO from PEDIDO_IMP" , null);
              c.moveToFirst();
              while( !c.isAfterLast() ){
                //incrementa o max
                cPedidoMax++;
                Integer cPedido            = c.getInt(c.getColumnIndex("PEDIDO"));
                Integer cRegistro          = c.getInt(c.getColumnIndex("REGISTRO"));
                String cTipoPed            = c.getString(c.getColumnIndex("TIPOPED"));
                String cNrNota             = c.getString(c.getColumnIndex("NRNOTA"));
                Integer cCliente           = c.getInt(c.getColumnIndex("CLIENTE"));
                String cNome               = c.getString(c.getColumnIndex("NOME"));
                String cEspecie            = c.getString(c.getColumnIndex("ESPECIE"));
                String cCondicao           = c.getString(c.getColumnIndex("CONDICAO"));
                String cObs1               = c.getString(c.getColumnIndex("OBSV1"));
                String cObs2               = c.getString(c.getColumnIndex("OBSV2"));
                Integer cTransmitir        = c.getInt(c.getColumnIndex("TRANSMITIR"));
                String cData               = c.getString(c.getColumnIndex("DATA"));
                String cHora               = c.getString(c.getColumnIndex("HORA"));
                Integer cEnviar            = c.getInt(c.getColumnIndex("ENVIAR"));
                Integer cSituacao          = c.getInt(c.getColumnIndex("SITUACAO"));
                Integer cDBRegistro        = c.getInt(c.getColumnIndex("DBREGISTRO"));
                Integer cEmpresa           = c.getInt(c.getColumnIndex("EMPRESA"));
                String cTroca              = c.getString(c.getColumnIndex("TROCA"));                
                String cSimples            = c.getString(c.getColumnIndex("SIMPLES"));                  
                String cBonificacao        = c.getString(c.getColumnIndex("EBONIFICACAO"));                 
                float cPercentualDesconto  = c.getFloat(c.getColumnIndex("PERCDESC"));                  
                float cValorDesconto       = c.getFloat(c.getColumnIndex("VLRDESC"));               
                float cValorLiquido        = c.getFloat(c.getColumnIndex("VLRLIQUIDO"));
                sb.append("INSERT INTO PEDIDO (PEDIDO,REGISTRO,TIPOPED,NRNOTA,CLIENTE,NOME,ESPECIE,CONDICAO,OBSV1,OBSV2,TRANSMITIR,DATA,HORA,ENVIAR,SITUACAO,DBREGISTRO,EMPRESA,TROCA,SIMPLES,EBONIFICACAO,PERCDESC,VLRDESC,VLRLIQUIDO,ID_PEDIDO_EXT)");
                sb.append(" VALUES ("+cPedidoMax+","+cRegistro+",'"+cTipoPed+"','"+cNrNota+"',"+cCliente+",'"+cNome+"','"+cEspecie+"','"+cCondicao+"','"+cObs1+"','"+cObs2+"',"+cTransmitir+",'"+cData+"','"+cHora+"',"+cEnviar+","+cSituacao+","+cDBRegistro+","+cEmpresa+",'"+cTroca+"','"+cSimples+"','"+cBonificacao+"',"+cPercentualDesconto+","+cValorDesconto+","+cValorLiquido+","+cPedido+");");
                db_r3.execSQL(sb.toString(),null);
                c.moveToNext();
              }
              c.close();

Important remark:

1 - I am absolutely sure that the select is working and is bringing the data and filling in the variables.

2 - I checked the syntax of insert and even tested run manually in the bank and worked.

So the error is in something I did and not in the syntax, so don’t worry about the giant string in the code there.

Question:

How to resolve this type of error?

Answer 1

Although in your question you say you’re using mysql, it seems to me that the access is through the class SQLiteDatabase, or am I mistaken? If so, according to the documentation of execSQL:

Executes a single SQL statement that is NOT a SELECT/INSERT/UPDATE/DELETE.

That is, this function is for things like creating and maintaining a scheme (at least second that answer in the SOEN). To execute a insert, it is necessary to use a specific function for this. Probably the function of the same name:

ContentValues valores = new ContentValues();
valores.put("PEDIDO", cPedidoMax);
valores.put("REGISTRO", c.getInt(c.getColumnIndex("REGISTRO")));
...
db_r3Att.insert("PEDIDO", null, valores);

See if this solves your problem. With the bonus of avoiding any injection of SQL that may result from the query as it is being done (or the fields c.getColumnIndex("TIPOPED") etc are "sanitized"?).

Observing: as noted, the version of execSQL with 1 argument only supports insert, but its documentation still recommends using a specific method when possible [for basic operations select, insert, update and delete]. I don’t understand all the reasons behind such a recommendation, but only the mentioned risk of SQL injection is already a good indicator...

Answer 2

I found the mistake.

Explanation:

It would be because I was using the function .execSQL() sending two parameters instead of one and sending null in the second just as I was doing in the .rawQuery() but the second parameter of .execSQL() cannot be null because it is an object bindArgs and must be completed if used.

Solution:

Would modify:

db_r3.execSQL(sb.toString(),null);

For:

db_r3.execSQL(sb.toString());