SmartClient Evaluation version 11.1: Steps to connect to oracle database

[nmurthy]

SmartClient trial package: SmartClient_v111p_2017-11-02_Evaluation

Below is the use case which I am trying to implement using smartclient:
- I have a stored procedure(VALIDATE_USER_ID) defined in oracle database which returns STATUS_MSG(success/failure)
- Connect to Oracle DB from smartclient: Achieved using admin console
- I have implemented login screen in smartclient which takes "username" and "password" as input
- On click of login button, I want to make call to "VALIDATE_USER_ID" from smartclient and pass "username" and "password" as parameters
- Read and display STATUS_MSG of the above call on the login screen

What I have in place:
- JS file for login screen
- Datasource file attached to login screen

What did I try:
Used operationBinding tag to invoke strored procedure, but I am not able to send any add/remove/update request from client while I am able to fetch data from database using "operationType"=fetch.

<DataSource ID="LoginDialog" serverType="sql" dataFormat="isc" tableName="AA_USERS">
<fields>
<field name="USER_ID" type="text" hidden="true" primaryKey="true"/>
<field name="USER_ID" type="text" title="User ID" required="true"/>
<field name="PASSWORD" type="text" title="Password" required="true"/>
</fields>

<operationBinding operationType='add' operationId='newuser'>
<customSQL>
call AA_USER_MANAGEMENT.VALIDATE_USER_LOGIN(AA_USERS.USER_ID,AA_USERS.PASSWORD)

</customSQL>
</operationBinding>
</DataSource>

Stored procedure function signature:

PROCEDURE VALIDATE_USER_LOGIN(I_LOGIN_USER_ID IN VARCHAR2,
I_Password IN VARCHAR2,
O_STATUS OUT VARCHAR2,
O_ERROR_MSG OUT VARCHAR2);

Queries:
- How to implement button click function in javascript for the above usecase?
- What am I missing in the above syntax written in *.ds.xml file?

Thank You
Nandini

[Blama]

Hi nmurthy,

this is Isomorphic's suggestion for a container based login mechanism, which IMHO will be more easy to protect than a homegrown Oracle based approach.
Also the Quick Start Guide also suggests to use a plain HTML page for login, just search for "login" in the document.

For all customSQL / Stored procedure issues read these two threads and all linked threads as well. You'll definitely need operationType='custom' and perhaps also some other settings.

Best regards
Blama

[nmurthy]

Thanks Blama.

Currently what I see is, stored procedure mentioned within <customSQL> tag is not getting called. SmartClient is generating query using "tableName" and "operationType" tags. Am I missing any tags which is avoiding stored procedure from being invoked?

[Blama]

Hi nmurthy,

if that's your whole DS you show in #1, you are missing the enclosing <operationBindings> tag.
Also, code enclosed in [ CODE ] tags makes it very more readable, here.

Best regards
Blama

[nmurthy]

Here is my ds.xml:

<DataSource ID="LoginDialog" serverType="sql" dataFormat="iscServer">
    <fields>
        <field name="USER_ID" type="sequence" hidden="true" primaryKey="true" />
        <field name="USER_ID" type="text" title="User ID" length="50"
            required="true" primaryKey="true" />
        <field name="PASSWORD" type="text" title="Password" length="50"
            required="true" />
    </fields>

    <operationBindings>
        <operationBinding operationType='custom' operationId='validateUser'
            allowMultiUpdate='true' sqlType='update'>
            <customSQL>CALL VALIDATE_USER_LOGIN $values.USER_ID,$values.PASSWORD
            </customSQL>
        </operationBinding>
    </operationBindings>
</DataSource>

From the attched logs, smartclient is trying to send the below query to database instead of the one mentioned withing <customSQL>

INSERT INTO LoginDialog (PASSWORD, USER_ID) VALUES ('test', 'test')

[Blama]

See this in the logs:

    operationConfig:{
        dataSource:"LoginDialog",
        repo:null,
        operationType:"add",
        textMatchStyle:"exact"
    },

You are executing an "add" operation, while your operationBinding is operationType='custom'. You also need to change the client request your browser is sending.

But I again suggest reading the suggestion for a container based login mechanism. Most likely what you are planning is only security-by-obsecurity. How are you making sure that nobody forges requests to your real DataSources without being logged on?

Best regards
Blama

[nmurthy]

Login is one of the usecase I am trying in order to make sure if I am able to invoke stored procedure call. There are many screens which falls under same path.

From the client(.js file), I am invoking ds.xml using dynamicForm.saveData(). (not sure if this right client request)

.js dynamicForm.saveData() -> ds.xml to invoke stored database procedure call -> Database procedure

This is the path I am trying to establish.

[Blama]

OK, dynamicForm.saveData() is a add request if you start with an empty form, which you do.
I assume you have a Button executing your dynamicForm.saveData(). Make it instead executing a custom operation on your DataSource object with the values from dynamicForm.getValues().

If this is just a test, it's OK IMHO. The real login should really be Realm based. Also, make sure to read the Quick Start Guide, if you not have already. This also covers the "Why does a DynamicForm issue an Add operation" and "When should I use a custom operation".

Best regards
Blama

[nmurthy]

Thanks Blama , Your suggestion did work. What I was missing was having appropriate trigger in place from client code(JS file). Hence stored procedure placed in between <customSQL> tag was not getting invoked.

I used the following snippet in my JS file to fix theissue:

isc.DataSource.get(datSourceID).performCustomOperation(operationID,DynamicForm0.getValues())

Thank you
Nandini