SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Result set not updateable

 
Reply to topic    SoftTree Technologies Forum Index » 24x7 Scheduler, Event Server, Automation Suite View previous topic
View next topic
Result set not updateable
Author Message
Nicolas Mercier-Gaboury



Joined: 17 May 2001
Posts: 3

Post Result set not updateable Reply with quote

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.

Thu May 17, 2001 3:57 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Re: Result set not updateable Reply with quote

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

Thu May 17, 2001 4:55 pm View user's profile Send private message
Nicolas Mercier-Gaboury



Joined: 17 May 2001
Posts: 3

Post Re: Result set not updateable Reply with quote

: "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.

Fri May 18, 2001 8:06 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Re: Result set not updateable Reply with quote

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.

Fri May 18, 2001 8:43 am View user's profile Send private message
Nicolas Mercier-Gaboury



Joined: 17 May 2001
Posts: 3

Post Re: Result set not updateable Reply with quote

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

Fri May 18, 2001 11:39 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Re: Result set not updateable Reply with quote

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.

Fri May 18, 2001 12:00 pm View user's profile Send private message
Jeremy



Joined: 06 Dec 2000
Posts: 17

Post Re: Result set not updateable Reply with quote

: 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

Tue Jun 26, 2001 11:18 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Re: Result set not updateable Reply with quote

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

Tue Jun 26, 2001 12:00 pm View user's profile Send private message
Jeremy



Joined: 06 Dec 2000
Posts: 17

Post Re: Result set not updateable Reply with quote

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.

Tue Jun 26, 2001 12:06 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » 24x7 Scheduler, Event Server, Automation Suite All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.