[Wsf-general] Data service - Stored Procedures

[Diluka Moratuwage]

Hi all,

When considering the extensibility of the dataservice, we can
also think of including the support for stored procedures. If
we use stored procedurs, for complex queries, as well as for services
that include, multiple types of sql statements it would become a
good solution.

And also it will solve some problems regarding authorization. The
dataservice owner can only execute stored procedures, which are
allowed by the database owner.

Also it will remove the complication of writing complex queries
in the config file, which might be hard to extract. But we have
to implement the same difficult queries in stored procedures.

Then the config file writer's task becomes very flexible, as an
example, suppose their is a stored procedure called getUserInfo(id),
then, the config file writer can specify his operation as,

<operation name = "getUserInfo">
<!-- PARAMETER LIST -->
</operation>

I'm little bit, confused regarding the problem of the connection
section of the config file. As shown in james mail earliar, we can
specify the connectivity information in different ways. If we need
to use a driver, such as jdbc or odbc, then its ok. But if we use,
generic way to access the database, then its neccessary to mention
about the database system to use.

Suggestions please....

Thanks,
Diluka.

[Asankha C. Perera]

Diluka

Stored procedures are not portable, and since the database is not
complicated or huge, the advantages may soon be outweighed

asankha

Post by Diluka Moratuwage
Hi all,
When considering the extensibility of the dataservice, we can
also think of including the support for stored procedures. If
we use stored procedurs, for complex queries, as well as for services
that include, multiple types of sql statements it would become a
good solution.
And also it will solve some problems regarding authorization. The
dataservice owner can only execute stored procedures, which are
allowed by the database owner.
Also it will remove the complication of writing complex queries
in the config file, which might be hard to extract. But we have
to implement the same difficult queries in stored procedures.
Then the config file writer's task becomes very flexible, as an
example, suppose their is a stored procedure called getUserInfo(id),
then, the config file writer can specify his operation as,
<operation name = "getUserInfo">
<!-- PARAMETER LIST -->
</operation>
I'm little bit, confused regarding the problem of the connection
section of the config file. As shown in james mail earliar, we can
specify the connectivity information in different ways. If we need
to use a driver, such as jdbc or odbc, then its ok. But if we use,
generic way to access the database, then its neccessary to mention
about the database system to use.
Suggestions please....
Thanks,
Diluka.
_______________________________________________
Wsf-general mailing list
http://wso2.org/cgi-bin/mailman/listinfo/wsf-general

[Diluka Moratuwage]

Post by Diluka Moratuwage
Diluka
Stored procedures are not portable, and since the database is not
complicated or huge, the advantages may soon be outweighed
asankha

Our case is little different, in data service, a database owner writes
his own stored procedure in his own database system, and let the
webservices developer know
the stored procedure and the parameters return values..etc...

And then the developer is capable of using that stored procedure...So
the portability
doesn't involve for our case, since we just use a ready made stored
procedure, instead
of building a stored procedure our own.

Thanks,
Diluka.

[Asankha C. Perera]

Diluka

Sorry - I misunderstood the exact problem you are solving.. my apologies

asankha

Post by Diluka Moratuwage

Post by Diluka Moratuwage
Diluka
Stored procedures are not portable, and since the database is not
complicated or huge, the advantages may soon be outweighed
asankha

Our case is little different, in data service, a database owner writes
his own stored procedure in his own database system, and let the
webservices developer know
the stored procedure and the parameters return values..etc...
And then the developer is capable of using that stored procedure...So
the portability
doesn't involve for our case, since we just use a ready made stored
procedure, instead
of building a stored procedure our own.
Thanks,
Diluka.

[James Clark]

Post by Diluka Moratuwage
Hi all,
When considering the extensibility of the dataservice, we can
also think of including the support for stored procedures. If
we use stored procedurs, for complex queries, as well as for services
that include, multiple types of sql statements it would become a
good solution.

Can't queries and other SQL statements call out to stored procedures?

I'm not seeing that we need to do anything special in the config file to
support stored procedures.

Post by Diluka Moratuwage
Also it will remove the complication of writing complex queries
in the config file

Not always, because the person writing the config file might not be
authorized to add stored procedures to the database.

Post by Diluka Moratuwage
But if we use,
generic way to access the database, then its neccessary to mention
about the database system to use.

I'm not sure what you mean by "generic" here. Are you thinking of how
Axis2/C will decide whether to use MySQL or PostgreSQL when it's linked
against both of them? I would handle this by having a property
"axis2c.driver" with properties "mysql" or "postgresql".

James

[Sanjiva Weerawarana]

Post by James Clark

Post by Diluka Moratuwage
Hi all,
When considering the extensibility of the dataservice, we can
also think of including the support for stored procedures. If
we use stored procedurs, for complex queries, as well as for services
that include, multiple types of sql statements it would become a
good solution.

Can't queries and other SQL statements call out to stored procedures?

I think you can but then that call becomes part of the query and not part
of the result AFAIK.

Post by James Clark
I'm not seeing that we need to do anything special in the config file to
support stored procedures.

I think we probably will need some syntax to help users say just invoke
this SP and take the results and send it back.

However, I'd like to keep focused on getting queries supported just right
(including with the real REST binding) and then start thinking about how
to do SPs.

Sanjiva.