I'm using the BaseElements Plug-in to experiment with managing a simple Filemaker table named Nested_Category

consisting of four fields:

category_ID: number, autoenter serial

name: text

rgt: number

lft: number

result: text

I am executing SQL commands in two steps like this:

set variable[$mySQL; Value: "BE_FileMakerSQL (" & Quote("SELECT rgt FROM Nested_Category WHERE name = 'Televisions'") & ")"]

set field[Nested_Category::result; Evaluate($mySQL)]

This returns a value of 2 into Nested::Category::result, which is correct.



To INSERT a new record into the table I use the following calculation to set $mySQL  ($rgt is set to a numeric value prior to defining $mySQL)

BE_FileMakerSQL(" & Quote(

"INSERT INTO Nested_Category(name, lft, rgt) VALUES('Game Consoles'," & GetAsNumber($rgt+1) & ", " & GetAsNumber($rgt+2) & ")"

) & ")"

this works OK, and I get a new record in my table



However if I try to update a record  ($rgt is set to a numeric value prior to defining $mySQL)

"BE_FilemakerSQL(" &

Quote("UPDATE Nested_Category SET  rgt  =  rgt  + 2 WHERE rgt > " & GetAsNumber($rgt))

& ")"

the process fails and I get a ? returned as a result, which indicates the SQL command failed

I can't see why this should not work. Where am I going wrong?

Thanks

Brian

I think I have found the problem. I swopped the BaseElements plug-in for Draco Ventions SQLrunner. When executing the update step, SQLrunner reported 'error 301 - Record is locked by another user'

I inserted a Commit Records step before starting the update and the update step now completes successfully. This works with both BaseElements and SQLrunner, however SQLRunner scores because it reported the problem.

This leads to a some other questions about FQL

If you are working in a multi-user environment, can you lock a table whilst you are doing updates? I can't see LOCK or UNLOCK as reserved words in FQL

Can you string a series of SQL commands together, separated by semi-colons as you can in MySQL? I'd like to be able to do a set of commands such as

SEARCH...;

UPDATE...;

UPDATE... ;

INSERT..;

COMMIT

in sequence so I can set up a series on SQL management scripts that - for example, adds a record to a table

Thanks

Brian

SQL runner has an option "waitForIdle" Set =Yes and your update should work without the commit. Use this with CREATE TABLE & ALTER TABLE also.