Adam Glazier

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.

Attempt at replacing the second row / second column:

It returns errors saying FRED is an invalid parameter.

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

what-is-the-set-syntax-for-net-loadbang-sql

Quote: Adam Glazier wrote on Tue, 25 March 2008 18:09

----------------------------------------------------

Depending on your database setup, table names can be case sensitive. So maybe you need to do:

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: 

I am using Fred everywhere else to manipulate the database.

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)

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?

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?

That looks correct. You can double check at 

The only other thing I can think of is to try using table aliases. That looks like this:

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.

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...)

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:

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- 

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

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

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.)

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


What is the SET syntax for net.loadbang-SQL