Solved Calling Stored Procedure with LONGVAR: Parameter Type?

Discussion forum about all things SqlBase.
shaunb
Switzerland
Posts: 2
Joined: 04 Feb 2020, 13:53
Location: Kreuzlingen

Calling Stored Procedure with LONGVAR: Parameter Type?

Post by shaunb » 11 Mar 2020, 14:36

If you have a column type of LONGVAR:
e.g.

Code: Select all

NAME COLTYPE
======== ========
MSGTEXT LONGVAR
What type of parameter do you need to specify in a stored procedure to receive it?

e.g.

Code: Select all

CREATE TRIGGER SYSADM."PROBLEM" AFTER INSERT ON SYSADM.PROBLEM_TABLE
(EXECUTE INLINE(MSGTEXT)
PROCEDURE P1 STATIC
Parameters
What-Type-Goes-Here : sMsgText
Local Variables
Actions
)
FOR EACH ROW;

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 274
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Calling Stored Procedure with LONGVAR: Parameter Type?

Post by Steve Leighton » 12 Mar 2020, 09:38

.
Use a RECEIVE LONG STRING: sMsgText e.g.

Code: Select all

PROCEDURE: sp_GetMsgText static
PARAMETERS
 RECEIVE LONG STRING: sMsgText
LOCAL VARIABLES
 Sql Handle: hSql
 Number: nFetched
ACTIONS
	!--------------------------------------------------------------------------------------
	!--Stored Proc returning a Long String
	!--------------------------------------------------------------------------------------
	On Procedure Startup
		Call SqlConnect(hSql)
	On Procedure Execute
			Call SqlPrepareAndExecute(hSql, '
							Select 	Your Long String Here
	    						From 	Your Table
							Into		:sMsgText' )
	On Procedure Fetch
		If SqlFetchNext(hSql, nFetched)
			Return 0
		Else
			Return 1
	On Procedure Close
		Call SqlDisconnect(hSql)
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

shaunb
Switzerland
Posts: 2
Joined: 04 Feb 2020, 13:53
Location: Kreuzlingen

Re: Calling Stored Procedure with LONGVAR: Parameter Type?

Post by shaunb » 17 Mar 2020, 13:50

Superb! Thank you, Steve.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests