How do I create Read-Only classes or tables?
If no users ever need to file into the table, you could mark the class
as abstract and it will be compiled as read-only.
If some users will need to file into the tables (but not all users),
and all users can read from the tables, you could just do:
GRANT SELECT ON * TO PUBLIC.
This would need to be done each time a new table is created.
If only some users need read access on all tables you could:
GRANT SELECT ON * TO Select_Role
And then GRANT Select_Role to the users who should have access. Again,
this would need to be done each time a new table is created.
If they have a lot of tables being created all the time, they could
put this GRANT code into a Stored Procedure or something which would make
it a little easier to call. Don't forget, the user who creates a table automatically
gets all privileges on the table.
I am working on porting the following stored procedure from MS SQL Server
(I have simplified it as follows) to Caché:
CREATE PROCEDURE UpdateName
@LastName varchar(35),
@FirstName varchar(25),
@NewKey int OUTPUT
AS
INSERT INTO NameTable (LastName, FirstName)
VALUES @LastName, @FirstName
SELECT @NewKey = @@Identity
I want to be able use our ID column in the same way that this example
was using an auto incremented column in SQL Server. I am not sure how to extract
the value of the ID column after the insert is made and I'm not sure how to
set an output parameter.
You could do something like using the DDL Create Procedure Statement:
CREATE PROCEDURE UpdateName(
INOUT pHandle %SQLProcContext,
LastName VARCHAR(35),
FirstName VARCHAR(25),
OUT NewKey INT)
LANGUAGE COS
{
Set NewKey=""
&sql(INSERT INTO NameTable (LastName, FirstName)
VALUES :LastName, :FirstName)
// %ROWID special variable contains the value of the ID column created
If 'SQLCODE Set NewKey=%ROWID
If $Get(pHandle)'=$$$NULLOREF
Set pHandle.SQLCode=SQLCODE,pHandle.RowCount=%ROWCOUNT
QUIT
}
Having set up a parent-child relationship between customers and orders,
I wanted to see the latest order date and the total order amount, so I used
the query:
SELECT ID, Name,
MAX(Order->Date),
SUM(Order->TotalAmount)
FROM Sales.Customer
where Date and TotalAmount are columns in the (child) Order table. This
almost works, it gives me the correct values, but it repeats each customer
once for each order present. Isn't it obvious that when I ask for Max and/or
Sum I only want a single value per customer?
Your query is returning the proper results that you requested, since
you are in essence performing a join on the parent and child tables, and then
processing the query based on the join, using some Cache extensions to SQL
(in standard SQL this query would be illegal, even without the -> syntax).
Your query is actually not even returning the right answer, since you want
the latest order and total per customer. What your query returns is latest
order and total over ALL customers, repeated for each customer order. There
are several ways to perform such a query, to get your desired results, as
described below:
SELECT ID, Name,
MAX(Order->Date),
SUM(Order->TotalAmount)
FROM Sales.Customer
GROUP BY Customer.ID
Or, using only standard SQL:
SELECT P.ID, P.Name,
(SELECT MAX(C.Date)
FROM Sales.Order AS C
WHERE C.CustomerID = P.ID),
(SELECT SUM(C.TotalAmount)
FROM Sales.Order AS C
WHERE C.CustomerID = P.ID)
FROM Sales.Customer AS P
Or (also using only standard SQL):
SELECT ID, Name,
MAX(Order.Date),
SUM(Order.TotalAmount)
FROM Sales.Customer, Sales.Order
WHERE customer.id = Order.CustomerID
GROUP BY Customer.ID