execute immediate is not giving proper result when called through a webservice
274 views
Skip to first unread message
durga
Aug 21, 2009, 8:38:01 AM8/21/09
to SQL Anywhere Web Development
HI
can some one tell me why "execute immediate" is not working for me .
I have created a webservice which would call a test procedure. Inside
procudure i am calling "execute immediate".
output in the browser i could see only first column of my query. If I
dont use execute immediate my query is working fine and i could see
all columns as response in my browser.
webservice
create service testservice type 'html' authorization on user null url
on as call test_proc(:url);
procedure
create procedure test_proc(in url long varchar)
begin
execute immediate with result set on 'SELECT * FROM customers';
end
NOTE: same procedure i culd run interactive SQL
thanks
durga
can some one tell me why "execute immediate" is not working for me .
I have created a webservice which would call a test procedure. Inside
procudure i am calling "execute immediate".
output in the browser i could see only first column of my query. If I
dont use execute immediate my query is working fine and i could see
all columns as response in my browser.
webservice
create service testservice type 'html' authorization on user null url
on as call test_proc(:url);
procedure
create procedure test_proc(in url long varchar)
begin
execute immediate with result set on 'SELECT * FROM customers';
end
NOTE: same procedure i culd run interactive SQL
thanks
durga
Eric Farrar
Aug 31, 2009, 11:11:11 AM8/31/09
to SQL Anywhere Web Development
Hello Durga,
The problem is that SQL Anywhere does not know what the result set
will look like when the stored procedure is first compiled and saved.
As a result, it defaults to a single column (since it knows the result
set will have at least on column). Usually SQL Anywhere can
automatically scan the procedure to determine it based on the
contained SELECT statements. However, if you are using execute
immediate, it can not do it automatically,
The way around this is to explicitly tell SQL Anywhere what the result
set will look like by using the RESULTS(...) directive in the CREATE
PROCEDURE statement. (http://dcx.sybase.com/1101en/dbreference_en11/
create-procedure-user-defined-statement.html).
The reason that it works in iSQL is because Interactive SQL does some
extra work to try to dynamically display the result set.
The problem is that SQL Anywhere does not know what the result set
will look like when the stored procedure is first compiled and saved.
As a result, it defaults to a single column (since it knows the result
set will have at least on column). Usually SQL Anywhere can
automatically scan the procedure to determine it based on the
contained SELECT statements. However, if you are using execute
immediate, it can not do it automatically,
The way around this is to explicitly tell SQL Anywhere what the result
set will look like by using the RESULTS(...) directive in the CREATE
PROCEDURE statement. (http://dcx.sybase.com/1101en/dbreference_en11/
create-procedure-user-defined-statement.html).
The reason that it works in iSQL is because Interactive SQL does some
extra work to try to dynamically display the result set.
durga
Sep 2, 2009, 2:07:22 AM9/2/09
to SQL Anywhere Web Development
HI Eric,
Thanks for your suggestion.
As per my requirement client app would send a query to my webservice
which i would execute and send the result set in html format.
since I dont know the result set, i am executing the query whch was
sent by the client app.
is there any work around for me.
thanks
durga
Thanks for your suggestion.
As per my requirement client app would send a query to my webservice
which i would execute and send the result set in html format.
since I dont know the result set, i am executing the query whch was
sent by the client app.
is there any work around for me.
thanks
durga
Eric Farrar
Sep 3, 2009, 3:17:30 PM9/3/09
to SQL Anywhere Web Development
One way you could workaround this is to implement your own automatic
HTML generation in your procedure. This is actually a very good option
since it gives you full control over how the HTML table will be
formatted.
I have written a function called dynamic_sql_to_html that takes in a
query string as input, and returns a string containing the a formatted
HTML string that exactly mimics the built-in HTML support in the
server. It uses the sa_describe_query() procedure to analyze the
result set of the input sql query string, and dynamically builds a new
query to return a properly formatted HTML result set.
create function dynamic_sql_to_html(dynamic_sql_query long varchar)
returns long varchar
begin
declare html_table_header long varchar;
declare xml_query long varchar;
declare temp_table_definition long varchar;
declare temp_table_column_list long varchar;
declare res long varchar;
call sa_set_http_header('Content-type', 'text/html');
select
XMLELEMENT(name tr, XMLATTRIBUTES('header' as class), XMLAGG
(XMLELEMENT(name th, XMLCONCAT(XMLELEMENT(name small,
"base_table_name"), XMLELEMENT(name br), XMLELEMENT(name b,
"name"))))),
'XMLAGG(XMLELEMENT(name tr, xmlconcat(' || list('XMLELEMENT(name
td, "' || "name" || '")') || ')) order by
sa_maintain_order_of_derived_table)',
'declare local temporary table sa_foo
(sa_maintain_order_of_derived_table int default autoincrement, ' ||
list('"' || "name" || '" ' || "domain_name_with_size", ',') || ')',
LIST('"' || "name" || '"', ',')
into html_table_header, xml_query, temp_table_definition,
temp_table_column_list
from sa_describe_query(dynamic_sql_query);
execute immediate temp_table_definition;
execute immediate 'insert into sa_foo(' || temp_table_column_list ||
') ' || dynamic_sql_query;
execute immediate
'select ''<html><head><title>'
|| HTTP_HEADER('@HttpURI')
|| '</title></head><body><h3>'
|| HTTP_HEADER('@HttpURI')
|| '</h3><table border=1>'
|| html_table_header
|| ''' || '
|| xml_query
|| ' || ''</table></body><br><hr><address>SQLAnywhere/'
|| @@version
|| '</address></html>'' into res from sa_foo';
return res;
end;
As an example of using this function, you could create a web service
such as:
create service root
authorization off
user dba
type 'raw'
url on
as select dynamic_sql_to_html(:url);
Using that service, you could then pass in queries directly through
the URL. For example,
http://localhost/SELECT * FROM SYSTABLE
would return a properly formatted HTML result for the query 'SELECT *
FROM SYSTABLE". Of course, there are huge security implications of
allowing any arbitrary query to be executed through the URL, I was
simply using that service it as a simple example to show of how to use
dynamic_sql_to_html() function. And, as I said earlier, since the
dynamic_sql_to_html() code is in your control, you can tweak it to
output the HTML however you like.
Will that workaround work?
HTML generation in your procedure. This is actually a very good option
since it gives you full control over how the HTML table will be
formatted.
I have written a function called dynamic_sql_to_html that takes in a
query string as input, and returns a string containing the a formatted
HTML string that exactly mimics the built-in HTML support in the
server. It uses the sa_describe_query() procedure to analyze the
result set of the input sql query string, and dynamically builds a new
query to return a properly formatted HTML result set.
create function dynamic_sql_to_html(dynamic_sql_query long varchar)
returns long varchar
begin
declare html_table_header long varchar;
declare xml_query long varchar;
declare temp_table_definition long varchar;
declare temp_table_column_list long varchar;
declare res long varchar;
call sa_set_http_header('Content-type', 'text/html');
select
XMLELEMENT(name tr, XMLATTRIBUTES('header' as class), XMLAGG
(XMLELEMENT(name th, XMLCONCAT(XMLELEMENT(name small,
"base_table_name"), XMLELEMENT(name br), XMLELEMENT(name b,
"name"))))),
'XMLAGG(XMLELEMENT(name tr, xmlconcat(' || list('XMLELEMENT(name
td, "' || "name" || '")') || ')) order by
sa_maintain_order_of_derived_table)',
'declare local temporary table sa_foo
(sa_maintain_order_of_derived_table int default autoincrement, ' ||
list('"' || "name" || '" ' || "domain_name_with_size", ',') || ')',
LIST('"' || "name" || '"', ',')
into html_table_header, xml_query, temp_table_definition,
temp_table_column_list
from sa_describe_query(dynamic_sql_query);
execute immediate temp_table_definition;
execute immediate 'insert into sa_foo(' || temp_table_column_list ||
') ' || dynamic_sql_query;
execute immediate
'select ''<html><head><title>'
|| HTTP_HEADER('@HttpURI')
|| '</title></head><body><h3>'
|| HTTP_HEADER('@HttpURI')
|| '</h3><table border=1>'
|| html_table_header
|| ''' || '
|| xml_query
|| ' || ''</table></body><br><hr><address>SQLAnywhere/'
|| @@version
|| '</address></html>'' into res from sa_foo';
return res;
end;
As an example of using this function, you could create a web service
such as:
create service root
authorization off
user dba
type 'raw'
url on
as select dynamic_sql_to_html(:url);
Using that service, you could then pass in queries directly through
the URL. For example,
http://localhost/SELECT * FROM SYSTABLE
would return a properly formatted HTML result for the query 'SELECT *
FROM SYSTABLE". Of course, there are huge security implications of
allowing any arbitrary query to be executed through the URL, I was
simply using that service it as a simple example to show of how to use
dynamic_sql_to_html() function. And, as I said earlier, since the
dynamic_sql_to_html() code is in your control, you can tweak it to
output the HTML however you like.
Will that workaround work?
durga moganty
Sep 4, 2009, 5:38:40 AM9/4/09
to sql-anywhere-w...@googlegroups.com
Thanks Eric, this would help me a lot...:)
~durga
~durga
Reply all
Reply to author
Forward
0 new messages