How to use SCOPE_IDENTITY in sqlite

Question

I have a mobile application that inserts a BD registration and I need to return the ID of this registration, but I do not know how to use and I do not know if it works SCOPE_IDENTITY in sqlite, my code is 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);

Answer 1

Let’s go in order of importance: never, never, never construct an SQL query by 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 never used Phonegap, and the documentation seemed terrible, but if I understood correctly, the second parameter of the function executeSQL is a list; the elements of that list are used to fill in the ? in the query itself, and the Phonegap that turns to put quotes in whatever you need to put quotes.

---

Second, every SQL dialect I know uses only simple quotes to demarcate strings. You’ll 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 on Sqlite. On the other hand, by phonegap documentation, the 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 in place 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 in the new window that will appear).

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

In addition to not appearing to the user and avoid this type of incident when you give console.log of an object, both in Chrome and Firefox, you can click on it to see the type of the object and its properties, which is much more useful than the [object BlaBlaBla] that appears when you do 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);