SQLEXEC - including a return parameter

[Kflasph]

Hi !
I am trying to receive an parameter from a SQLEXEC and I cannot get the formatting correct.
I am sending a string of record information so it looks like

result=SQLEXEC(nConn,"sp_procname "+"','"+mvar+"','"+mvar+"','"

...the next part is the one I am having problems with
after connecting about 50 mvars, I want to include a return value at the end but when I put the ?@reternvar at the end - "','"+mvar+"',"+?@returnvar,"SQLCURSOR&quot - to end the SQLEXEC command, I receive an error.

Any ideas? It works great when I just pass the value of 0 to the variable I am trying to return but does not work the other way.

Thanks
kflasph

 

[Mike Lewis]

A couple of thoughts off the top of my head:

- Are you actually creating the variable for the output parameter before you send the command? You need to assign a value to it, in order for VFP to establish its data type. It doesn't matter what the value is, provided it is the same data type as the one being returned.

- In your stored procedure, are you declaring the parameter as an output parameter (with the keyword OUTPUT)? If not, that could also explain the error.

If the above doesn't help, perhaps you could post the entire code? Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[Kflasph]

Thanks for the reply! I have the output parameter declared in the Stored proc.

Stored Proc code on SQL:

CREATE procedure usp_insertsalesact @cust_no char(50),@ord_no char(10),@part_no char(15), @vpart_no char(15),@part_desc char(10), @ship_no char(6), @weight char(5), @bill_no char(6),@dt_blank char(6), @curr_dt char(8), @quantity int, @division char(1),@pse char(1), @bch char(1), @terr char(2),@trans_no char(2),@disc_cd char(1),@ext_cd char(1),@stx_cd char(1), @ship_via char(5),@freight dec(14,2),@misc1_amt decimal(14,2), @misc2_amt decimal(14,2),@misc3_amt decimal(14,2),@misc_desc1 char(10), isc_desc2 char(10), @misc_desc3 char(3),@p_cd char(1), @amt decimal(16,3), @cost_cd char(2), @inv_no char(6),@com_cat char(3),
@endus char(2), @hp_cd char(2), @nr_cd char(1), @engsr char(9), @inv_dt char(8),@net_cst decimal(13,2), @ref_no char(6), @claim_no char(10), @cm_num char(6), @hanlg decimal(14,3),@exc_tx decimal(14,3), @sales_tx decimal(14,3), @cmess char(1),@sh_cd char(1), @num_lines int,@cust_info1 varchar(40), @cust_info2 varchar(37),@cust_info3 varchar(37), @cust_info4 varchar(37),@cust_info5 varchar(37),
@disc_amt char(3), @jrnl_no char(3), @penalty char(1), @eng_code char(3),@warranty char(8),@osal_amt decimal(10,2), @cust_ord2 char(10), @auto_ind char(1),@dist_part char(15), @dep_inv char(1), @netext_amt decimal(13,2), @mtlcost decimal(14,4),@lbrcost decimal(14,4), @acct_mo char(2),@acct_yr char(4), @recin int OUTPUT

The Stored proc then looks to see a file exist and if it does, it is suppose to return a 3 if not insert the file and return a 2.


This is the code that I am trying to use in FoxPro to return my parameter. (Excuse the formating -doesn't copy well

Basically passing 67 values with the return at the end.

select salesstruc
scatter memvar
resultcode=SQLExec(nConn,"usp_insertsalesact "+ " '"+;
m.customer+" ','"+m.custpo+" ','"+m.partnum+" ','"+;
m.custpart+" ','"+m.partname+" ','"+m.shipnum+" ','"+m.wgt+" ','"+m.billn+" ','"+m.dued+" ','"+m.adate+" ',"+alltrim(str(m.qty))" ,'"+m.div+" ','"+m.pse+" ','"+m.branch+" ','"+m.territory+" ','"+m.tran+" ','"+ ;
m.discd+" ','"+m.etxcd+" ','"+m.stxcd+" ','"+m.shipvia+" ',"+alltrim(str(m.freight))+" ,"+alltrim(str(m.misc1))+" ,'"+alltrim(str(m.misc2))+" ','"+alltrim(str(m.misc3))+" ','"+ ;
m.desc1+" ','"+m.desc2+" ','"+m.desc3+" ','"+m.pcode+" ',"+alltrim(str(m.Uprice))+" ,'"+m.costcd+" ','"+m.invref+" ','"+m.category+" ','"+ ;
m.endus+" ','"+m.hpcode+" ','"+m.nrcode+" ','"+m.engserl+" ','"+m.invdate+" ',"+alltrim(str(m.netamt))+" ,'"+m.refnbr+" ','"+m.claim+" ','"+ ;
m.cmnum+" ',"+alltrim(str(m.handling))+" ,"+alltrim(str(m.excisetax))+" ,"+alltrim(str(m.salestax))+" ,'"+m.cmess+" ','"+m.shcod+" ',"+alltrim(str(m.numli))+" ,'"+m.Slin1+" ','"+ ;
m.Slin2+" ','"+m.Slin3+" ',;
'"+m.Slin4+" ','"+m.Slin5+" ',"+alltrim(str(m.disct))+" ,'"+m.jrnal+" ','"+m.penalty+" ','"+m.engr_c_c+" ','"+m.warranty+" ',"+;
alltrim(str(m.osal))+" ,'"+m.custord2+" ','"+m.auto_ind+" ','"+m.dist_part+" ','"+m.dep_inv+" ',"+alltrim(str(m.netext))+" ,"+alltrim(str(m.mtlcost))+" ,"+alltrim(str(m.lbrcost))+" ,'"+ ;

The last line is where the trouble is:

m.acct_mo+" ','"+m.acct_yr+"',"+" ?@recin","Sqlreturn&quot


and the end of the code
nDisconnect=SQLDisconnect(0)
if recin=0
MessageBox("This did not work&quot
endif

I get a successful return when ran.

Unless I put it in that format, I get an error from FoxPro of either I am missing a required formatting parameter or an invalid type.
When I have used output before it was usually in the format of:
SQLEXEC(nConn,"strProcName ?@output","SQLRESULT&quot
It has return a value but now I am inserting 67 vaules before the return and cannot get it to return the recin value.
The command goes through - the line is inserted or returned and I receive a 1 in the resultcode call but my
recin value remains at 0.

I hope this information provides a little more light.

Thanks,
KFlasph

 

[Mike Lewis]

Your stored procedure looks fine.

Howeve, in your VFP code, I suggest you try putting this line before your SQLEXEC():

recin = 0

That's what I meant when I said that you have to assign a value to a variable before you can use it as an output parameter.

Perhaps you could give it a try and report back.
Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[Kflasph]

Thanks again for the reply,I eventually played around with it 'till I got it to work (with some help from a co-worker too)

I did have the variable initialized ( I forgot to put it in the posted code )

This is what I was missing :

result=SQLExec(nConn,"execute usp_insertsalesact "+ " '"+;
all of the other variables
last line:
m.acct_mo+" ','"+m.acct_yr+ "',?@recin","Sqlreturn&quot

Enclosing the return variable within the quotes and placing execute before the stored proc command made it work!

Thanks again for your help!

KFlasph

 

[Mike Lewis]

Glad you got it working. I didn't notice the lack of quotes.

Also, I see you added the word execute just before the SP name.

I don't know which back-end you are using, but in SQL Server, "execute" is optional if the command is the first in the batch (as is the case here).
Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk