Android Question [SQL] OUTPUT clause of the INSERT statement

[Blueforcer]

I need a return value for an INSTERT query, to get the primary key with Auto-increment for this query

strSQL = $"INSERT INTO tbl_sessions (game, note, time,user,friends) OUTPUT Inserted.session_id VALUES (?,?,?,?,?)"$
result = con.ExecQuery2(strSQL, Array As Object(game,note,time,userID,jp.ToString))

But i get an error

java.sql.SQLException: Can not issue data manipulation statements with executeQuery().

how to solve it?

 

[RB Smissaert]

I need a return value for an INSTERT query, to get the primary key with Auto-increment for this query

strSQL = $"INSERT INTO tbl_sessions (game, note, time,user,friends) OUTPUT Inserted.session_id VALUES (?,?,?,?,?)"$
result = con.ExecQuery2(strSQL, Array As Object(game,note,time,userID,jp.ToString))

But i get an error



how to solve it?

con.ExecNonQuery2

RBS

 

[Blueforcer]

con.ExecNonQuery2

RBS

ExecNonQuery2 doesnt return anything

 

[Mahares]

ExecNonQuery2 doesnt return anything

Yes, it does not return anything. You have to run the below code immeditely after the insert to get the rowid.

Dim MaxRec As Int 'or long
MaxRec=sql.ExecQuerySingleResult("SELECT last_insert_rowid() FROM mytable" )
Log($"Last rec: ${MaxRec}"$)

 

[Blueforcer]

Yes, it does not return anything. You have to run the below code immeditely after the insert to get the rowid.

Dim MaxRec As Int 'or long
MaxRec=sql.ExecQuerySingleResult("SELECT last_insert_rowid() FROM mytable" )
Log($"Last rec: ${MaxRec}"$)

ok thought i could use the output clause to minimize my queries

OUTPUT clause (Transact-SQL) - SQL Server

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.

docs.microsoft.com

 

[Alexander Stolte]

I had tried that the other day too, but then switched to last_insert_rowid.

    Dim DR As ResultSet = m_SQL.ExecQuery("SELECT last_insert_rowid() AS Appointments_Id;")
    Do While DR.NextRow
        Appointment.Id = DR.GetInt("Appointments_Id")
    Loop
    DR.Close

 

[Blueforcer]

Yep i also do this now.

btw for mysql it is :

Dim SessionID As Int = con.ExecQuerySingleResult("SELECT LAST_INSERT_ID() FROM tbl_sessions" )