My question is about JAL.

I'm trying to update a result set after several DatabaseSet commands.
I'm getting the message "Result set is not updateable, must be singleton select and the affected table must have a primary key"

My table does have a primary key, but what's a "singleton select"?

My select statement looks like "SELECT col1, col2, col3 FROM table WHERE condition" and I'm running DatabaseUpdate after DatabaseSet commands in a loop.

Thanks.

"singleton" means select from a single table.

Make sure you have entire primery key included in SELECT

: My question is about JAL.

: I'm trying to update a result set after several DatabaseSet commands.
: I'm getting the message "Result set is not updateable, must be singleton
: select and the affected table must have a primary key"

: My table does have a primary key, but what's a "singleton select"?

: My select statement looks like "SELECT col1, col2, col3 FROM table WHERE
: condition" and I'm running DatabaseUpdate after DatabaseSet commands
: in a loop.

: Thanks.

: "singleton" means select from a single table.

: Make sure you have entire primery key included in SELECT

I did include my select statement and it does have a single table. Furthermore, col1 IS the primary key. I still get the same message.

Who is the table owner? Is it in your or another schema?
Which database system do you use?
Do you connect using native database driver or ODBC?

: I did include my select statement and it does have a single table.
: Furthermore, col1 IS the primary key. I still get the same message.

: Who is the table owner? Is it in your or another schema?
: Which database system do you use?
: Do you connect using native database driver or ODBC?

I connect via ODBC using a system DSN. Connection works fine.
For the moment, I'm using DATABASEEXECUTE statement which works.
But DATABASEUPDATE would be more appropriate in my case.

But what about DBMS and schema/table ownership?

: I connect via ODBC using a system DSN. Connection works fine.
: For the moment, I'm using DATABASEEXECUTE statement which works.
: But DATABASEUPDATE would be more appropriate in my case.

: But what about DBMS and schema/table ownership?

I'm having a similar problem as what is referenced in this thread. I have a simple "SELECT * FROM table_name WHERE ref_id=1". I am using a DatabaseGet to pull one field, a DatabaseSet to change one field, and then a DatabaseUpdate. The account used by the ODBC connection is not the dbo, but has full permission on the table. I have this exerpt from the log:

JAL 183: Executing CONCATEX("SELECT server_id FROM server_ownership WHERE server_name='", "COL-1", "'", "")
JAL 184: DATABASERETRIEVE
JAL 184: Executing DATABASERETRIEVE("SELECT server_id FROM server_ownership WHERE server_name='COL-1'", "0")
JAL Return "1"
JAL 185: DATABASEGET
JAL 185: Executing DATABASEGET("1", "1", "")
JAL Return "197"
JAL 186: CONCATEX
JAL 186: Executing CONCATEX("SELECT * FROM drive_info WHERE server_id=", "197", "AND drive_letter='", "C", "'", "SELECT server_id FROM server_ownership WHERE server_name='COL-1'")
JAL 187: DATABASERETRIEVE
JAL 187: Executing DATABASERETRIEVE("SELECT * FROM drive_info WHERE server_id=197 AND drive_letter='C'", "1")
JAL Return "1"
JAL 188: ISEQUAL
JAL 188: Executing ISEQUAL("1", "0", "false")
JAL Return "false"
JAL 189: IFTHEN
JAL 189: Executing IFTHEN("false", "PROCESS_DRIVEINFO3")
JAL 190: DATABASEGET
JAL 190: Executing DATABASEGET("1", "1", "")
JAL Return "155"
JAL 191: DATABASESET
JAL 191: Executing DATABASESET("1", "4", "104171")
JAL
JAL 192: DATABASEUPDATE
JAL 192: Executing DATABASEUPDATE("UPDATE", "1")
JAL Return "1"

and the related portion of code:

concatex ("SELECT server_id FROM server_ownership WHERE server_name='", server_name, "'", sqlquery)

DataBaseRetrieve (sqlquery, rows)

DataBaseGet (1,1,server_id)

concatex ("SELECT * FROM drive_info WHERE server_id=", server_id, " AND drive_letter='", drive_letter, "'", sqlquery)

DataBaseRetrieve (sqlquery, rows)

isequal (rows, 0, norows)

ifthen (norows, PROCESS_DRIVEINFO3)

DataBaseGet (1,1,drive_info_id)

DataBaseSet (1,4,total_space)

DataBaseUpdate ("UPDATE", rows)

GoTo PROCESS_DRIVEINFO4

You must be either the table owner or be aliased to dbo in order for the system to figure out you have the permissions to update that table. Different databases systems use different methods and system tables where the security information is stored. Since there is no standard method to determine whether a table is updateable, 24x7 does not attempt to access that security information directly in the system tables and so the UPDATE option handling is based only on the user name and object owner name.

There was an enhancement request to make all database operations updateable by default vs. relying on the user/owner comparison. This request is still in a queue for the pending changes. The problem with this default is that in some databases certain operations can create unneeded transactions and possibly locks.

: I'm having a similar problem as what is referenced in this thread. I have a
: simple "SELECT * FROM table_name WHERE ref_id=1". I am using a
: DatabaseGet to pull one field, a DatabaseSet to change one field, and then
: a DatabaseUpdate. The account used by the ODBC connection is not the dbo,
: but has full permission on the table. I have this exerpt from the log: JAL
: 183: Executing CONCATEX("SELECT server_id FROM server_ownership WHERE
: server_name='", "COL-1", "'", "")
: JAL 184: DATABASERETRIEVE
: JAL 184: Executing DATABASERETRIEVE("SELECT server_id FROM
: server_ownership WHERE server_name='COL-1'", "0")
: JAL Return "1"
: JAL 185: DATABASEGET
: JAL 185: Executing DATABASEGET("1", "1", "")
: JAL Return "197"
: JAL 186: CONCATEX
: JAL 186: Executing CONCATEX("SELECT * FROM drive_info WHERE
: server_id=", "197", "AND drive_letter='",
: "C", "'", "SELECT server_id FROM server_ownership
: WHERE server_name='COL-1'")
: JAL 187: DATABASERETRIEVE
: JAL 187: Executing DATABASERETRIEVE("SELECT * FROM drive_info WHERE
: server_id=197 AND drive_letter='C'", "1")
: JAL Return "1"
: JAL 188: ISEQUAL
: JAL 188: Executing ISEQUAL("1", "0", "false")
: JAL Return "false"
: JAL 189: IFTHEN
: JAL 189: Executing IFTHEN("false", "PROCESS_DRIVEINFO3")
: JAL 190: DATABASEGET
: JAL 190: Executing DATABASEGET("1", "1", "")
: JAL Return "155"
: JAL 191: DATABASESET
: JAL 191: Executing DATABASESET("1", "4",
: "104171")
: JAL
: JAL 192: DATABASEUPDATE
: JAL 192: Executing DATABASEUPDATE("UPDATE", "1")
: JAL Return "1"

: and the related portion of code: concatex ("SELECT server_id FROM
: server_ownership WHERE server_name='", server_name, "'",
: sqlquery)

: DataBaseRetrieve (sqlquery, rows)

: DataBaseGet (1,1,server_id)

: concatex ("SELECT * FROM drive_info WHERE server_id=", server_id,
: " AND drive_letter='", drive_letter, "'", sqlquery)

: DataBaseRetrieve (sqlquery, rows)

: isequal (rows, 0, norows)

: ifthen (norows, PROCESS_DRIVEINFO3)

: DataBaseGet (1,1,drive_info_id)

: DataBaseSet (1,4,total_space)

: DataBaseUpdate ("UPDATE", rows)

: GoTo PROCESS_DRIVEINFO4

Thanks for the info. Due to security requirements, the account being used for database updates cannot be the table owner or aliased to dbo so I guess I'm stuck with using a DatabaseExecute with an UPDATE statement. Thanks again!

: You must be either the table owner or be aliased to dbo in order for the
: system to figure out you have the permissions to update that table.
: Different databases systems use different methods and system tables where
: the security information is stored. Since there is no standard method to
: determine whether a table is updateable, 24x7 does not attempt to access
: that security information directly in the system tables and so the UPDATE
: option handling is based only on the user name and object owner name.

: There was an enhancement request to make all database operations updateable
: by default vs. relying on the user/owner comparison. This request is still
: in a queue for the pending changes. The problem with this default is that
: in some databases certain operations can create unneeded transactions and
: possibly locks.