1. CJ Lai
  2. PowerBuilder
  3. Wednesday, 29 December 2021 21:07 PM UTC

Hi all

I cannot figure out how to compile a stored procedure (SP) in the database painter in PowerBuilder's IDE.

Could someone point me to where the steps are?

Thank you

CJ

John Fauss Accepted Answer Pending Moderation
  1. Thursday, 30 December 2021 16:43 PM UTC
  2. PowerBuilder
  3. # 1

Hi, CJ -

I don't have any experience using Adaptive Server Anywhere, but I will show you here what I've verified works with a SQL Server database.

In SQL Server Management Studio, here is an example of a script that drops, creates, grants execution rights and tests a stored procedure::

/* Destroy any existing version of the stored procedure... */
IF OBJECT_ID('sp_john') IS NOT NULL
   DROP PROCEDURE dbo.sp_john
go

/* Create the stored procedure... */
CREATE PROCEDURE dbo.sp_john
(@one    integer,
 @two    char(1),
 @three  varchar(20)
)
AS
BEGIN
/* The body of the stored procedure goes here...
   What follows is only an example.              */
DECLARE @result varchar(50)

SELECT @result = CAST(@one AS varchar(12)) + ', ' + @two + ', ' + @three

RETURN (1)
END
go

/* Grant execution rights on the stored procedure... */
GRANT EXECUTE ON dbo.sp_john TO public
go 

/* Test execution of the stored procedure... */
EXEC dbo.sp_john @one = 314159, @two = 'X', @three = '(unknown)'
go

Note the "go" statements at the end of each batch.

In ISQL pane of the PB Database Painter, you need to replace the "go" statements with a semi-colon:

/* Destroy any existing version of the stored procedure... */
IF OBJECT_ID('sp_john') IS NOT NULL
   DROP PROCEDURE dbo.sp_john
;

/* Create the stored procedure... */
CREATE PROCEDURE dbo.sp_john
(@one    integer,
 @two    char(1),
 @three  varchar(20)
)
AS
BEGIN
/* The body of the stored procedure goes here...
   What follows is only an example.              */
DECLARE @result varchar(50)

SELECT @result = CAST(@one AS varchar(12)) + ', ' + @two + ', ' + @three

RETURN (1)
END
;

/* Grant execution rights on the stored procedure... */
GRANT EXECUTE ON dbo.sp_john TO public
; 

/* Test execution of the stored procedure... */
EXEC dbo.sp_john @one = 314159, @two = 'X', @three = '(unknown)'
;

I hope this helps you determine a solution.

Best regards and wishes for a Happy New Year, John

Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Wednesday, 29 December 2021 22:14 PM UTC
  2. PowerBuilder
  3. # 2

Which DBMS?

Why not use the vendor's tool(s)? For example, SQL Server Management Studio for working with  Microsoft SQL Server.

I know that when connected to a SQL Server database from the PB Database Painter, you have to use ; {semi-colon) as the statement delimiter instead of the keyword "go".

Comment
  1. CJ Lai
  2. Thursday, 30 December 2021 15:43 PM UTC
Hi John

We connect to Adaptive Server Anywhere db via ODBC setting in PB's DB painter. I am not aware of any vendor's editor although I came across this RazorSQL online which is a commercial licensed software.

I tried to copy/paste one SP into the ISQL Session window in the painter and compiled it. I am not sure if that's the way to do it; that's why I am asking.



CJ
  1. Helpful
  1. Miguel Leeuwe
  2. Thursday, 30 December 2021 19:03 PM UTC
Doesn't ASA come with a tool called "Adaptive Server Studio"? If not then have a look at "DBeaver Community edition": https://dbeaver.io/ it's free.

Setup for sqlanywhere: https://learningintheopen.org/2019/07/24/dbeaver-sql-anywhere-database-connection-setup/

Or checkout "Flyspeed SQL", which has become pretty good over the last years and used to be free. If there's no longer a free version, it's still very cheap (like $40): https://www.activedbsoft.com/download-querytool.html

  1. Helpful
  1. Miguel Leeuwe
  2. Thursday, 30 December 2021 19:05 PM UTC
BTW, when you said you "copied the sql into the isql session and compiled it". Did it work? This is how I did it in the 90's when using Sybase System 10.
  1. Helpful 1
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.