Android Question [SQL] OUTPUT clause of the INSERT statement

Blueforcer · May 13, 2022

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

B4X:

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 · May 13, 2022

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

B4X:

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 · May 13, 2022

RB Smissaert said:
con.ExecNonQuery2

RBS

ExecNonQuery2 doesnt return anything

Mahares · May 13, 2022

Blueforcer said:
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.

B4X:

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

Blueforcer · May 13, 2022

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

B4X:

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 · May 13, 2022

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

B4X:

    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 · May 13, 2022

Yep i also do this now.

btw for mysql it is :

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

Android Question [SQL] OUTPUT clause of the INSERT statement

Blueforcer

Well-Known Member

RB Smissaert

Well-Known Member

Blueforcer

Well-Known Member

Mahares

Expert

Blueforcer

Well-Known Member

OUTPUT clause (Transact-SQL) - SQL Server

Alexander Stolte

Expert

Blueforcer

Well-Known Member

Similar Threads

Android Question [SQL] OUTPUT clause of the INSERT statement

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency