DB: how could call a DB function?


#1

Hi,

does somebody know, how can I execute a DB (MySQL) function from REST API, I cannot find it in LiveAPI…

Thank you,
Chuan


#2

Karl,

Hey - to perform a simple GET on your DB, simply use this URL, but plug in your DSP name.

https://dsp_name.cloud.dreamfactory.com:443/rest/db

Is there a more advanced function that you’re trying to use?

Thanks,

  • Mark

#3

Hi Mark,

thank you for the answer, it’s just very simple function, give one parameter, reuten one valie.
This is what I get with you link (with my DSP name):
{“error”:[{“context”:null,“message”:“No application name header or parameter value in request.”,“code”:400}]}

And I’ve installed the DreamFactory localy (Binami), I guess you mean the “GET mydb/table_name” (getRecordsByIds), should I give the funtion’s name as table name? and the parameters as Ids?
I got: “Table “myfunc” does not exist in the database.”

Regards,
Chuan


#4

Hi Chuan,

You need to pass API name (the name of the app) into the API request. You also need to authenticate first to get a session token. Alternatively, you can enable guest users in the Config tab and assign guest users a role. Make sure that the role has access to the table you are querying with the API call.

The basics are all here https://github.com/dreamfactorysoftware/dsp-core/wiki/Important-Information.

Check out these [cURL examples] (https://github.com/dreamfactorysoftware/dsp-core/wiki/cURL-Examples).

These short [video tutorials] (https://www.youtube.com/playlist?list=PL2nQn3mpqAp6GKFAab0HROrZXZHeXMdPh ) show how to make basic API calls as well.


#5

Thank you Ben!
I’ve logged in and I can get & update data with tables, but how can I use GET (mydb/table) with function?
when I give the function in table name, I’ll get "Table “myfunc” does not exist in the database."
how can I do it? especially how could I build the $resource?

By the way I’ve wrote a procedure to do the same as this func, very simple, it has two params:
myfunc(OUT result int, IN name VARCHAR(20))
I try to use callStoredProcWithParams(), I give the procedure name, but I don’t how to give the body,
I’ve tried:
[ {“result”: “”}, {“name”: “seq_1”} ] OR
{ “result”: “”, “name”: “seq_1” }
the return message:
“Incorrect number of arguments for PROCEDURE …; expected 2, got 0”

thank you very much for the help!


#6

I suggest checking out the docs on calling stored procedures https://github.com/dreamfactorysoftware/dsp-core/wiki/SQL-Stored-Procedures.

Calling the DreamFactory API with Angular’s $resource is just like calling any REST API. Check out the Angular docs for info on that.

Here’s a quick example.

// Create a service

'use strict';

var baseUrl = 'https://dsp-foo.cloud.dreamfactory.com';
var appName = 'your-app';

angular.module('your-app.services', ['ngResource'])

    .service('GetItemsService', ['$resource', function ($resource) {
        return $resource(baseUrl + '/rest/db/item',
            { appName: appName, fields: '@fields', filter: '@filter' },
            { query: { method: 'GET' , isArray: false }
            });
    }])

Then you can have a controller that calls this service (or inject the service into your routeProvider resolve).

'use strict';

angular.module('your-app')

.controller('UnshippedItemsCtrl', ['$scope', 'GetItemsService', function($scope, GetItemsService) {

$scope.params = {
fields: 'id' + ',' + 'description' + ',' + 'price' + ',' + 'location' + ',' + 'url',
filter: 'status=' + '"Unshipped"'
};

 $scope.items = GetItemsService.query($scope.params)$promise;

 }])

#7

Hi Ben,

I checked the link in your answer, and write the following:

target ist call a procedure to get the nextval from a sequence (a mysql table):
proc_nextval(OUT cur_val int, IN seq_name VARCHAR(20));

in factory:

factory.getNextId = function () {
    return $resource('/rest/mydb/_proc/proc_nextval/?app_name=myapp',
                     {},
                     {
                        exec: { method: 'POST' }
                     });
};

in controller:

var param = { "params": [{"name": "cur_val", "param_type": "OUT", "type": "integer"}, 
                                     {"name": "seq_name", "param_type": "IN",  "value": "seq_1"}] };
myFactory.getNextId().exec(param, function () { 
} );

I got response message:

"Failed to call database stored procedure.\nCDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error"

it may not be problem with df, but I use the REST API, with LiveAPI, I got the same error.
could you please help me to find out which part is wrong? (params?)

sorry I’m new with REST, and thank you for the help!


#8

If you see this in the live API then there’s some issue either with the connection to the SQL db or with the way you made the REST API call (like params being sent the wrong way).

Best thing is to email support@dreamfactory.com and they’ll get a support ticket going to help you.


#9

Hi Ben,

Funny thing, I got the same error. “error code: 500, msg: Failed to call database stored procedure.
SQLSTATE[HY000]: General error”… when calling my stored procedure via both the Live API and from my application using a POST call to my own EC2 DSP instance.
AND in both scenarios, the stored procedure actually did get called and I was able to see the new record created by the stored procedure each time.

My only problem is trying to track down exactly why this issue keeps coming back to my application as a failed attempt. My logic chain to handle this result is then completely useless.

Harry


#10

Hey Harry, it’s a bit tough to diagnose. Can you shoot an email to support@dreamfactory.com and they’ll have some diagnostic things to try. Hopefully they can get it sorted out.


#11

Hey Ben,

Thanks for getting back to me quickly.
I figured out the reason. I have it working perfectly now.

For others who might find it useful.
Detailed description:
In MySQL, there is no return value when calling a stored procedure with the sole purpose of doing an insert or update. So hence, the error was occurring b/c there was no return value coming back. My guess would be that your code on your end is looking for either a result or an output return variable.
And since I was not using an output parameter in my stored procedure… the error was happening.
I don’t know what the correct solution for this would be. Although, I think you guys probably handled it correctly… in stating that it is indeed a technical error since DSP was expecting something that was not right (you can say). And it got me to tweak my stored procedure accordingly.

2 possible Solutions:
(1) Use an output parameter which indicates the status of your stored procedure.
(2) I created my own return value by using logic to capture the SQLState of the event by creating a handler for the SQLException and getting the DIAGNOSTICS CONDITION (in mysql).

CODE for (2) in your Stored Procedure:



CREATE STORED PROC spName (
  -- your parameters here
)
BEGIN
	-- Declare variables to hold diagnostics area information
	DECLARE code CHAR(5) DEFAULT '00000';
	DECLARE rows INT;
	DECLARE result INT;
	-- Declare exception handler for failed insert
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE;
    END;

    -- perform the insert. Put your insert statement here

	-- Check whether the insert was successful
	IF code = '00000' THEN
		GET DIAGNOSTICS rows = ROW_COUNT;
		SET result = CAST(rows AS UNSIGNED);
	ELSE
		SET result = CAST(code AS UNSIGNED);
        END IF;
	-- Say what happened
	SELECT result;
END


HTH,
Harry


Stored Procedures error 500
Format for inserting data through a stored procedure - Using the Postman Chrome Extension
#12

Cool, glad you got it working!


#13

I migth have the same issue with MS SQL 2014. But i do not understand your fix/solution. Would you mind explaining it more?

I have a stored procedure and it does not return anything, it just updates a pivot table with multiple primary keys.

Do i need to return something?


#14

If I understand @harry’s response, if the SPROC returns no values the DSP will throw this error. Therefore, you could ignore the error (since the SPROC is called successfully anyway) or force the SPROC to return a value to make the DSP happy.


#15

please review this discussion
CLICK HERE