Hi Suhan, IMO, whether to use stored procedure or not depends on how complex the queries are.
For eg, to get a result if you have to do multiple database calls (a complex query), better you create an SP in the database and expose, so that from the service layer you can avoid multiple database calls. For simple searches, better to use direct SQL than SPs because if you are to create more SPs for simple searches, it would not be manageable. SPs are database specific so it would not be easily port an SP written in MySQL to Oracle, but direct SQLs are portable provided that those are written with ANSI complaint way. Thanks, Sajith On Mon, Apr 6, 2015 at 9:43 AM, Suhan Dharmasuriya <[email protected]> wrote: > > > On Sun, Apr 5, 2015 at 8:13 PM, Suhan Dharmasuriya <[email protected]> > wrote: > >> Hi, >> >> I'm using WSO2 DSS 3.2.1 with local MySQL DB. >> >> Please find the data service XML configuration below[1]. >> >> There are two ways to retrieve data from a mysql database table as I have >> tested. >> >> 1. Using a stored procedure created as follows in MySQL DB. >> Here the query logic is inside MySQL DB as stored procedures. >> CREATE PROCEDURE GetAllEmployees() SELECT FirstName , LastName , Team >> FROM Employee; >> Refer Query "getAllEmployeesQuery" in below configuration[1]. >> >> 2. Directly using the select statement inside data service configuration. >> Here the query logic is embedded inside the data service configuration. >> Refer Query "getAllEmployeesSQLQuery" in below configuration[1]. >> >> What is the recommended way/best practice to retrieve data from the above >> given two alternatives? >> >> Or is there a better third alternative? >> >> [1] >> <data xmlns:svns="http://org.wso2.securevault/configuration"; >> name="SampleDataSource" serviceNamespace="www.wso2.org/dss"> >> <description>test with or without stored procedure</description> >> <config id="dss_sampleDB"> >> <property name="driverClassName">com.mysql.jdbc.Driver</property> >> <property name="url">jdbc:mysql://127.0.0.1:3306/dss_sample >> </property> >> <property name="username">root</property> >> <property name="password" svns:secretAlias="yourMySQLDBPassword"/> >> </config> >> <query id="*getAllEmployeesQuery*" useConfig="dss_sampleDB"> >> <sql>call GetAllEmployees;</sql> >> <result element="Employees" rowName="Employee"> >> <element column="FirstName" name="FirstNameValue" >> xsdType="string"/> >> <element column="LastName" name="LastNameValue" >> xsdType="string"/> >> <element column="Team" name="TeamValue" xsdType="string"/> >> </result> >> </query> >> <query id="*getAllEmployeesSQLQuery*" useConfig="dss_sampleDB"> >> <sql>SELECT FirstName , LastName , Team FROM Employee;</sql> >> <result element="Employees" rowName="Employee"> >> <element column="FirstName" name="FirstNameValue" >> xsdType="string"/> >> <element column="LastName" name="LastNameValue" >> xsdType="string"/> >> <element column="Team" name="TeamValue" xsdType="string"/> >> </result> >> </query> >> <operation name="getAllEmployees"> >> <description>using a stored procedure</description> >> <call-query href="getAllEmployeesQuery"/> >> </operation> >> <operation name="getAllEmployeesSQL"> >> <description>without using a stored procedure</description> >> <call-query href="getAllEmployeesSQLQuery"/> >> </operation> >> </data> >> >> Thanks, >> Suhan >> >> -- >> Suhan Dharmasuriya >> Software Engineer - Test Automation >> >> *WSO2, Inc. * >> >> lean . enterprise . middleware >> Tel: +94 112 145345 >> Mob: +94 779 869138 >> Blog: http://suhan-opensource.blogspot.com/ >> > > > > -- > Suhan Dharmasuriya > Software Engineer - Test Automation > > *WSO2, Inc. * > > lean . enterprise . middleware > Tel: +94 112 145345 > Mob: +94 779 869138 > Blog: http://suhan-opensource.blogspot.com/ > > _______________________________________________ > Dev mailing list > [email protected] > http://wso2.org/cgi-bin/mailman/listinfo/dev > >
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
