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

Resolved PowerServer 2022 Support for multiple DBMS

  1. How-to
0
Votes
Undo
  1. Mike Kolenda
    Mike Kolenda
  2. PowerServer
  3. Tuesday, 14 November 2023 21:13 PM UTC

Our software has been developed to support both MS SQL Server & ORACLE depending on the client using the software. With PowerServer 2020 we had both SQL Server and ORACLE set up in our configuration (Database Type section) so the output would support either DBMS. This solution had served us well for years.

Since beginning our migration to PS 2022 we have been focused on MSSQL, but we have noticed several syntax and object errors in the Errors tab after deployment. We've assumed this is the case since we have not setup an ORACLE DB on the Database page of the PS Project. 

Upon inspection the Database page of the PS project, it has not been obvious how to utilize this to support both DBMS.

Can a PowerServer support both DBMS at the same time in the same project (possibly the DB connection profile) or do we need to create 2 separate projects?  If it can be done within one project, can you provide some guidance on how this can be accomplished.

 

Regards, 

Mike

  1. Database
  2. PowerServer
How can I get an argument when my program run on P...
Web API connecting to different DBs
Responses (4)
Francisco Martinez @Appeon
Francisco Martinez @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 14 November 2023 21:34 PM UTC
  2. PowerServer
  3. # 1

Hi Mike,

Have you taken a look at Cache Groups? It feels to me that this is the problem this feature seeks to address.

 

Regards,
Francisco

Comment
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Tuesday, 14 November 2023 22:17 PM UTC
  2. PowerServer
  3. # 2

you can setup the 2 different databases in the project's database setup section.  you can also set them up at runtime.  it depends on what you are trying to do.  is this application being installed for each customer at their site? 

 

it sounds like your issue is that you have oracle specific syntax in a pbl that you are deploying to powerserver and you are using sql server?  and PS hits that and generates errors during generation?  you can have errors and it can still run, depending on what is going on.  If you have things like oracle specific datawindows that use oracle only syntax (NVL for example) then that stuff you can probably just ignore since that datawindow wouldn't be loaded/run anyway.  (would be better to convert your datawindows to work in all databases - so replace NVL with coalesce, etc).  I'm not 100% sure whether it fully builds if it hits those sorts of errors, but i do have errors where an object is missing and PS deploys and runs fine.  so my guess it would work for you too.

In any case, how you developed your software can make a big difference on how well that will work.  We do similar, and we use sql syntax that works in all databases we support (sql server, oracle, postgres, ase, sqlanywhere).  We use views and sql functions for database specific stuff that we can't use ansi standards for.  because of that we never had any issues with powerserver getting confused on oracle vs sql server

 

 

Comment
Mike Kolenda
  1. Mike Kolenda
  2. Tuesday, 28 November 2023 15:15 PM UTC
Thanks for the input, Mike.



The software is in-house (SaaS) and may be installed the customers site.



Overall we've been have been using SQL syntax that works in all DBs we support, but over time DMBS specific syntax has been utilized to resolved various things. My goal is to get one deployment & package to serve both SQL Server and Oracle. It looks like I can use Confection Profiles (project's database setup section) to resolve this, but I've other started at looking at options recently.



  1. Helpful
mike S
  1. mike S
  2. Tuesday, 28 November 2023 15:55 PM UTC
For any dbms specific syntax (since it generates the webapi for a specific database) move that stuff to the database (view, stored procedure, function) that has the same name and everything in all databases. For example, if you use length for oracle you can create an oracle function LEN and use that instead since that is what sql server uses. This is how we handle most differences between databases (views and functions mainly).



- OR -



have that syntax generate at runtime. For example, you can create a datatwindow using sql syntax at runtime. Doing it that way avoids the web api generation and is being done only at runtime. Execute immediate of a sql string is another example of that.

  1. Helpful
There are no comments made yet.
Mike Kolenda
Mike Kolenda Accepted Answer Pending Moderation
  1. Tuesday, 14 November 2023 22:14 PM UTC
  2. PowerServer
  3. # 3

Thanks for the quick reply, Francisco.  Is Cache Groups and DB Connection Profiles the same thing?  It appears they both utilize Applications.json.  I don't recall this section (DB Connection Profiles) being in the projects for initial builds of PS 2022. I assume this is the GUI to manage the Applications.json instead of the manual way shown in the link you provided.  I will look into setting the DBParm with the cachegroup. I'm not sure what implications it has at this pint.

 

- Mike

 

 

Comment
There are no comments made yet.
Logan Liu @Appeon
Logan Liu @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 15 November 2023 03:18 AM UTC
  2. PowerServer
  3. # 4

Hi Mike,

In the PowerServer's current C# solution, it can only generate one C# model for a datawindow object (either for SQL Server or Oracle database). If you have many datawindow objects that can work for both Oracle and SQL Server databases. You may easily get a C# datatype mismatch issue (e.g.: Different execution results in different databases - - PowerServer 2022 R2 Help (appeon.com)) during runtime. 

I suggest that you create 2 separate projects using different database configurations and build C# solutions to different paths. You will also need to deploy two PowerServer Web API applications.

(If want to use only one project, you can create two DB connection profiles (Cache Groups) for SQL Server and Oracle. But you have to remember to click the "Set as Current" to switch the profile before Build and Deploy. This is not better than using 2 projects.)

You can submit an enhancement requirement via our support ticketing system to ensure it is being properly received by our tech support and tracked at: https://www.appeon.com/standardsupport/

Regards, Logan

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 15 November 2023 03:41 AM UTC
Hi Logan;

I agree ... You can see those aspects in my latest STD Framework and in particular, the OrderEntry Demo App. In this latest release, I modified the Demo App to work with SQL Anywhere, PostGreSQL, & SQL Server concurrently as the PB Demo DB is now available in those 3 DBMS variations.

So when you compile an PS App though against SA but run it against SS or PG you get "some" SQL errors here & there. The errors also vary by DBMS in different places within the App

However, if you run the Demo App in C/S or PowerClient mode, then all the DWO's DML execution work OK as the DWO's adjust the SQL generated at runtime. Something that PS does not do.

So I would agree with your assessment that you need a PS App deployment per DBMS to ensure DML compliance with the corresponding Web APIs per DBMS.

Regards.. Chris
  1. Helpful
Mike Kolenda
  1. Mike Kolenda
  2. Tuesday, 28 November 2023 15:27 PM UTC
Thanks, Logan & Chris.

I know we have potential date v datetime datatype issues, so the C# model generation did concern.



As far are as the "Set as Current" option goes, I'm not sure I understand why it is not better than using 2 projects. Is this due to both deployments placing the C# solutions to the same location? Or is there some other fallback I'm missing.



- Mike



- Mike
  1. Helpful
Logan Liu @Appeon
  1. Logan Liu @Appeon
  2. Wednesday, 29 November 2023 01:16 AM UTC
Hi Mike,

Yes. The C# model will be saved in the same location. Each time you change the "Set as Current" option (e.g.: from SQL Server profile to Oracle profile), you have to Build and Deploy again to generate the correct C# model for this database. It will cost more time in this process. So I suggest that you create two separate PowerServer projects and make sure they are not using the same C# solution.

Regards, Logan
  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.