1. Juan Pedro Sanz Diez
  2. PowerServer 2020 or older (Obsolete)
  3. Tuesday, 1 October 2019 10:40 AM UTC

 

Hello,

We have a  funcionality that creates dynamically DW the PB function SyntaxFromSQL, in this way

dwsyntax_str = sqlca0.SyntaxFromSQL(sqlsyntax, "style(type=grid)", s_err) 
messagebox('table',dwsyntax_str)

The messagebox is showed below for both web and cliente-server version.

 

The table is in a MS SQL Server Database.

 

I'm using Powerserver Appeon PowerServer 2019 Build 2151.00

Powerbuilder Universal Edition. Version 2019 Build 2082

 

So this sparks than in web version(Powerserver) we can't update the table since the SyntaxFromSQL function seems not to set dynamically  both the update and key flags.

 

Is this a lack of functionality?. I Have reviewed the Powerserver (PB Edition) help (below), and we haven't found any reference to this issue

Is there any workaround? Is there any property to set (or so) , to make it work in web version?

Thanks for your time.

Regards.

 

Juan Pedro  Sanz Díez

 

Powerserver (PB Edition) help.

Client-sever version

 

 

Web version (Powerserver)

mike S Accepted Answer Pending Moderation
  1. Tuesday, 1 October 2019 13:54 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 1

Use modify to set those values as required:

"columnname.Key= Yes"

"columnname.Update = Yes "

 

you may need to set the table as well:

DataWindow.Table.UpdateTable
DataWindow.Table.UpdateWhere

 
Comment
  1. mike S
  2. Tuesday, 1 October 2019 16:48 PM UTC
I believe that Juan is saying that the dwo DOES figure it out in PB, but it does NOT in powerserver.



imo, it should work consistently. So if PS doesn't do that then it should be reported
  1. Helpful
  1. Chris Pollach @Appeon
  2. Tuesday, 1 October 2019 17:04 PM UTC
Hi Mike;

Yes, if that's not the case in PowerServer - then a Support Ticket should be created.

Regards ... Chris
  1. Helpful
  1. Miguel Leeuwe
  2. Thursday, 3 October 2019 15:36 PM UTC
Far away memory: Not sure if the update had to be set manually when there was more than one table in the select. Also not sure if the behaviour was different depending on which database you were using.
  1. Helpful
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Tuesday, 1 October 2019 15:05 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 2

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

 

Comment
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Tuesday, 1 October 2019 15:15 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 3

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

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 2 October 2019 21:35 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 4

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

 

 

 

Comment
  1. Juan Pedro Sanz Diez
  2. Thursday, 3 October 2019 06:42 AM UTC
It sounds great!



I was implementing the Olan and Mike advice when I saw your message.



Only one question. What do you mean when you say ' removed the Primary key'. Where did you removed it? In database?



Thanks a lot for you, mike and Olan's help!!



  1. Helpful
  1. Chris Pollach @Appeon
  2. Thursday, 3 October 2019 14:27 PM UTC
Hi Juan;

Yes, I used my DBA tool to do that.

You can also use the PB IDE's DB Painter as well for that. ;-)

Regards ... Chris
  1. Helpful
There are no comments made yet.
Juan Pedro Sanz Diez Accepted Answer Pending Moderation
  1. Thursday, 3 October 2019 13:13 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 5

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'" )

Comment
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Thursday, 3 October 2019 16:13 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 6

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.

 

Comment
  1. Chris Pollach @Appeon
  2. Thursday, 3 October 2019 16:16 PM UTC
Hi Mike;

FYI: PowerServer will not work with SS using the ODBC driver.

Regards ... Chris
  1. Helpful
There are no comments made yet.
Juan Pedro Sanz Diez Accepted Answer Pending Moderation
  1. Friday, 4 October 2019 06:52 AM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 7

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

 

 

  

 

 

Comment
  1. Chris Pollach @Appeon
  2. Friday, 4 October 2019 13:55 PM UTC
Hi Juan;

When I look at your posted DWO source, it has been generated as PB version 8. So there is something very wrong if that is really the case. Looks like a PS "bug". I will open a support case for this.

Regards ... Chris
  1. Helpful
  1. Juan Pedro Sanz Diez
  2. Monday, 7 October 2019 14:57 PM UTC
Thanks!!!
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.