• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerServer
  • SQL statement fails when used with application deployed with PowerServer

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

Resolved SQL statement fails when used with application deployed with PowerServer

  1. Issue
0
Votes
Undo
  1. Hannu Pikkarainen
    Hannu Pikkarainen
  2. PowerServer
  3. Tuesday, 5 December 2023 16:18 PM UTC

Hi,

 

I have a problem with application deployed through PowerServer.

Following SQL statement works fine when used in IDE enterface : 

INSERT INTO rule_history Select *, :Allocation_id from rules
WHERE ( rules.rule_number = :saanto ) AND ( rules.dataareaid = :dataarea ) AND (rules.customer = :customernr) ;
vastaus2 = SQLCA.SQLErrText
if vastaus2 > "" then gnv_powermessage.Messagebox('SQL Database Error',vastaus2)
commit;

Buty when run on a client on workstation, it produces error:

 

I have scanned previous problems of this type and they suggest : do a full rebuild and deploy after that. It does not solve my problem.

BR, Hannu

 

 

Session creation Failed: Http Status Code: 503
Session creation failed. status code: 404
Responses (3)
mike S
mike S Accepted Answer Pending Moderation
  1. Tuesday, 5 December 2023 16:28 PM UTC
  2. PowerServer
  3. # 1

my guess is that it is because you are using:

select *

and also you are not specifying the columns to insert into.

 

generally, this is considered a bad practice since your tables could change and is also the columns are positionally dependent rather than by name.

Comment
Load more comments
Hannu Pikkarainen
  1. Hannu Pikkarainen
  2. Thursday, 7 December 2023 15:00 PM UTC
Here is the current SQL Statement :

INSERT INTO rule_history SELECT rules.account_a, rules.account_b, rules.dim1_a, rules.dim1_b,rules.dim2_a, rules.dim2_b, rules.dim3_a, rules.dim3_b,rules.dim4_a, rules.dim4_b, rules.dim5_a, rules.dim5_b, rules.dim6_a, rules.dim6_b, rules.dim7_a, rules.dim7_b, rules.dim8_a, rules.dim8_b, rules.percent_dest, rules.account_dest, rules.dim1_dest, rules.dim2_dest, rules.dim3_dest, rules.dim4_dest,rules.dim5_dest, rules.dim6_dest, rules.dim7_dest, rules.dim8_dest, rules.dataareaid, rules.recordid, rules.rule_number, rules.customer, rules.userid, rules.timestamp, :Allocation_id FROM rules

WHERE ( rules.rule_number = :saanto ) AND ( rules.dataareaid = :dataarea ) AND (rules.customer = :customernr) ;

vastaus2 = SQLCA.SQLErrText

if vastaus2 > "" then gnv_powermessage.Messagebox('SQL Database Error',vastaus2)

commit;
  1. Helpful
Hannu Pikkarainen
  1. Hannu Pikkarainen
  2. Thursday, 7 December 2023 15:02 PM UTC
I did a full rebuild of exe and then deployed the app to IIS, updated the application to workstation from there. Is there some kind of Web-Api deployment in the between?
  1. Helpful
mike S
  1. mike S
  2. Thursday, 7 December 2023 15:18 PM UTC
I would specify the columns you are inserting into. insert into table (column1, column2) select col2, col3 from table2 where x=y;



yes, the webapi must be deployed. your ps app talks to the webapi which talks to the database.
  1. Helpful
There are no comments made yet.
Francisco Martinez @Appeon
Francisco Martinez @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 5 December 2023 18:53 PM UTC
  2. PowerServer
  3. # 2

Hi Hannu,

 

This error typically occurs when you deploy a new Cloud App pointing to an old Web API version. When the PowerServer compiler is working through your project, it takes the ESQL it finds and moves it into the Web APIs (you can see them in the AppModels project in the Web APIs solution). To reference the ESQL then, a handle name is generated 'sqlhandle_####'. I'm not sure what exact procedure is being followed to determine the number sequence that gets appended to the handle name, but it changes when you modify/insert new ESQLs in your app. So, if you deploy your PS project into the sever, its Cloud App has some set of SQL handles, and it expects your Web APIs to have the exact same set. If this doesn't happen and there's a miss you get the sqlhandle not found error. Thus, my guess is that you need to deploy your Web APIs to make sure that the SQL Handles match the Cloud App, and make sure that the Application Pool is restarted so that they are reloaded.

 

Regards,
Francisco

Comment
Load more comments
Francisco Martinez @Appeon
  1. Francisco Martinez @Appeon
  2. Wednesday, 6 December 2023 15:47 PM UTC
Please make absolutely sure that you're also redeploying the Web APIs



Regards - Francisco
  1. Helpful 1
mike S
  1. mike S
  2. Wednesday, 6 December 2023 15:58 PM UTC
Make sure it is a full build.

make sure that either PB is deploying the web api to the correct server, or that you are doing so manually.
  1. Helpful
Hannu Pikkarainen
  1. Hannu Pikkarainen
  2. Thursday, 7 December 2023 15:03 PM UTC
I did a full rebuild of exe and then deployed the app to IIS, updated the application to workstation from there. Is there some kind of Web-Api deployment in the between?
  1. Helpful
There are no comments made yet.
Miguel Alzate
Miguel Alzate Accepted Answer Pending Moderation
  1. Wednesday, 13 December 2023 13:56 PM UTC
  2. PowerServer
  3. # 3

Hi, Hannu.

Try deploying the app with an EXE installer. This EXE also installs the API automatically, so there´s no need to deploy the Web API separately.

Comment
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.