Need Help grabbing result from select, update, insert queries

Questions and Help
1

I am having trouble trying to figure out some queries with data blaze.

My goal is to see if a row exists, and if it does, just update the row. If it doesn't, then insert a new row. I have 2 scenarios which I have tried but the documentation is lacking on a few elements or I am just missing it.

My first try was to read the db to see if a row exists, and if it doesn't, then insert a new row, if it does, then update. This works, but I dont need or want a form to pop up on the read. I just want to pass if the SELECT was successful or not (or if the row exists), and use it as a condition to decide the next action.

(this are simplified for the post)

{id={clipboard}}
{dbselect: SELECT user_id FROM Userlist WHERE user_id = @user_id; space=1Sh7eQMLS3dHzazQFwCYsU; menu=no; trim=yes; haserror=error}
{if: error}
// no row exists: Insert New Row
{else}
// row exists: Update Existing Row
{endif}

I really dont care for the popup form on the Select since I only ever do 1 at a time and i specifically copy the id right before hand to pull or update information.

The other method I tried because I don't want the form to pop up is to just try and update a row, and if it fails, to insert instead.

{user_id="7587504885"; trim=yes}
{user_name="Danner"; trim=yes}

// I want to try to update first
{dbupdate: UPDATE Userlist
SET user_name=@user_name
WHERE user_id = @user_id; space=1Sh7eQMLS3dHzazQFwCYsU; autoaddfields=no; trim=yes; completed=(res, status) -> "ERROR" if res <> 200 else ""}

// Then insert if it fails.
{if: error} <--I need to catch the result to go here on if the update was a success or not.
// row doesn't exist: Insert New Row
{endif: trim=yes}

I just have no idea how to grab the result or status of if this update query completed. I tried iserror(), catch(), . This method doesn't bring up a form which I like, but I can't figure out how to use "completed" properly nor find documentation for it that has examples of proper use.

Can someone help me figure this out? Or point me towards another solution? I rather not have the for popup if at all possible from the read, so I prefer the second option. I appreciate any guidance here!

2

Hi @Phillip_Danner
Try this:
I put the DBselect in a {note} and also read the rowid(), then I followed the same approach as your first snippet.

{formtext: name=first name; default=Bob}
{note}{dbselect: SELECT rowid() AS `Row ID`, Name, Count FROM Table1 WHERE Name=`@first name`; space=id; menu=no;haserror=error}{endnote}
{if: error}
{dbinsert: INSERT INTO Table1 SET Name=`@first name`, count=3; space=id; autoaddfields=no}
{else}
{dbupdate: UPDATE Table1 SET Name=`@first name` WHERE rowid() = `@Row ID`; space=id; autoaddfields=no}
{endif}

3

Thanks this helps and I got the logic sorted out. I still wish that a read query had the option to not pop up the form window all the time but since it does, I decided to try and utilize it anyways. The putting it in a note just makes sure the form popup is blank but I do like being able to hide some of the info that I dont need with {note} and prevent insertion.

Additional though, I think some complex query examples would be a good addition to the documentation. :slight_smile: