Thanks Sajith for sharing. Thanks, Suhan
On Mon, Apr 6, 2015 at 11:11 AM, Sajith Kariyawasam <[email protected]> wrote: > 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 >> >> > -- 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
