Unable to get PRIMARY_KEY violation response from SQL Adapter using worklight?

Question

I have MySql table with userID,Mobile,Password,Re-Password column where Mobile Column is my PRIMARY_KEY and i am inserting data into this from my SQL Adapter.

SQLAdapter-impl.js

function registration(mob_no,user_name,pass,re_pass){
    return WL.Server.invokeSQLStoredProcedure({
        procedure : "registration",
        parameters : [mob_no,user_name,pass,re_pass]

    });
}

SQLAdapter.xml

<procedure name="registration" />

Registration.js

function registration(mob_no,user_name,pass,re_pass){
    var invocationData = {
            adapter : "SQLAdapter",
            procedure: "registration",
            parameters: [mob_no,user_name,pass,re_pass]
    };
    WL.Client.invokeProcedure(invocationData, {
        onSuccess: getSecretDataOKReg, 
        onFailure: getSecretDataFAILReg
    });
}

function getSecretDataOKReg(response)
{   
    var bool=response.invocationResult.isSuccessful;
    console.log("Registration Operation Successfull :" +bool);
    if(bool)
    {
        saveRegistrationDetails();
        $("#pagePort").load(path + "pages/Login.html", function(){
            $.getScript(path + "js/Login.js", function() {
                if (currentPage.init) {
                    currentPage.init();
                }
            });
        });
    }
}

function getSecretDataFAILReg(response)
{
    alert("Unsuccess "+response);
}

My Problem is When i insert new data, Everything goes fine i get proper response and everything is "Happie Ending Story" but while i am trying to insert the same Mobile number along with different permo-combo data i get error

Procedure invocation error. Runtime: Failed to retrieve data with procedure : registration worklight.js:4673
WL.Logger.__log worklight.js:4673
PUBLIC_API.(anonymous function) worklight.js:4858
onInvokeProcedureSuccess worklight.js:7355
window.WLJSX.Ajax.WLRequest.WLJSX.Class.create.onSuccess worklight.js:3238
window.WLJSX.Ajax.WLRequest.WLJSX.Class.create.onWlSuccess worklight.js:3210
(anonymous function) worklight.js:947
window.WLJSX.Ajax.Request.WLJSX.Class.create.respondToReadyState worklight.js:1156
window.WLJSX.Ajax.Request.WLJSX.Class.create.onStateChange worklight.js:1094
(anonymous function)

I understand this may have arrived since i have tried to insert same primary key data in MYSql but how could i positively handle it and show alert or something to user stating you are already registered ? Is there any problem in my js file to identify SQL Error code which is cant see in error response.

Answer 1

This error has nothing wrong with Worklight here i think you should use Callback which return value back from MySQL.

function registration(mob_no,user_name,pass,re_pass,returnValue){
    var invocationData = {
            adapter : "SQLAdapter",
            procedure: "registration",
            parameters: [mob_no,user_name,pass,re_pass,returnValue]
    };
    WL.Client.invokeProcedure(invocationData, {
        onSuccess: getSecretDataOKReg, 
        onFailure: getSecretDataFAILReg
    });
}

function getSecretDataOKReg(response)
{   
    var bool=response.invocationResult.isSuccessful;
    console.log("Registration Operation Successfull :" +bool);
    if(bool)
    {
        saveRegistrationDetails();
        $("#pagePort").load(path + "pages/Login.html", function(){
            $.getScript(path + "js/Login.js", function() {
                if (currentPage.init) {
                    currentPage.init();
                }
            });
        });
    }
}

function getSecretDataFAILReg(response)
{   
    console.log("Check this here "+ response.invocationResult.resultSet[0].returnValue);
    alert("Unsuccess "+JSON.stringify(response));
}

and in your Stored procedure use returnValue as OUT parameter and use if and else condition.

Please find this SQL Stored Procedure for the same:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `loginAuthentication`(IN mob_no VARCHAR(45),IN user_name VARCHAR(45),pass VARCHAR(45),re_pass VARCHAR(45),OUT returnvalue INT)

BEGIN
       DECLARE no_of_records INT;

       SELECT COUNT(*) INTO no_of_records
       FROM tablename.registration 
       WHERE tablename.registration.mob_no=mob_no ;

       IF no_of_records = 1 THEN 
       SET returnvalue = 0; //Already exists one record with mob_no
       ELSE

       // Your INSERT QUERY

       SET returnvalue = 1;
       END IF;
END $$
DELIMITER ;