MERGE or UPDATE/INSERT conditions for SQLite3 with "github.com/mattn/go-sqlite3"

[Stéphane phenetas]

Hello,

I am trying to implement a command that UPDATE a value in a table if the value exist, or INSERT the value if the value does not exist at that moment.

I have searched for how to do that, and I have come with this command "

"UPDATE devicesInfos SET statusStack=statusStack+2, lastSeen="+ a +" WHERE macAddress="+ b +" AND statusStack<4;

IF @@ROWCOUNT = 0 INSERT INTO 

devicesInfos(macAddress,currentStatus,statusStack,firstSeen,lastSeen)

VALUES ("+ c +",1,3,"+ d +","+ e +")"

The program is compiling, it runs but the database is not updated. So I am pretty sure this is this command that is faulty.

And the only "new" thing I am using here is the @@ROWCOUNT, so I am not sure if I am using it right ? Or if the library matte/go-sqlite3 can handle it ?

Thank you.

[Tamás Gulácsi]

You can move the logic into Go: do the update, then check the result's AffectedRows(), then do the insert if needed.

[Kiki Sugiaman]

Tamás' suggestion should fix it for you. In addition;

On 20/08/15 11:07, Stéphane phenetas wrote:
> Hello,
>
> I am trying to implement a command that UPDATE a value in a table if
> the value exist, or INSERT the value if the value does not exist at
> that moment.
>
> I have searched for how to do that, and I have come with this command "
>
> "UPDATE devicesInfos SET statusStack=statusStack+2, lastSeen="+ a +" WHERE macAddress="+ b +" AND statusStack<4;
> IF @@ROWCOUNT = 0 INSERT INTO
> devicesInfos(macAddress,currentStatus,statusStack,firstSeen,lastSeen)
> VALUES ("+ c +",1,3,"+ d +","+ e +")"

If there's a chance that user input might make it into your query, you
should use query placeholders. Even if not, it's a good habit that costs
you nothing.

>
> The program is compiling, it runs but the database is not updated. So I am pretty sure this is this command that is faulty.
> And the only "new" thing I am using here is the @@ROWCOUNT, so I am not sure if I am using it right ? Or if the library matte/go-sqlite3 can handle it ?

Drivers don't try to make sense of your query, only its results.

>
>
> Thank you.
> --
> You received this message because you are subscribed to the Google
> Groups "golang-nuts" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to golang-nuts...@googlegroups.com
> <mailto:golang-nuts...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Stéphane phenetas]

Hi and thank you for your kind answers.

So I have done a :

sqlCommand := fmt.Sprintf("UPDATE devicesInfos SET statusStack=statusStack+2, lastSeen="+ timeNow +" WHERE macAddress="+ sliceOfDevices[i][1] +" AND statusStack<4") result, _ := db.Exec(sqlCommand)
         
 numberOfModifiedRows, _ := result.RowsAffected()
 if numberOfModifiedRows == 0 { do the INSERT here }

I get an error 

goroutine 1 [running]:
main.updateDatabase(0xc20805a000, 0x38, 0x40)
	/Users/stephane/GoCode/src/deviceDiscoveryV2/main.go:52 +0x4a4
main.main()
	/Users/stephane/GoCode/src/deviceDiscoveryV2/main.go:25 +0x1e0

goroutine 17 [syscall, locked to thread]:
runtime.goexit()
	/usr/local/go/src/runtime/asm_amd64.s:2232 +0x1
exit status 2

The line 52 is numberOfModifiedRows, _ := result.RowsAffected()

[Stéphane phenetas]

I forgot this at the beginning of the error message :

panic: runtime error: invalid memory address or nil pointer dereference

[signal 0xb code=0x1 addr=0x28 pc=0x40e2ed4]

[Tamás Gulácsi]

1. You're still not using placeholders in the SQL string!
2. You're not checking the returned errors! Please check your errors, every time!

[Stéphane phenetas]

Really sorry about that, I have read your first comment and thank you for this but I am beginning and not sure for the moment how to use and what are placeholders. So I wanted to deal with only one problem at a time and finish my command which is causing me troubles.

I have checked the errors and they are no errors :

numberOfModifiedRows, err := result.RowsAffected()

if err != nil {

   log.Fatal(err)

}

Concerning the placeholders, how is this supposed to work. Can I replace this :

UPDATE devicesInfos SET statusStack=3, lastSeen="+ timeNow +" WHERE macAddress="+ sliceOfDevices[i][1]

By this :
UPDATE devicesInfos SET (statusStack,lastSeen) VALUES (3, "+ timeNow +" ) WHERE macAddress="+ sliceOfDevices[i][1]

[Matt Harden]

The documentation demonstrates clearly how to use placeholders: http://golang.org/pkg/database/sql/#DB.Query

--

You received this message because you are subscribed to the Google Groups "golang-nuts" group.

To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.

[Stéphane phenetas]

Thank you Matt, so if I do it right it should be this :

sqlCommand := fmt.Sprintf("UPDATE devicesInfos SET statusStack=?, lastSeen=? WHERE macAddress=?")

result, err := db.Exec(sqlCommand, "3", timeNow, sliceOfDevices[i][1])

[Matt Harden]

Kind of, but notice - you no longer need fmt.Sprintf. You can just replace it with the quoted string. Which is a good thing because it means you're immune to SQL injection attacks now, not to mention better performance, lower system utilization, and other benefits. You can also Prepare() this statement ahead of time and Exec() the prepared statement multiple times, which saves on parsing time within the database.

--