Hi, I'm looking to develop my first web API in Powerbuilder 2019R2 to replace some aging PHP applications. Our PHP applications create a lot of dynamic sql (generally the from and where clauses) based on the arguments passed from the client. I'm looking to replicate this dynamic creation of SQL with SqlQueryBuilder. Is that the preferred way to go? Or is it possible to create dynamic SQL with the new .Net DataStore?
My other question regarding the SqlQueryBuilder is whether it can load an existing SQL statement that I can then modify, or whether I need to create it from scratch by specifying everything?
I'll read up a bit more on the methods you mention above. Basically I like to start with a raw sql statement, but i want a structured way to modify it. I've started looking at the SqlModelMapper today, but I can't yet see a way to make a query across databases. E.g. with a sql statement similar to:
SELECT title = p.title,
status = p.status,
reference = p.reference,
owner = dbo.Owner_function(p.id)
FROM properties p,
php..filter f
WHERE p.id = f.id
AND f.account_id = 100
The filter table is in a different database to the properties table. I can't work out to use the FromTable and\or the SqlAndWhere attributes to allow a cross database join. Is that possible with the SqlModelMapper or SqlQueryBuilder?
SqlModelMapper and SqlQueryBuilder haven't provided a structured way to specify the server name and database name of a table source. So you need to use a raw SQL statement way to build and execute the across databases query.
Please report an enhancement requirement in the Appeon Standard Support at https://www.appeon.com/standardsupport/.
Regards,
Logan
That saved me some time trying to find out why it wasn't working. I found a partial workaround to use a table from a different database with SqlModelMapper. I can do it either using a synonym in the database or by using a sub select that references the other database table in the SqlWhere attribute of the model.
I'm not sure if I will use either approach, I'm still just trying to get a feel for what the best method will be for my project. Thanks again for your help.
Regards,
Daryl.