Calling Stored Proc in MS SQl Server 2005 from PB 10.5.1

ALL:
The stored procedure has the OUTPUT parameter defined in it.
When it is getting executed the values are not passed back
to PB. Please any help is appreciated.Hope a member from
Teamsybase will address it.

function long sp_Upload_MCCS(int agi_center_id,long
agl_campaign_id,ref string messg_text) RPCFUNC ALIAS FOR
"dbo.sp_Upload_MCCS"

li_ret_fetch =
lnvo_tr.sp_Upload_MCCS(gi_center,ll_campaign_id,ls_messg)

Is there any parameter need to be setup. I am using OLEDB to
connect to DB.
tr.DbParm="PROVIDER='SQLOLEDB',DATASOURCE='" + servername+
"',PROVIDERSTRING='database=" +dbname+";app=mccs
system',INTEGRATEDSECURITY='SSPI'"

Thank you
Ranjith
Using PB since V2.0
0
Ranjith
12/6/2007 10:55:47 PM
sybase.powerbuilder.database 9855 articles. 1 followers. Follow

9 Replies
767 Views

Similar Articles

[PageSpeed] 21

When passing a string to anything external you should always allocate 
memory:

messg_text = Space(<the longest possible length that can be returned>)

li_ret_fetch = lnvo_tr.sp_Upload_MCCS(gi_center,ll_campaign_id,ls_messg)


Brad


Ranjith Lakshman wrote:
> ALL:
> The stored procedure has the OUTPUT parameter defined in it.
> When it is getting executed the values are not passed back
> to PB. Please any help is appreciated.Hope a member from
> Teamsybase will address it.
> 
> function long sp_Upload_MCCS(int agi_center_id,long
> agl_campaign_id,ref string messg_text) RPCFUNC ALIAS FOR
> "dbo.sp_Upload_MCCS"
> 
> li_ret_fetch =
> lnvo_tr.sp_Upload_MCCS(gi_center,ll_campaign_id,ls_messg)
> 
> Is there any parameter need to be setup. I am using OLEDB to
> connect to DB.
> tr.DbParm="PROVIDER='SQLOLEDB',DATASOURCE='" + servername+
> "',PROVIDERSTRING='database=" +dbname+";app=mccs
> system',INTEGRATEDSECURITY='SSPI'"
> 
> Thank you
> Ranjith
> Using PB since V2.0
0
Brad
12/7/2007 2:35:20 PM
I did allocated the memory by assigning a value to the
variable being passed. But still I am not getting the value
back from the stored procedure.

nvo_tr 			lnvo_tr
String				ls_messg_text
long 				ll_campaign_id
Int					li_ret_fetch

ll_campaign_id		=
dw_411_campaigns.object.campaign_id[dw_411_campaigns.getrow()]

lnvo_tr = Create nvo_tr
f_set_connect_params(lnvo_tr, gs_servername,
'mccsdata',gb_64)
Connect Using lnvo_tr;

ls_messg_text =
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'

li_ret_fetch =
lnvo_tr.sp_Upload_MCCS_OUTPUT_TEST(gi_center,ll_campaign_id,ls_messg_text)

If lnvo_tr.Sqlcode <> 0 Then
	Messagebox('DBERROR', 'Error in execution of sp_Upload_MCCS
Procedure. ~n' + lnvo_tr.sqlerrtext + ls_messg_text)
	Return
End If


Stored proc defination:
function long sp_Upload_MCCS_output_test(int
agi_center_id,long agl_campaign_id,ref string messg_text)
RPCFUNC ALIAS FOR "dbo.sp_Upload_MCCS_output_test"

> When passing a string to anything external you should
> always allocate  memory:
>
> messg_text = Space(<the longest possible length that can
> be returned>)
>
> li_ret_fetch = lnvo_tr.sp_Upload_MCCS(gi_center
> ,ll_campaign_id,ls_messg)
>
>
> Brad
>
>
> Ranjith Lakshman wrote:
> > ALL:
> > The stored procedure has the OUTPUT parameter defined in
> > it. When it is getting executed the values are not
> > passed back to PB. Please any help is appreciated.Hope a
> > member from Teamsybase will address it.
> >
> > function long sp_Upload_MCCS(int agi_center_id,long
> > agl_campaign_id,ref string messg_text) RPCFUNC ALIAS FOR
> > "dbo.sp_Upload_MCCS"
> >
> > li_ret_fetch =
> > lnvo_tr.sp_Upload_MCCS(gi_center,ll_campaign_id
> > ,ls_messg)
> > Is there any parameter need to be setup. I am using
> > OLEDB to connect to DB.
> > tr.DbParm="PROVIDER='SQLOLEDB',DATASOURCE='" +
> > servername+ "',PROVIDERSTRING='database=" +dbname+"
> > ;app=mccs system',INTEGRATEDSECURITY='SSPI'"
> >
> > Thank you
> > Ranjith
> > Using PB since V2.0
0
Ranjith
12/10/2007 2:39:24 PM
Can we see your procedure?

Ranjith Lakshman wrote:
> I did allocated the memory by assigning a value to the
> variable being passed. But still I am not getting the value
> back from the stored procedure.
> 
> nvo_tr 			lnvo_tr
> String				ls_messg_text
> long 				ll_campaign_id
> Int					li_ret_fetch
> 
> ll_campaign_id		=
> dw_411_campaigns.object.campaign_id[dw_411_campaigns.getrow()]
> 
> lnvo_tr = Create nvo_tr
> f_set_connect_params(lnvo_tr, gs_servername,
> 'mccsdata',gb_64)
> Connect Using lnvo_tr;
> 
> ls_messg_text =
> 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
> 
> li_ret_fetch =
> lnvo_tr.sp_Upload_MCCS_OUTPUT_TEST(gi_center,ll_campaign_id,ls_messg_text)
> 
> If lnvo_tr.Sqlcode <> 0 Then
> 	Messagebox('DBERROR', 'Error in execution of sp_Upload_MCCS
> Procedure. ~n' + lnvo_tr.sqlerrtext + ls_messg_text)
> 	Return
> End If
> 
> 
> Stored proc defination:
> function long sp_Upload_MCCS_output_test(int
> agi_center_id,long agl_campaign_id,ref string messg_text)
> RPCFUNC ALIAS FOR "dbo.sp_Upload_MCCS_output_test"
> 
>> When passing a string to anything external you should
>> always allocate  memory:
>>
>> messg_text = Space(<the longest possible length that can
>> be returned>)
>>
>> li_ret_fetch = lnvo_tr.sp_Upload_MCCS(gi_center
>> ,ll_campaign_id,ls_messg)
>>
>>
>> Brad
>>
>>
>> Ranjith Lakshman wrote:
>>> ALL:
>>> The stored procedure has the OUTPUT parameter defined in
>>> it. When it is getting executed the values are not
>>> passed back to PB. Please any help is appreciated.Hope a
>>> member from Teamsybase will address it.
>>>
>>> function long sp_Upload_MCCS(int agi_center_id,long
>>> agl_campaign_id,ref string messg_text) RPCFUNC ALIAS FOR
>>> "dbo.sp_Upload_MCCS"
>>>
>>> li_ret_fetch =
>>> lnvo_tr.sp_Upload_MCCS(gi_center,ll_campaign_id
>>> ,ls_messg)
>>> Is there any parameter need to be setup. I am using
>>> OLEDB to connect to DB.
>>> tr.DbParm="PROVIDER='SQLOLEDB',DATASOURCE='" +
>>> servername+ "',PROVIDERSTRING='database=" +dbname+"
>>> ;app=mccs system',INTEGRATEDSECURITY='SSPI'"
>>>
>>> Thank you
>>> Ranjith
>>> Using PB since V2.0
0
Brad
12/10/2007 3:35:13 PM
Sure.
I had added the test code to return user friendly error
message.

USE [MCCSdata]
GO
/****** Object:  StoredProcedure
[dbo].[sp_Upload_MCCS_output_test]    Script Date:
12/10/2007 13:52:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Upload_MCCS_output_test]
  @agi_center_id tinyint,
  @agl_campaign_id int,
  @messg_text VARCHAR(100) OUTPUT
AS

DECLARE @li_timezone INT,@Success INT
DECLARE @call_time_start DATETIME , @call_time_stop DATETIME
DECLARE @errorvar_p INT,@errorvar_ps INT,@errorvar_pd
INT,@errorvar_pds INT
BEGIN

SET @Success = 0
SELECT @call_time_start = call_time_start,
	   @call_time_stop = call_time_stop
FROM campaigns  WHERE campaign_id =@agl_campaign_id

SELECT @li_timezone = CASE Upper(timezone_abbrev) WHEN 'PST'
THEN 1
										  WHEN 'MST' THEN 2
										  WHEN 'CST' THEN 3
										  WHEN 'EST' THEN 4
										  ELSE 5
						END
FROM centers WHERE id = @agi_center_id

BEGIN TRANSACTION
INSERT INTO PATRONS (center_id, campaign_id,
m_seq_no,acct1,acct2,acct3,
                             
acct4,acct5,acct9,city,state,zipcode, area_code,
						   timezone_abbrev,  cusip, reg_ben,account_no,
call_sw)
SELECT center_id,campaign_id,m_seq_no,acct1,acct2,acct3,
	   acct4,acct5,control_no,city,state,zipcode,COALESCE
(Left(area_code,3),' '),
	   timezone_abbrev,cusip,reg_ben,req_num,call_sw
FROM RECORDS WHERE rectype in (1,2) AND campaign_id =
@agl_campaign_id AND center_id = @agi_center_id ORDER BY
M_SEQ_NO
	
SET @errorvar_p = @@ERROR

IF @errorvar_p <> 0
BEGIN
	ROLLBACK TRANSACTION
	SET @messg_text = 'TEST OUTPUT VALUES'
	SELECT @errorvar_p
	RETURN @errorvar_p
END

INSERT INTO PATRONS_STATUS(M_I_V_date_sw,	center_id
,campaign_id,	m_seq_no, d_seq_no,
				status_code, staff_id,phone_no, phone_no_2,
phone_no_count ,
				direct_asst_call_count ,dial_count,last_dial_time,
				last_dial_result_code,
				last_dial_where_found,
				local_start_call_time,
				local_stop_call_time,
				my_shares,
				total_shares,  duplicate_count,comment,M_I_V_date, 
requested_do_not_contact,
				total_shares_rev)
SELECT 0,center_id,campaign_id,m_seq_no,dseqno,
	   CASE WHEN CONVERT( BIGINT,Ltrim(RTrim(phone_1))) < 999
THEN 193 ELSE 190 END,
		0,phone_1,phone_2,0,
		0,0,convert(datetime, '01-01-1900',101),
		last_dial_result_code = CASE WHEN CONVERT(
BIGINT,Ltrim(RTrim(phone_1))) < 999 THEN 247 ELSE 235 END,
		0,
		Convert(char(11),getdate(),101) +
convert(varchar(8),DATEADD(hh,@li_timezone - (CASE
Upper(timezone_abbrev) WHEN 'PST' THEN 1
										  WHEN 'MST' THEN 2
										  WHEN 'CST' THEN 3
										  WHEN 'EST' THEN 4
										  ELSE 5
						END) , @call_time_start),108),
		Convert(char(11),getdate(),101) +
convert(varchar(8),DATEADD(hh,@li_timezone - (CASE
Upper(timezone_abbrev) WHEN 'PST' THEN 1
										  WHEN 'MST' THEN 2
										  WHEN 'CST' THEN 3
										  WHEN 'EST' THEN 4
										  ELSE 5
						END) , @call_time_stop),108),
		my_shares,
		total_shares,recfreq - 1,' ',convert(datetime,
'01-01-1900',101),0,
		999999999 - total_shares
FROM RECORDS WHERE rectype in (1,2) AND campaign_id =
@agl_campaign_id AND center_id = @agi_center_id ORDER BY
M_SEQ_NO

SET @errorvar_ps = @@ERROR
IF @errorvar_ps <> 0
BEGIN
	ROLLBACK TRANSACTION
	SELECT @errorvar_ps
	RETURN @errorvar_ps
END
COMMIT TRANSACTION

BEGIN TRANSACTION
INSERT INTO patrons_duplicates
	(center_id ,campaign_id ,master_m_seq_no ,
duplicate_m_seq_no, my_shares ,acct1,
	 acct2, acct3, acct4, acct5, acct9, city, state, zipcode,
account_no ,area_code,
	 timezone_abbrev ,cusip,call_sw, REG_BEN)
SELECT center_id,campaign_id,m_seq_no,master_m_seq_no,
my_shares, acct1,
	  acct2,acct3,acct4,acct5,control_no,city,
state,zipcode,req_num, COALESCE (Left(area_code,3),' '),
	   timezone_abbrev,cusip,call_sw,reg_ben
FROM RECORDS WHERE rectype = 4 AND campaign_id =
@agl_campaign_id AND center_id = @agi_center_id ORDER BY
M_SEQ_NO

SET @errorvar_pd = @@ERROR

IF @errorvar_pd <> 0
BEGIN
	ROLLBACK TRANSACTION
	SELECT @errorvar_pd
	RETURN @errorvar_pd
END

INSERT INTO patrons_status(M_I_V_date_sw,center_id
,campaign_id,m_seq_no,d_seq_no,
				status_code,
				staff_id,phone_no, phone_no_2, phone_no_count ,
				direct_asst_call_count ,dial_count  ,last_dial_time,
				last_dial_result_code,
				last_dial_where_found,
				local_start_call_time,
				local_stop_call_time,
				my_shares,
				total_shares, duplicate_count, comment,	M_I_V_date, 
requested_do_not_contact,
				total_shares_rev)
SELECT 0,center_id,campaign_id,m_seq_no,dseqno,
	   status_code = CASE WHEN CONVERT(
BIGINT,Ltrim(RTrim(phone_1))) < 999 THEN 193 ELSE 190 END,
		0,phone_1,phone_2,0,
		0,0,convert(datetime, '01-01-1900',101),
		last_dial_result_code = CASE WHEN CONVERT(
BIGINT,Ltrim(RTrim(phone_1))) < 999 THEN 247 ELSE 235 END,
		0,
		Convert(char(11),getdate(),101) +
convert(varchar(8),DATEADD(hh,@li_timezone - (CASE
Upper(timezone_abbrev) WHEN 'PST' THEN 1
										  WHEN 'MST' THEN 2
										  WHEN 'CST' THEN 3
										  WHEN 'EST' THEN 4
										  ELSE 5
						END) , @call_time_start),108),
		Convert(char(11),getdate(),101) +
convert(varchar(8),DATEADD(hh,@li_timezone - (CASE
Upper(timezone_abbrev) WHEN 'PST' THEN 1
										  WHEN 'MST' THEN 2
										  WHEN 'CST' THEN 3
										  WHEN 'EST' THEN 4
										  ELSE 5
						END) , @call_time_stop),108),
		my_shares,
		total_shares,0 ,' ',convert(datetime, '01-01-1900',101),0,
		999999999 - total_shares
FROM RECORDS WHERE rectype = 4 AND campaign_id =
@agl_campaign_id AND center_id = @agi_center_id ORDER BY
M_SEQ_NO

SET @errorvar_pds = @@ERROR
IF @errorvar_pds <> 0
BEGIN
	ROLLBACK TRANSACTION
	SELECT @errorvar_pds
	RETURN @errorvar_pds
END

COMMIT TRANSACTION
SELECT @Success
RETURN @Success
END

> Can we see your procedure?
>
> Ranjith Lakshman wrote:
> > I did allocated the memory by assigning a value to the
> > variable being passed. But still I am not getting the
> > value back from the stored procedure.
> >
> > nvo_tr             lnvo_tr
> > String                ls_messg_text
> > long                 ll_campaign_id
> > Int                    li_ret_fetch
> >
> > ll_campaign_id        =
> >
> dw_411_campaigns.object.campaign_id[dw_411_campaigns.getro
> > w()]
> > lnvo_tr = Create nvo_tr
> > f_set_connect_params(lnvo_tr, gs_servername,
> > 'mccsdata',gb_64)
> > Connect Using lnvo_tr;
> >
> > ls_messg_text =
> >
> 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
> > li_ret_fetch =
> > lnvo_tr.sp_Upload_MCCS_OUTPUT_TEST(gi_center
> > ,ll_campaign_id,ls_messg_text)
> > If lnvo_tr.Sqlcode <> 0 Then
> >     Messagebox('DBERROR', 'Error in execution of
> > sp_Upload_MCCS Procedure. ~n' + lnvo_tr.sqlerrtext +
> >     ls_messg_text) Return
> > End If
> >
> >
> > Stored proc defination:
> > function long sp_Upload_MCCS_output_test(int
> > agi_center_id,long agl_campaign_id,ref string
> > messg_text) RPCFUNC ALIAS FOR
> > "dbo.sp_Upload_MCCS_output_test"
> >> When passing a string to anything external you should
> >> always allocate  memory:
> >>
> >> messg_text = Space(<the longest possible length that
> can >> be returned>)
> >>
> >> li_ret_fetch = lnvo_tr.sp_Upload_MCCS(gi_center
> >> ,ll_campaign_id,ls_messg)
> >>
> >>
> >> Brad
> >>
> >>
> >> Ranjith Lakshman wrote:
> >>> ALL:
> >>> The stored procedure has the OUTPUT parameter defined
> in >>> it. When it is getting executed the values are not
> >>> passed back to PB. Please any help is appreciated.Hope
> a >>> member from Teamsybase will address it.
> >>>
> >>> function long sp_Upload_MCCS(int agi_center_id,long
> >>> agl_campaign_id,ref string messg_text) RPCFUNC ALIAS
> FOR >>> "dbo.sp_Upload_MCCS"
> >>>
> >>> li_ret_fetch =
> >>> lnvo_tr.sp_Upload_MCCS(gi_center,ll_campaign_id
> >>> ,ls_messg)
> >>> Is there any parameter need to be setup. I am using
> >>> OLEDB to connect to DB.
> >>> tr.DbParm="PROVIDER='SQLOLEDB',DATASOURCE='" +
> >>> servername+ "',PROVIDERSTRING='database=" +dbname+"
> >>> ;app=mccs system',INTEGRATEDSECURITY='SSPI'"
> >>>
> >>> Thank you
> >>> Ranjith
> >>> Using PB since V2.0
0
Ranjith
12/10/2007 6:54:07 PM
Just an aside, but I don't know why people ever bother with OUTPUT parms to 
a stored procedure, when you can just as easily throw those values into the 
result set and process them that way...

Paul Horan[TeamSybase]

<Ranjith Lakshman> wrote in message 
news:475d8b4f.7723.1681692777@sybase.com...
> Sure.
> I had added the test code to return user friendly error
> message.
>
> USE [MCCSdata]
> GO
> /****** Object:  StoredProcedure
> [dbo].[sp_Upload_MCCS_output_test]    Script Date:
> 12/10/2007 13:52:47 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sp_Upload_MCCS_output_test]
>  @agi_center_id tinyint,
>  @agl_campaign_id int,
>  @messg_text VARCHAR(100) OUTPUT
> AS
>
> DECLARE @li_timezone INT,@Success INT
> DECLARE @call_time_start DATETIME , @call_time_stop DATETIME
> DECLARE @errorvar_p INT,@errorvar_ps INT,@errorvar_pd
> INT,@errorvar_pds INT
> BEGIN
>
> SET @Success = 0
> SELECT @call_time_start = call_time_start,
>    @call_time_stop = call_time_stop
> FROM campaigns  WHERE campaign_id =@agl_campaign_id
>
> SELECT @li_timezone = CASE Upper(timezone_abbrev) WHEN 'PST'
> THEN 1
>   WHEN 'MST' THEN 2
>   WHEN 'CST' THEN 3
>   WHEN 'EST' THEN 4
>   ELSE 5
> END
> FROM centers WHERE id = @agi_center_id
>
> BEGIN TRANSACTION
> INSERT INTO PATRONS (center_id, campaign_id,
> m_seq_no,acct1,acct2,acct3,
>
> acct4,acct5,acct9,city,state,zipcode, area_code,
>    timezone_abbrev,  cusip, reg_ben,account_no,
> call_sw)
> SELECT center_id,campaign_id,m_seq_no,acct1,acct2,acct3,
>    acct4,acct5,control_no,city,state,zipcode,COALESCE
> (Left(area_code,3),' '),
>    timezone_abbrev,cusip,reg_ben,req_num,call_sw
> FROM RECORDS WHERE rectype in (1,2) AND campaign_id =
> @agl_campaign_id AND center_id = @agi_center_id ORDER BY
> M_SEQ_NO
>
> SET @errorvar_p = @@ERROR
>
> IF @errorvar_p <> 0
> BEGIN
> ROLLBACK TRANSACTION
> SET @messg_text = 'TEST OUTPUT VALUES'
> SELECT @errorvar_p
> RETURN @errorvar_p
> END
>
> INSERT INTO PATRONS_STATUS(M_I_V_date_sw, center_id
> ,campaign_id, m_seq_no, d_seq_no,
> status_code, staff_id,phone_no, phone_no_2,
> phone_no_count ,
> direct_asst_call_count ,dial_count,last_dial_time,
> last_dial_result_code,
> last_dial_where_found,
> local_start_call_time,
> local_stop_call_time,
> my_shares,
> total_shares,  duplicate_count,comment,M_I_V_date,
> requested_do_not_contact,
> total_shares_rev)
> SELECT 0,center_id,campaign_id,m_seq_no,dseqno,
>    CASE WHEN CONVERT( BIGINT,Ltrim(RTrim(phone_1))) < 999
> THEN 193 ELSE 190 END,
> 0,phone_1,phone_2,0,
> 0,0,convert(datetime, '01-01-1900',101),
> last_dial_result_code = CASE WHEN CONVERT(
> BIGINT,Ltrim(RTrim(phone_1))) < 999 THEN 247 ELSE 235 END,
> 0,
> Convert(char(11),getdate(),101) +
> convert(varchar(8),DATEADD(hh,@li_timezone - (CASE
> Upper(timezone_abbrev) WHEN 'PST' THEN 1
>   WHEN 'MST' THEN 2
>   WHEN 'CST' THEN 3
>   WHEN 'EST' THEN 4
>   ELSE 5
> END) , @call_time_start),108),
> Convert(char(11),getdate(),101) +
> convert(varchar(8),DATEADD(hh,@li_timezone - (CASE
> Upper(timezone_abbrev) WHEN 'PST' THEN 1
>   WHEN 'MST' THEN 2
>   WHEN 'CST' THEN 3
>   WHEN 'EST' THEN 4
>   ELSE 5
> END) , @call_time_stop),108),
> my_shares,
> total_shares,recfreq - 1,' ',convert(datetime,
> '01-01-1900',101),0,
> 999999999 - total_shares
> FROM RECORDS WHERE rectype in (1,2) AND campaign_id =
> @agl_campaign_id AND center_id = @agi_center_id ORDER BY
> M_SEQ_NO
>
> SET @errorvar_ps = @@ERROR
> IF @errorvar_ps <> 0
> BEGIN
> ROLLBACK TRANSACTION
> SELECT @errorvar_ps
> RETURN @errorvar_ps
> END
> COMMIT TRANSACTION
>
> BEGIN TRANSACTION
> INSERT INTO patrons_duplicates
> (center_id ,campaign_id ,master_m_seq_no ,
> duplicate_m_seq_no, my_shares ,acct1,
> acct2, acct3, acct4, acct5, acct9, city, state, zipcode,
> account_no ,area_code,
> timezone_abbrev ,cusip,call_sw, REG_BEN)
> SELECT center_id,campaign_id,m_seq_no,master_m_seq_no,
> my_shares, acct1,
>   acct2,acct3,acct4,acct5,control_no,city,
> state,zipcode,req_num, COALESCE (Left(area_code,3),' '),
>    timezone_abbrev,cusip,call_sw,reg_ben
> FROM RECORDS WHERE rectype = 4 AND campaign_id =
> @agl_campaign_id AND center_id = @agi_center_id ORDER BY
> M_SEQ_NO
>
> SET @errorvar_pd = @@ERROR
>
> IF @errorvar_pd <> 0
> BEGIN
> ROLLBACK TRANSACTION
> SELECT @errorvar_pd
> RETURN @errorvar_pd
> END
>
> INSERT INTO patrons_status(M_I_V_date_sw,center_id
> ,campaign_id,m_seq_no,d_seq_no,
> status_code,
> staff_id,phone_no, phone_no_2, phone_no_count ,
> direct_asst_call_count ,dial_count  ,last_dial_time,
> last_dial_result_code,
> last_dial_where_found,
> local_start_call_time,
> local_stop_call_time,
> my_shares,
> total_shares, duplicate_count, comment, M_I_V_date,
> requested_do_not_contact,
> total_shares_rev)
> SELECT 0,center_id,campaign_id,m_seq_no,dseqno,
>    status_code = CASE WHEN CONVERT(
> BIGINT,Ltrim(RTrim(phone_1))) < 999 THEN 193 ELSE 190 END,
> 0,phone_1,phone_2,0,
> 0,0,convert(datetime, '01-01-1900',101),
> last_dial_result_code = CASE WHEN CONVERT(
> BIGINT,Ltrim(RTrim(phone_1))) < 999 THEN 247 ELSE 235 END,
> 0,
> Convert(char(11),getdate(),101) +
> convert(varchar(8),DATEADD(hh,@li_timezone - (CASE
> Upper(timezone_abbrev) WHEN 'PST' THEN 1
>   WHEN 'MST' THEN 2
>   WHEN 'CST' THEN 3
>   WHEN 'EST' THEN 4
>   ELSE 5
> END) , @call_time_start),108),
> Convert(char(11),getdate(),101) +
> convert(varchar(8),DATEADD(hh,@li_timezone - (CASE
> Upper(timezone_abbrev) WHEN 'PST' THEN 1
>   WHEN 'MST' THEN 2
>   WHEN 'CST' THEN 3
>   WHEN 'EST' THEN 4
>   ELSE 5
> END) , @call_time_stop),108),
> my_shares,
> total_shares,0 ,' ',convert(datetime, '01-01-1900',101),0,
> 999999999 - total_shares
> FROM RECORDS WHERE rectype = 4 AND campaign_id =
> @agl_campaign_id AND center_id = @agi_center_id ORDER BY
> M_SEQ_NO
>
> SET @errorvar_pds = @@ERROR
> IF @errorvar_pds <> 0
> BEGIN
> ROLLBACK TRANSACTION
> SELECT @errorvar_pds
> RETURN @errorvar_pds
> END
>
> COMMIT TRANSACTION
> SELECT @Success
> RETURN @Success
> END
>
>> Can we see your procedure?
>>
>> Ranjith Lakshman wrote:
>> > I did allocated the memory by assigning a value to the
>> > variable being passed. But still I am not getting the
>> > value back from the stored procedure.
>> >
>> > nvo_tr             lnvo_tr
>> > String                ls_messg_text
>> > long                 ll_campaign_id
>> > Int                    li_ret_fetch
>> >
>> > ll_campaign_id        =
>> >
>> dw_411_campaigns.object.campaign_id[dw_411_campaigns.getro
>> > w()]
>> > lnvo_tr = Create nvo_tr
>> > f_set_connect_params(lnvo_tr, gs_servername,
>> > 'mccsdata',gb_64)
>> > Connect Using lnvo_tr;
>> >
>> > ls_messg_text =
>> >
>> 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
>> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
>> > li_ret_fetch =
>> > lnvo_tr.sp_Upload_MCCS_OUTPUT_TEST(gi_center
>> > ,ll_campaign_id,ls_messg_text)
>> > If lnvo_tr.Sqlcode <> 0 Then
>> >     Messagebox('DBERROR', 'Error in execution of
>> > sp_Upload_MCCS Procedure. ~n' + lnvo_tr.sqlerrtext +
>> >     ls_messg_text) Return
>> > End If
>> >
>> >
>> > Stored proc defination:
>> > function long sp_Upload_MCCS_output_test(int
>> > agi_center_id,long agl_campaign_id,ref string
>> > messg_text) RPCFUNC ALIAS FOR
>> > "dbo.sp_Upload_MCCS_output_test"
>> >> When passing a string to anything external you should
>> >> always allocate  memory:
>> >>
>> >> messg_text = Space(<the longest possible length that
>> can >> be returned>)
>> >>
>> >> li_ret_fetch = lnvo_tr.sp_Upload_MCCS(gi_center
>> >> ,ll_campaign_id,ls_messg)
>> >>
>> >>
>> >> Brad
>> >>
>> >>
>> >> Ranjith Lakshman wrote:
>> >>> ALL:
>> >>> The stored procedure has the OUTPUT parameter defined
>> in >>> it. When it is getting executed the values are not
>> >>> passed back to PB. Please any help is appreciated.Hope
>> a >>> member from Teamsybase will address it.
>> >>>
>> >>> function long sp_Upload_MCCS(int agi_center_id,long
>> >>> agl_campaign_id,ref string messg_text) RPCFUNC ALIAS
>> FOR >>> "dbo.sp_Upload_MCCS"
>> >>>
>> >>> li_ret_fetch =
>> >>> lnvo_tr.sp_Upload_MCCS(gi_center,ll_campaign_id
>> >>> ,ls_messg)
>> >>> Is there any parameter need to be setup. I am using
>> >>> OLEDB to connect to DB.
>> >>> tr.DbParm="PROVIDER='SQLOLEDB',DATASOURCE='" +
>> >>> servername+ "',PROVIDERSTRING='database=" +dbname+"
>> >>> ;app=mccs system',INTEGRATEDSECURITY='SSPI'"
>> >>>
>> >>> Thank you
>> >>> Ranjith
>> >>> Using PB since V2.0 


0
Paul
12/10/2007 7:28:44 PM
> ALTER PROCEDURE [dbo].[sp_Upload_MCCS_output_test]
>  @agi_center_id tinyint,
>  @agl_campaign_id int,
>  @messg_text VARCHAR(100) OUTPUT

What are native strings in PB 10+?  Unicode.
What datatype did you use for the string argument of the procedure?  Varchar
(i.e., non-unicode).

I don't think that PB will do any automatic datatype mapping for you in this
case.  Hopefully, changing the argument's datatype to nvarchar should solve
the problem.



0
Scott
12/10/2007 8:08:46 PM
Now that we are talking about Nvarchar - unicode datatype.

I have a question with respect to it.
1.When i insert values through MS SQLserver as foreigh
char's the values are stored right in the table with a
nvarchar column. And when i retrieve the table i do get them
back perfectly fine in DB as well as PB.
2. But when I try to copy the foreign chars into the column
from PB through datawindow the values are not passed onto
the DB. it stores as '?????????????????'.

Any thoughts. Let me know if you need any other info.

In the meanwhile i will try to work on the other option
mentioned with respect to Nvarchar as datatype.

> > ALTER PROCEDURE [dbo].[sp_Upload_MCCS_output_test]
> >  @agi_center_id tinyint,
> >  @agl_campaign_id int,
> >  @messg_text VARCHAR(100) OUTPUT
>
> What are native strings in PB 10+?  Unicode.
> What datatype did you use for the string argument of the
> procedure?  Varchar (i.e., non-unicode).
>
> I don't think that PB will do any automatic datatype
> mapping for you in this case.  Hopefully, changing the
> argument's datatype to nvarchar should solve the problem.
>
>
>
0
Ranjith
12/11/2007 3:57:14 AM
<Ranjith Lakshman> wrote in message 
news:475e0a9a.18c.1681692777@sybase.com...
> Now that we are talking about Nvarchar - unicode datatype.
>
> I have a question with respect to it.
> 1.When i insert values through MS SQLserver as foreigh
> char's the values are stored right in the table with a
> nvarchar column. And when i retrieve the table i do get them
> back perfectly fine in DB as well as PB.

SQL server can convert, implicitly and explicitly, between varchar and 
nvarchar.   Converting from nvarchar to varchar may not always work 
depending on the characters involved.  Converting the other way should 
always work - as far as I know (but I'm no expert on unicode).  In this 
particular case, retrieving varchar or nvarchar into a PB10+ datawindow 
should work "perfectly fine" since the only possible conversion is from the 
smaller datatype into the larger.  Another thing to keep in mind is the 
presentation.  The font you use can affect what you see.  The following link 
may help you understand the issues involved with unicode strings.

http://www.joelonsoftware.com/articles/Unicode.html

> 2. But when I try to copy the foreign chars into the column
> from PB through datawindow the values are not passed onto
> the DB. it stores as '?????????????????'.

I don't understand what this means nor how you made this determination, so I 
can't comment.

>
> Any thoughts. Let me know if you need any other info.

I'm guessing that the stored procedure code you posted contains "extra" 
logic for debugging purposes.  I recommend you remove the SELECT statements 
that generate resultsets to see if that is causing additional confusion with 
PB.  In addition, you only use the output variable once - is this really 
something you need that is an "improvement" over standard error handling? 


0
Scott
12/11/2007 2:06:25 PM
Scott: Thank you for the article.
I get it to work on PB and DB interface. Except that for
every INSERT or UPdate issued to the DB needs have the
Nvarchar column appended with 'N' in the string. But need to
find any other solution which is much better then this one.
Iam doing this in SQLpreview event in datawindow.

With respect to the OUTPUT param's in stored procedure for
now I am going to leave it with 'Resultset' as return param.

Thank you for all the help.
0
Ranjith
12/11/2007 8:05:31 PM
Reply:

Similar Artilces:

Porting Code From PB 10.5 + Adaptive Server as Back End TO PB 10.5 + MS Sql Server as Back End.
Hi All I have been trying to get the Date part of the value of a datetime data field & can not work out the logic in MS SQL. My Original Query (as in Adaptive Server) Looks as Follows: dSelectedDate = Today() SELECT IsNull ( Sum ( ( ( "kotline"."quantityplate" + "kotline"."quantitypack" ) * "kotline"."saleprice") + ( ( "kotline"."quantityplate" + "kotline"."quantitypack" ) * "kotline"."saleprice") * ("saletax"."tax" / 100)), 0 ) INTO...

PB (10.2.1) connect with MS SQL Server 2005
Hi, I am currently working on Migration project and its almost complete. I am using OLEDB with SQLNCLI to connect with the db and its working fine. DBMS = OLEDB DBpram = PROVIDER='SQLNCLI',DATASOURCE='xxxx',PROVIDERSTRING='DataBase=yyyy',IntegratedSecurity='SSPI' when i create PBD and tested with someother PC, its giving DBMS is not supported error. Please let me know wats the specific dll which will connect with the db. i tried searching PBSNCxxx.dll but i dont find it anyway. Please help me in this regards. thanks Regards Balaji Chinnu ...

SQL Server 2005
Hi, I am looking for a good example of Application Role within PB. The purpose is to use as logon screen the Windows Authentication mode where the user has to reenter his password and then use an application role in the application. In such case, the user would not be authorized to use Query Analyser or Access to get in the database. Thanks, Claudy Here is a quick example that I cut and pasted from an existing application. There are some issues when using application role with PB and SQL Server. There is an issue with connecting again (same application or diffe...

PB calling a MS sql Server stored proc...and the raiserror are not bubling up in the correct order.
Running MSsqlserver 2000, PB 8.03 (PFC) I am doing a RPC call via sqlca like so ll_rtc = sqlca.usp_claim_verify( istr_name_id.claim_id, ls_emsg) SetPointer(arrow!) IF sqlca.sqlcode <> 0 then MessageBox('Claim Item Processing',& 'An error occured trying to VERIFY' + & ' the claim/items.~r~n' + & 'sqlcode:' + string(sqlca.sqlcode) + & '~r~n'+ 'sqlerrtext:'+ sqlca.sqlerrtext) return END IF If ll_rtc <> 0 THEN MessageBox('Claim Item Procesin...

How to set database profile to PowerBuilder 10.5 connect to MS-SQL Server 2000 sp4?
Hi: How to set database profile to PowerBuilder 10.5 connect to MS-SQL Server 2000 sp4? It seems that the special connection inferface for "MSS Microsoft SQL Server" is canceled in PowerBuilder 10.5. I do not want to via ODBC. Thanks! Wu; No, MSS driver (actually Microsoft's NTDBLIB.dll) was cancelled by Bill Gates in SQLServer v7. It was supported in 2000 - but ANSI only with limited functionality and dropped for SS2000 and 2005 in the Unicode world. So your choices are: ODBC, OLE-DB and ADO.net. There is new SNC driver for PB 11 that is simil...

Porting Ms Sql Server 6.5 database to Ms Sql Server 7.0
Hi, We have developed a product using PowerBuilder7.0, Ms Sql Server 6.5 and connecting them using powerbuilder native driver. Now we are thinking of porting Ms Sql Server 6.5 database to Ms Sql Server 7.0.As we feel, we could achive new features of Sql server 7.0.e.g row level locking. Actually i want to know, what things i have to do.e.g what sort of connection i have to use? what new features i will able to use?.will Stored procedures work properly. Is there anyting i have to chang in my application(at front end)e.g datawindows or any embeded sql, external functions. Should i po...

Porting Ms Sql Server 6.5 Database to Ms Sql Server 7.0
Hi, We have developed a product using PowerBuilder7.0, Ms Sql Server 6.5 and connecting them using powerbuilder native driver. Now we are thinking of porting Ms Sql Server 6.5 database to Ms Sql Server 7.0.As we feel, we could achive new features of Sql server 7.0.e.g row level locking. Actually i want to know, what things i have to do.e.g what sort of connection i have to use? what new features i will able to use?.will Stored procedures work properly. Is there anyting i have to chang in my application(at front end)e.g datawindows or any embeded sql, external functions. Should i po...

Migrating sql server 2005 express database to sql server 2005 database
Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail. Regards, Sandyhttp://www.thequinn.infohttp://www.sksdataservices.comhttp://www.infobasket.info Hi Sandy, To move a database from SQL Express to SQL 2005, you can use the following steps. 1. If your database file is attached to the server instance, you will need to detach it first using SQL management studio. If it is under App_Data folde...

BUG!!!! ?? Using Stored Procedure with PB 6.5 and MS SQL Server 6.5.
I have some stored procedures on my DBMS, MS SQL Server 6.5. After upgrading to PB 6.5 I cannot assign any of my stored procedures to a datawindow. What the .... is wrong. Is it my installation? Is it PB 6.5? In fact it is serious as my reports are not running. Any comments are appreciated. Thomas Door Maersk Data AS What is the error you are getting, maybe we could help you then. Randy Thomas D��r <thd@maerskdata.dk> wrote in article <CoL4foOz9GA.276@forums.powersoft.com>... > I have some stored procedures on my DBMS, MS SQL Server 6.5. After upgrading ...

Call a MS SQL Server 2000 stored procedure in PB 8 via a PB script and Datawindow
I tried to call a stored procedure with owner name "sbs" via PB script and datawindow.Also I tried different way to call it, using ownername.procname, procname, and dbname.ownername.procname. But nothing works. it Always say something is not a parameter of the proc. The error message did not show the owner name as a part of the proc call. so I think that somehow the owner name wasn't sent to SQL 2000 server. if I changed the owner name to dbo, it works! so my question is how we let PB send the whole name(including owner name) of the proc to SQL 2000 server to get it sol...

PB and MS SQL Server stored procs
Hi , We are using MS SQL Server 2000 with PowerBuilder 6.5 . There is a stored procedure that returns four columns from a table ( max 1 row). The procedure has 2 input parameters and it returns 4 columns and 1 row as part of a select statement . We are calling this procedure from PowerBuilder using DECLARE and trying to get the result using EXECUTE and FETCH . But there are no rows returned to PowerBuilder . The same EXECUTE gives the 1 row and 4 columns from with MS SQL SERVER ( using the Query Analyzer) . What do we do ? We also tried using a datawindow with this proc...

PB Stored Procs for MS SQL Server?
Are there MS SQL Server versions of the pbsyc.sql and pbsyc2.sql Adaptive Server stored procedures we run as part of installing PowerBuilder (9.0)? If so, where can they be found? I find some for Oracle and DB2 in the server folder of the PB 9 installation CD, but not SQL Server. If there are none, what functionality will be missing in PowerBuilder without them? Thanks in Advance for your help! (BTW, we are very sad to be migrating to MS SQL Server from ASE, but a mandate came down from management to do so. But we are trying to convince them to let us keep our AS...

PB 10.5 and MS SQL Server
I'm trying to convert an 8.0 PB app to 10.5. I'm getting errors because I don't have a MS SQL Server connection. It's not listed as an installed DB interface like it was in 8.0. How can I fox this? > I'm trying to convert an 8.0 PB app to 10.5. I'm getting > errors because I don't have a MS SQL Server connection. > It's not listed as an installed DB interface like it was > in 8.0. How can I fox this? I do not believe that PB 10.5 supports or has any Microsoft native drivers. I think using an odbc connection is going to be your best bet....

Problem with stored proc, migrating from MS SQL 2000 to MS SQL 2005
I've been migrating some DB from MS SQL 2000 to MS SQL 2005 all works fine but I have one problem with stored procedures. Even if there is actually no errors I get the message "The transaction ended in the trigger. The batch has been aborted.". The record is succesfully deleted but still I get the message above. I know MS SQL 2005 added the try/catch block did they change something else that cause compatibility issues with 2000? Here's a sample : *** DECLARE @Error bit DECLARE @ErrorMsg varchar (100) DECLARE @ContactIDToDelete int SELECT @ContactIDToDelete ...

Web resources about - Calling Stored Proc in MS SQl Server 2005 from PB 10.5.1 - sybase.powerbuilder.database

Calling Dr. Death - Wikipedia, the free encyclopedia
Calling Dr. Death (1943) is the first of the Universal Pictures Inner Sanctum mystery films . The "Inner Sanctum" franchise originated with a ...

List of country calling codes - Wikipedia, the free encyclopedia
This is a list of country calling codes defined by ITU-T recommendations E.123 and E.164 , also called IDD ( International Direct Dialling ) ...

Calling Batman: Bane Takes Over Ecko Unlimited Facebook Page
Bane, the villain in upcoming Batman feature film The Dark Knight Rises , turned his criminal mind toward the Ecko Unlimited Facebook page , ...

Facebook Video Calling - Facebook
Bring your conversations to life on Facebook. With face-to-face video calling, now you can watch your friends smile, wink and LOL. To get started, ...

Facebook’s Talktime program in India pays new mobile users in calling credit
... Facebook has launched its Talktime program in India which gives every new user who signs up with a mobile device 50 Rupees (almost $1) in calling ...

Berlin Calling (@neuraum) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

Calling all foodies: Here comes @TwitterFood
There are many thousands of food-related Tweets people send on Twitter each day – Tweets about meals, ingredients, favorites, recipes and dining ...

Rounds Video Calling + Chat for iPhone 4, iPhone 4S, iPhone 5, iPhone 5c, iPhone 5s, iPad 2 Wi-Fi, iPad ...
Get Rounds Video Calling + Chat on the App Store. See screenshots and ratings, and read customer reviews.


They're back. All-Australian Koels calling in ACT suburbia
Too many Gang-gang cockatoos, our dear territory's faunal emblem,&nbsp;are not enough.

Resources last updated: 11/20/2015 10:53:11 PM