Hi. 

We're using the native driver for all our apllications, as it can be seen below

On the other hand, there is only one table in the query, as you can see in the screenshots of the main post for both powerserver and clientserver version. 

select * from consumo_agua

In the database, the table has the primary key.

I select all the key columns, since the query is a select *

It works fine in PB for client server version, as it always did. But for the first time we have tried this functionaty in powerserver it doesn't works:

It puts all columns tabsequence = 0

Not key and update column properties  to yes.

not update and updatewhere table properties to yes.

It powerserver it has access to the INFORMATION_SCHEMA, since I make the select to get the primary key of the table in the code. And it works...

This is de Describe("DataWindow.Syntax")) after changing the properties by code in powerserver version. Updatekeyinplace gets automatically when I changed any of the others . I didn't set this property....

that all folcks

Glad you got it working.  However, this should work without you having to set keys, updateable, etc.

I do the same thing that you are doing, also in SQL Server, but i am on an older version of powerserver.  However, Chris confirmed that it does work in 2019..

As Miguel said, if you have more than 1 table in the sql then it will NOT set any of this.  It also would not work if your table doesn't have a primary key defined in the database, or if you did not include all the primary keys in the select statement.  the fact that you are saying that it works with PB and same exact thing doesn't work in PS is odd.

So, it might be the database driver that you are using?  Powerserver does have an ODBC option that you could setup with sql server, but you should be using the native driver with sql server in PS.  what driver did you setup with PS?

Also, possibly the permissions for you PS database connection might possibly prevent PS from getting the column/table info.  I doubt it, but it might be something to look into.  I am fairly confident that PB does not use the newer system views for that information, and PS probably works the same exact way.

Finally I have done a mix.

Step 1.- changed Update and tabsequence column properties to let user modify the data column

Step 2.- Searched the key columns and autoidentity ones in the marvelous INFORMATION_SCHEMA for set key columns and not let change the autoidentity, if any.

Step 3. Changed both updatetable and updatewhere properties

It works fine!!

Thanks Mike, Chris and Olan

Long fila_ds, filas_ds
integer pos_where, pos_from, n_objeto
string claves_primarias, msg_error,tabla, column
string objetos, objeto, tipo_objeto

//Paso 1: Actualizamos el actualizado y la secuencia de todas las columnas, pera que se pueda escribir.

objetos = dw_maestro.Describe('DataWindow.Objects') // Lista de objetos
objetos += '~t' // Adiciona un TAB al final para tomar hasta el último control

Do While Pos(objetos, '~t') > 0
n_objeto++
objeto = Left(objetos, Pos(objetos, '~t') - 1) // Nombre del control
if dw_maestro.Describe(objeto+'.Type') = 'column' then
dw_maestro.Modify(objeto + ".Update= Yes")
dw_maestro.Modify(objeto + ".tabsequence=" +string(n_objeto * 10))
end if
objetos = Mid(objetos, LenA(objeto) + 2) // Actualiza lista de objetos
loop

//Paso 2: Buscamos la clave primaria, para actualizar.

pos_from = Pos(upper(consulta),"FROM") + 4 //4: longitud del from
pos_where = Pos(upper(consulta)," WHERE ")

if pos_where = 0 then pos_where = len(consulta)

tabla = trim(mid(upper(consulta), pos_from, pos_where - pos_from))

claves_Primarias = " SELECT KU.table_name as TABLENAME,column_name as PRIMARYKEYCOLUMN, " + &
" COLUMNPROPERTY (OBJECT_ID('" + tabla + "'),COLUMN_NAME ,'IsIdentity') AS ident " + &
" FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC " + &
" INNER JOIN " + &
" INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU " + &
" ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND " + &
" TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND " + &
" KU.table_name='" + tabla + "'" + &
" ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION "

datastore ds_claves
ds_claves = create datastore

if uf_id_createds(ds_claves,claves_Primarias,"") < 0 then
msg_error = "Error al crear el datastore en sintaxis"
return -1
end if

ds_claves.SetTransObject(sqlca)
filas_ds = ds_claves.retrieve()

for fila_ds = 1 to filas_ds
column = ds_claves.getitemstring(fila_ds,'PRIMARYKEYCOLUMN')
dw_maestro.Modify(column + ".Key= Yes")
if ds_claves.getitemnumber(fila_ds,'ident') = 1 then
dw_maestro.Modify(column + ".tabsequence= 0")//El autonumérico, no dejamos modificar.
end if
next

dw_maestro.Modify('DataWindow.Table.UpdateTable = "' +tabla + '"')
dw_maestro.Modify ("DataWindow.Table.UpdateWhere= '1'" )

Hi Juan;

   I finally had some time to run a PowerServer 2019 test on the Dynamic DWO & its SQL generation. The DWO was built with the correct primary key as the update vehicle. When I called the Update in the PS Web App, the ID primary key was being used (as I expected).

PS Log (after DW Update command called):

My Code Snippet:

String       ls_sql  = "  SELECT id, last_name, first_name, title, street, city, state, zip, phone, fax   FROM contact  ;"
String        ls_dwo
String        ls_error
String        ls_presentation = "Style(Type=grid)"

ls_dwo    =    SQLCA.syntaxfromsql ( ls_sql , ls_presentation, ls_error )
IF  IsNull ( ls_dwo ) = TRUE or ls_dwo = "" THEN
    MessageBox ( "Create of Dynamic DWO Failed!", ls_error )
    Return
END IF

dc_data.Create( ls_dwo )
dc_data.Modify ("DataWindow.Table.UpdateWhere='2'" )              //  Key and Modified Columns
dc_data.SetTransObject( SQLCA )
dc_data.Retrieve ( )

Note: When I removed the Primary key, the Dynamic DWO then indeed used the unique index instead (as a native PB App would).

HTH

Regards ... Chris

In Oracle:

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'TABLE_NAME'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

// Ensure that TABLE_NAME is in all CAPS!

Olan

get the primary key from the database.  INFORMATION_SCHEMA is your friend

here are some queries i found using google since i am too lazy to write them myself:

https://stackoverflow.com/questions/3930338/sql-server-get-table-primary-key-using-sql-query

Use modify to set those values as required:

"columnname.Key= Yes"