What is the SET syntax for net.loadbang-SQL
I am having success with other SQL syntax, but am not able to get SET to work. I'm new to SQL, so it's probably something simple, but I've tried just about every permutation I can think of.
My Table:
TABLE FRED(SID INTEGER,I1 INTEGER)
INSERT INTO FRED VALUES(0,2)
INSERT INTO FRED VALUES(1,4)
INSERT INTO FRED VALUES(2,0)
Attempt at replacing the second row / second column:
update Fred SET i1=666 WHERE SID=1
It returns errors saying FRED is an invalid parameter.
Any ideas??
Quote: Adam Glazier wrote on Tue, 25 March 2008 18:09
----------------------------------------------------
> update Fred SET i1=666 WHERE SID=1
Depending on your database setup, table names can be case sensitive. So maybe you need to do:
update FRED SET i1=666 WHERE SID=1
I am using Fred everywhere else to manipulate the database.
Here is the error returned:
16923 [AWT-AppKit] ERROR net.loadbang.sql.mxj.NoddySQL - update
net.loadbang.sql.exn.OperationException: update
at net.loadbang.sql.Database.update(Database.java:172)
at net.loadbang.sql.mxj.MXJ_SQL.update(MXJ_SQL.java:115)
Caused by: java.sql.SQLException: Unexpected token: FRED in statement [Fred]
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.executeUpdate(Unknown Source)
at net.loadbang.sql.Database.update(Database.java:170)
... 1 more
• error: update: update
STATUS: 0 update
Meant to confirm above that I had tried FRED as all uppercase, as well as how I defined it originally.
I can query column entries and create new columns, but just can't figure out how to SET them. Is the correct syntax to SET a column entry?
update FRED SET i1=666 WHERE SID=1
Quote: Adam Glazier wrote on Tue, 25 March 2008 22:09
----------------------------------------------------
> Meant to confirm above that I had tried FRED as all uppercase, as well as how I defined it originally.
>
> I can query column entries and create new columns, but just can't figure out how to SET them. Is the correct syntax to SET a column entry?
> update FRED SET i1=666 WHERE SID=1
>
----------------------------------------------------
That looks correct. You can double check at http://hsqldb.org/web/hsqlDocsFrame.html
(see the SQL syntax chapter)
The only other thing I can think of is to try using table aliases. That looks like this:
update FRED f set f.I1=666 where f.SID=1
(change 'f' to anything you want...)
But aliases should be optional. It is probably something stupid staring us in the face. Just in case it is a case-sensitivity issue, also try I1 instead of i1 since you defined the table that way.
Another possibility is there is a bug in the net.loadbang.sql object. Maybe Nick can help.
On 26 Mar 2008, at 01:09, Adam Glazier wrote:
> Attempt at replacing the second row / second column:
> update Fred SET i1=666 WHERE SID=1
Yeah, this is a bit confusing: the first word of the message is
actually the Max command ("update" or "query"), and then the complete
SQL follows this, so the message looks like
update UPDATE Fred SET ... WHERE ...
The first "update" to Max is case-sensitive; the rest will depend on
the database. (I can't remember offhand whether MySQL is case-
sensitive or not.)
-- N.
Nick Rothwell - nick@cassiel.com - www.cassiel.com
--- open-source goodies for MaxMSP: Python, Groovy, Nixie Tubes,
--- rotatable text bricks, databases: all at www.loadbang.net