This Question and Answer Set includes the following topics:
  • General Questions

  • General Questions
    Question:
    How do I create Read-Only classes or tables?
    Answer:
    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.
    Question:
    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.
    Answer:
    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 
    }
    
    Question:
    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?
    Answer:
    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