-
Sathishkumar S
- PowerServer
- Monday, 3 February 2025 06:58 PM UTC
Hi All,
I am currently working on implementing PowerServer for our application, which is transitioning from a client-server setup to a PowerServer-based deployment.
Environment:
- PowerBuilder: 2022 R3 (Build 3397)
- Database: SQL Server 2019
During testing, I encountered multiple issues, which I have categorized as follows:
1. Defaulting Stored Procedure Parameters
I have a stored procedure as shown below:
CREATE PROCEDURE dbo.usp_getname
@id INT,
@dept_id INT = 1, --> defaulted
@sec_id INT = NULL, --> defaulted
@location_id INT = 1, --> defaulted
@up_date DATETIME2(3) = NULL --> defaulted
In the client-server setup, I can execute it in multiple ways, such as:
EXEC dbo.usp_getname @id = 5
or
EXEC dbo.usp_getname @id = 5, @up_date = '2025-01-01'
However, in PowerServer, the generated SQL statement (captured using SQL Profiler) is:
EXEC sp_executesql N'EXEC dbo.usp_getname @id', N'@id FLOAT', @id=1003 -- Working fine
But when executing with the datetime parameter:
EXEC sp_executesql N'EXEC dbo.usp_getname @id, @up_date', N'@id FLOAT, @up_date DATETIME2(3)', @id=1003, @up_date='2025-01-01'
I get the following error:
Msg 206, Level 16, State 2, Procedure dbo.usp_getname, Line 0 [Batch Start Line 21]
Operand type clash: datetime2 is incompatible with int.
Since I have hundreds of DataWindows (DWs) using similar stored procedures with defaulted parameters, is there a way to handle this issue efficiently?
2. Expressions Used in Insert and Update Stored Procedures
I use expressions in stored procedure inserts and updates. However, I am facing issues with PowerServer. Is there a way to make it work the same as in the client-server setup?
3. Session Logout Issue
I am experiencing frequent session disconnections. I tried adding LongConnection=1 in DBPARM, but I am unsure if this affects the transactions. Any insights?
4. Unable to Save Issue
In PFC, when using SQLCA.Commit(), I receive the following error:
"Failed to commit because there is no available database connection or transaction."
(Note: We are using AutoCommit = True.)
Although the data is successfully saved in the database, the application still shows a commit failure. How can this be resolved?
5. Testing and Debugging Changes in PowerServer
If I make any corrections in the code, such as modifying a DataWindow or making logical changes, how can I test or debug them in PowerServer?
When I debug from the PowerServer project, it seems that DataWindow changes are not taking effect. It appears that I can only see or debug the changes after building and deploying the project.
I appreciate any guidance or suggestions on these issues.
Thanks in advance for your help!
Thanks & Regards,
Sathishkumar S.
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.