Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved SqlQueryBuilder Question

  1. Advice
0
Votes
Undo
  1. Daryl Foster
    Daryl Foster
  2. SnapObjects
  3. Monday, 25 May 2020 08:16 AM UTC

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?

How to Make SnapObject 2.0 Use run the query from ...
SqlModelMapper Issue
Responses (2)
Daryl Foster
Daryl Foster Accepted Answer Pending Moderation
  1. Monday, 25 May 2020 09:18 AM UTC
  2. SnapObjects
  3. # 1

Actually, I just found this knowledge base article which I think answers some of my questions:

https://www.appeon.com/developers/get-help/knowledgebase/how-reuse-model-different-queries.html

But I'm still keen to hear other people's suggestions.

 

Comment
There are no comments made yet.
Logan Liu @Appeon
Logan Liu @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 26 May 2020 05:52 AM UTC
  2. SnapObjects
  3. # 2

Hi Daryl,

To operate data after retrieving data (similar to DataWindow), you can choose .NET DataStore and use GetSqlSelect() and SetSqlSelect() to modify the SQL.
To retrieve data based on a model, you can choose SqlModelMapper and SqlBuilder.
To use raw SQL to retrieve data directly, you can choose SqlExecutor and SqlBuilder.

SqlQueryBuilder hasn't supported to parse an Existing SQL statement to create a new SqlQueryBuilder object.

It would be better if you can provide some pseudocode to tell us more details about your needs.

Regards,

Logan

Comment
Daryl Foster
  1. Daryl Foster
  2. Tuesday, 26 May 2020 08:46 AM UTC
Thanks Logan,



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?
  1. Helpful
Logan Liu @Appeon
  1. Logan Liu @Appeon
  2. Tuesday, 26 May 2020 15:31 PM UTC
Hi Daryl,

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
  1. Helpful
Daryl Foster
  1. Daryl Foster
  2. Wednesday, 27 May 2020 08:18 AM UTC
Thanks 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.
  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.