B4J Question jrdc2 postgresql stored procedure call

[hwatech]

I have been pouring over the forum and experimenting for the entire day and I cannot seem to make it work. I see a number of posts regarding stored procedures and postresql but none seem to show how the config.properties should be formatted. I've used variations on this:

sql.proc_addcategory=call proc_addcategory(?,?,?,?,?)
sql.proc_addcategory = call proc_addcategory p_categoryid=? ,_categorytitle=? , _isactive=? , _categorydescription = ? , _categoryimage = ?

Here is my procedure:

CREATE OR REPLACE PROCEDURE public.proc_addcategory(IN p_categoryid integer, IN _categorytitle character varying, IN _isactive integer, IN _categorydescription character varying, IN _categoryimage character varying)
    LANGUAGE 'plpgsql'
    
AS $BODY$
declare

begin
  if exists(select * from categoryinfo where categoryid = p_categoryid) then
 
    update categoryinfo
       set categorytitle = _categorytitle
       ,isactive = _isactive
       ,categorydescription = _categorydescription
       ,categoryimage = _categoryimage
     where categoryid = p_categoryid;
 
    
   else
        insert into categoryinfo
                   (categorytitle
                   ,isactive
                   ,categorydescription
                   ,categoryimage)
             values
                   (_categorytitle
                   ,_isactive
                   ,_categorydescription
                   ,_categoryimage);
    

end if;
end;
$BODY$;

I am able to call the procedure using SQL in pgadmin4 and all works ok but here is what I see in my output window

(PSQLException) org.postgresql.util.PSQLException: ERROR: syntax error at or near "p_categoryid"
  Position: 27
Command: , took: 24ms, client=192.168.1.110

Any help would be greatly appreciated

Larry

 

[Erel]

I believe that you will need to use this modified version of jRDC2: https://www.b4x.com/android/forum/t...ith-stored-procedures-support.123294/#content

 

[hwatech]

Thanks for the suggestion.

I gave it the old college try but I was not able to get it to work. Due to time constraints, I'll stay with mssql for the time being and work on the migration to postgres gradually.

I'll also keep searching the forum in case someone has a solution

 

[aeric]

sql.proc_addcategory=call proc_addcategory(?,?,?,?,?)

The first line should be correct.

 

[hwatech]

Thanks for your response...I thought that was the correct way from doing my research. So the issue must be in the jrdc2 server. Do you use the modified version that Erel pointed to? Or do you use the standard jrdc2? I've tried it both ways but it's also possible that my code is causing the issue...

Here is a snippet of code that I use:

        Dim req As DBRequestManager = CreateRequest
        Dim cmd As DBCommand = CreateCommand("proc_addcategory", Array(SelectedTblindex, txtDesc.Text, 1, "",s))
        
        
        'Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
        Wait For (req.ExecuteCall(cmd, 0, Null)) JobDone(j As HttpJob)
        If j.Success Then
            
        Else
                
            Log("ERROR: " & j.ErrorMessage)
        
        End If

        Dim req As DBRequestManager = CreateRequest
        
        Dim p1 As Map = CreateMap("name":"categoryname", "type":"IN", "sqlType": "INTEGER", "value":SelectedTblindex)
        Dim p2 As Map = CreateMap("name":"categorydesc", "type":"IN", "sqlType": "VARCHAR", "value":txtDesc.Text)
        Dim p3 As Map = CreateMap("name":"categoryimage", "type":"IN", "sqlType": "VARCHAR", "value":s)
        Dim cmd As DBCommand = CreateCommand("proc_addcategory", Array(p1, p2, 1, "",p3))
        
        Wait For (req.ExecuteCall(cmd, 0, Null)) JobDone(j As HttpJob)
        If j.Success Then
            
        Else
                
            Log("ERROR: " & j.ErrorMessage)
        
        End If

 

[OliverA]

A) Regarding the above post
1) The first code snippet is supposed to be standard jRDC2. Standard jRDC2 does not have an ExecuteCall method.
2) Regarding the second code snippet: make sure that the name you provide matches the name given in the procedure call.
So

Dim p1 As Map = CreateMap("name":"categoryname", "type":"IN", "sqlType": "INTEGER", "value":SelectedTblindex)

needs to be

Dim p1 As Map = CreateMap("name":"p_categoryid", "type":"IN", "sqlType": "INTEGER", "value":SelectedTblindex)

B) Regarding standard jRDC2:
1) If all you are doing is a simple procedure that only has IN variables, standard jRDC2 should work using the standard ExecuteQuery method. The jRDC2 config needs to have

sql.proc_addcategory=call proc_addcategory(?,?,?,?,?)

(as pointed out by @aeric )
and your could be

Dim cmd As DBCommand = CreateCommand("proc_addcategory", Array(SelectedTblindex, txtDesc.Text, 1, "",s))
Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)

2) Since the procedure is an insert/update procedure, it might be that it needs to be

Dim cmd As DBCommand = CreateCommand("proc_addcategory", Array(SelectedTblindex, txtDesc.Text, 1, "",s))
Wait For (req.ExecuteBatch(Array(cmd))) JobDone(j As HttpJob)

What would help is to tie any error messages you receive to the option that you have chosen. So if you try A2, then list the messages you receive trying it. If you try B1, then list the messages received for it and so on. This way the error can be associated with the means used to generate the error.

 

[hwatech]

Yeah, I had a cut-n-paste error with the snippets... I have been trying so many things that I'm not always sure if I'm coming or going...

Thanks for the suggestions, I'll give them a try. Not sure what A2 and B1 are but I think get your point

 

[hwatech]

Thanks to all, I have it working now, the fix was to change the names in the Map to the actual parameter name (OliverA), I had assumed that it wanted the column name. The standard jrdc2 is what I'm using and it seems all is well