Hi,
does somebody know, how can I execute a DB (MySQL) function from REST API, I cannot find it in LiveAPI…
Thank you,
Chuan
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
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
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.
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!
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;
}])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!
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.
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
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.
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
2 Likes
Cool, glad you got it working!
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?
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.