How to use SCOPE_IDENTITY in sqlite

1

I have a mobile application that inserts a database in the DB and needs to return the ID of this register, but I do not know how to use it and I do not know if SCOPE_IDENTITY works in sqlite, my code looks like this:

var idSolicitacao = tx.executeSql('INSERT INTO ouvidoria10 (assunto,mensagem,endereco,anexo,status, id_Departamento) 
                    VALUES ("' + document.getElementById("InputAssunto").value
                        + '","' + document.getElementById("InputMensagem").value
                        + '","' + document.getElementById("InputEndereco").value
                        + '","' + document.getElementById("ArquivosAnexados").value
                        + '","No Mobile",0);SELECT SCOPE_IDENTITY()');
        alert(idSolicitacao);
    
asked by anonymous 08.06.2015 / 15:03

1 answer

2

Let's put it in order of importance: never, ever, never construct an SQL query concatenating strings unless you know exactly what you are doing. Do this:

var idSolicitacao = tx.executeSql('INSERT INTO ouvidoria10 (assunto, mensagem, endereco, anexo, status, id_Departamento) 
                    VALUES (?, ?, ?, ?, "No Mobile", 0); SELECT SCOPE_IDENTITY()', [
    document.getElementById("InputAssunto").value,
    document.getElementById("InputMensagem").value,
    document.getElementById("InputEndereco").value,
    document.getElementById("ArquivosAnexados").value,
]);
alert(idSolicitacao);

I've never used PhoneGap, and the documentation seemed awful, but if I understood correctly, the second parameter of the executeSQL function is a list; the elements of this list are used to populate the ? in the query itself, and the PhoneGap that turns to put quotation marks wherever you need quotation marks.

Secondly, every SQL dialect I know of uses only single quotes to mark strings. You will need to adjust your query:

var idSolicitacao = tx.executeSql("INSERT INTO ouvidoria10 (" +
    "assunto, mensagem, endereco, anexo, status, id_Departamento) " + 
    "VALUES (?, ?, ?, ?, 'No Mobile', 0); SELECT SCOPE_IDENTITY()", [
    document.getElementById("InputAssunto").value,
    document.getElementById("InputMensagem").value,
    document.getElementById("InputEndereco").value,
    document.getElementById("ArquivosAnexados").value,
]);
alert(idSolicitacao);

Third, SCOPE_IDENTITY() is a unique function of SQL Server, which does not work in SQLite. On the other hand, by PhoneGap documentation , executeSQL returns an object with an attribute insertId , which is exactly the ID you want.

var resultSet = tx.executeSql("INSERT INTO ouvidoria10 (" +
    "assunto, mensagem, endereco, anexo, status, id_Departamento) " + 
    "VALUES (?, ?, ?, ?, 'No Mobile', 0)", [
    document.getElementById("InputAssunto").value,
    document.getElementById("InputMensagem").value,
    document.getElementById("InputEndereco").value,
    document.getElementById("ArquivosAnexados").value,
]);
var idSolicitacao = resultSet.insertId;
alert(idSolicitacao);

Fourth, I recommend you use console.log instead of alert to debug your code - you will need to open the JavaScript console (in Chrome or Firefox, just right click anywhere on the page and choose "Inspect Element", the console is one of the tabs of the new window that will appear).

A person I know (who is not me, did not even work with me, before you ask) put a alert('fodeu!!') into the code, but forgot to take it, and put the code into production; he only discovered when a user called the desperate support proclaiming that the system was talking about who fucked up. : P

In addition to not showing up for the user and avoiding this kind of incident, when you give console.log of an object, both in Chrome and Firefox, you can click it to see the object type and its properties, which is much more useful than the [object BlaBlaBla] that appears when you alert() .

var resultSet = tx.executeSql("INSERT INTO ouvidoria10 (" +
    "assunto, mensagem, endereco, anexo, status, id_Departamento) " + 
    "VALUES (?, ?, ?, ?, 'No Mobile', 0)", [
    document.getElementById("InputAssunto").value,
    document.getElementById("InputMensagem").value,
    document.getElementById("InputEndereco").value,
    document.getElementById("ArquivosAnexados").value,
]);
var idSolicitacao = resultSet.insertId;
console.log(idSolicitacao);
    
09.06.2015 / 01:04