Informix-IWA and Java
A developer couldn't get "SET ENVIRONMENT use_dwa/use_iwa '1'" to take effect from a Java/JDBC application before running queries (createStatement gave "Cannot use a select or any of the database statements in a multi-query prepare"). Art Kagel suggested running the statement via sysdbopen(), or wrapping it in stored procedures (set_iwa/clear_iwa) called on the same connection, and warned the setting is lost if Java uses a different pooled connection. The procedure approach still appeared not to work for the poster. Sandor Szabo then posted a full working demo (mart creation script plus Java code) showing the setting applied with conn.prepareStatement("set environment use_dwa '3'").executeUpdate() on the same connection as the query; no further confirmation from the original poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello Guys:
I have a issue with my java application where I try to connect to informix or
IWA and i can't execute the query because I need establish previously the
following environment variable:
set environment use_dwa '1'; (For example).
i've trying with createStatement but return the next error: Cannot use a
select or any of the database statements in a multi-query prepare.
Do you have any idea to resolve this situation?
Regards.
You should be able to execute the set environment from Java/JDBC. What
error codes do you get back when you try?
Two alternatives:
1) Put the set environment statement into this user's sysdbopen() function
so it gets executed anytime the user id connects to the server, or
2) Create a stored procedure to execute the statement and call that from
Java.
Art
Art S. Kagel, Principal Consultant
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Mon, Jan 13, 2014 at 10:53 AM, LICU GARCIA <licu99@gmail.com> wrote:
> Hello Guys:
>
> I have a issue with my java application where I try to connect to informix
> or
> IWA and i can't execute the query because I need establish previously the
> following environment variable:
> set environment use_dwa '1'; (For example).>
> i've trying with createStatement but return the next error: Cannot use a
> select or any of the database statements in a multi-query prepare.
>
> Do you have any idea to resolve this situation?
>
> Regards.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e012287a8ce753104efdc41e3
Thank you very much!!! I've considered the second option, but for each query I need one store procedure. is possible create only one store procedure and receive the query to execute?
Yes, using dynamic SQL you can pass in a query into a procedure, prepare it, and execute it or open a cursor on it and fetch the data and return it from the procedure. There is one limitation in that any queries that you pass in that way would have to all return the same number and types of data. However, you don't have to do that. Just create a procedure that sets the IWA environment (let's call it set_iwa()) and one that clears it (say clear_iwa()). Then you just execute the set_iwa() procedure after which you can issue any query directly from Java/JDBC and it will be passed on to IWA if possible. When you no longer want queries to be passed to IWA for that session, just execute the clear_iwa() procedure. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jan 13, 2014 at 11:40 AM, LICU GARCIA <licu99@gmail.com> wrote: > Thank you very much!!! > > I've considered the second option, but for each query I need one store > procedure. > is possible create only one store procedure and receive the query to > execute? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3ae4273b20a04efdd0059
Wow, I hadn't thought of that. Thank you. I will do it.
Hello Art Kagel:
Now my code java is the following:
Statement stmt;
ResultSet rs = null;
CallableStatement proc = null;
try {
proc = connection.getConnection().prepareCall("{ call set_iwa(); }");
proc.execute();
stmt = proc.getConnection().createStatement();
rs = stmt.executeQuery(query);
} catch (SQLException e) {
e.printStackTrace();
}
And the store procedure
CREATE PROCEDURE set_iwa()
set environment use_iwa '1';END PROCEDURE;
Both executions are successfull but the second execution don't take the
environment variable.
Do you know what happen with this?
Thank you...
It looks to me like you commented out the call to the procedure. Try it
without the curly braces:
proc = connection.getConnection().prepareCall("call set_iwa();");
Art
Art S. Kagel, Principal Consultant
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Mon, Jan 13, 2014 at 9:17 PM, LICU GARCIA <licu99@gmail.com> wrote:
> Hello Art Kagel:
>
> Now my code java is the following:
>
> Statement stmt;
> ResultSet rs = null;
> CallableStatement proc = null;
> try {
>
> proc = connection.getConnection().prepareCall("{ call set_iwa(); }");
>
> proc.execute();
>
> stmt = proc.getConnection().createStatement();
>
> rs = stmt.executeQuery(query);
> } catch (SQLException e) {
>
> e.printStackTrace();
> }
>
> And the store procedure
>
> CREATE PROCEDURE set_iwa()>
> set environment use_iwa '1';> END PROCEDURE;
>
> Both executions are successfull but the second execution don't take the
> environment variable.
>
> Do you know what happen with this?
>
> Thank you...
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c380c4584a9404efe5049d
I did it but it's the same situation...
Is it possible that the Java system is executing the several statements over separate connections? Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jan 13, 2014 at 9:50 PM, LICU GARCIA <licu99@gmail.com> wrote: > I did it but it's the same situation... > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c2432a6a93c004efe56d02
Maybe. i'll keep trying...
Hi,
To demonstrate how to do this I have created a demo for you:
first create a very simple mart to be accelerated:
=3D=3D=3D=3D=3D=3D CUT =3D=3D=3D=3D=3D=3D
for example:
# optimally edit the following 3 Variables, IWA should be up and runni=
ng
DB=3Dext
DWA=3DDWA
MART=3Dext
USER=3Dinformix
dbacc()
{
dbaccess $* 2>&1 | grep -v ^$
echo '--------------------'
}
cat <<!!! | dbacc -e $DB -
execute function ifx_dropmart('$DWA','$MART');!!!
cat <<!!! | dbacc -e - -
drop database if exists $DB;create database $DB with log;
!!!
cat <<!!! | dbacc -a -e $DB -
create table f(f1 int);
insert into f values(2508);create external table ext_f sameas f using (datafiles('disk:/tmp/f.data=
'));
insert into ext_f select * from f;
create table d(f1 int);
insert into d values(2508);create external table ext_d sameas d using (datafiles('disk:/tmp/d.data=
'));
insert into ext_d select * from d;
set environment use_dwa 'probe cleanup';
set environment use_dwa 'probe start';select {+ avoid_execute} * from ext_f, ext_d where ext_f.f1=3Dext_d.f1;=
set environment use_dwa 'probe stop';
execute procedure ifx_probe2mart('$DB','$MART');
execute function ifx_createmart('$DWA','$MART');
execute function ifx_loadmart('$DWA','$MART','NONE');!!!
cat <<!!! | dbacc -e $DB -
set environment use_dwa '3';
select * from ext_f, ext_d where ext_f.f1=3Dext_d.f1;!!!
=3D=3D=3D=3D=3D=3D CUT =3D=3D=3D=3D=3D=3D
and now the java test prog called san.java
=3D=3D=3D=3D=3D=3D=3D CUT =3D=3D=3D=3D=3D
/*
* java san
* 'jdbc:informix-sqli://myhost:1533:informixserver=3Dmyserver=
;
* user=3D<username>;password=3D<password>'
* HARD coded Database called ext !!
***********************************************************************=
****
*/
import java.sql.*;
import java.util.*;
public class san
{
public static void main(String[] args)
{
if (args.length =3D=3D 0)
{
System.out.println("FAILED: connection URL must be provided=
in
order to run the demo!");
return;
}
String url =3D args[0];
Connection conn =3D null;
Statement stmt =3D null;
StringTokenizer st =3D new StringTokenizer(url, ":");
String token;
String newUrl =3D "";
for (int i =3D 0; i < 4; ++i)
{
if (!st.hasMoreTokens())
{
System.out.println("FAILED: incorrect URL format!");
return;
}
token =3D st.nextToken();
if (newUrl !=3D "")
newUrl +=3D ":";
newUrl +=3D token;
}
newUrl +=3D "/ext";
while (st.hasMoreTokens())
{
newUrl +=3D ":" + st.nextToken();
}
String cmd=3Dnull;
String testName =3D "To test USE_DWA ";
System.out.println(">>>" + testName + " test.");
System.out.println("URL =3D \\\\"" + newUrl + "\\\\"");
try
{
Class.forName("com.informix.jdbc.IfxDriver");
}
catch (Exception e)
{
System.out.println("FAILED: failed to load Informix JDBC
driver.");
}
try
{
conn =3D DriverManager.getConnection(newUrl);
}
catch (SQLException e)
{
System.out.println("FAILED: failed to connect!");
}
System.out.println("Trying to set USE_DWA ...");
try
{
PreparedStatement pst =3D conn.prepareStatement("set enviro=
nment
use_dwa '3'");
pst.executeUpdate();
}
catch (SQLException e)
{
System.out.println("FAILED to set USE_DWA: " + e.toString()=
);
}
try
{
PreparedStatement pstmt =3D conn.prepareStatement("select *=
from
ext_f, ext_d where ext_f.f1=3Dext_d.f1");
ResultSet r =3D pstmt.executeQuery();
while(r.next())
{
int i =3D r.getInt(1);
int i2 =3D r.getInt(1);
// verify result
System.out.println("Returned ext_f.f1 =3D " + i);
System.out.println("Returned ext_d.f1 =3D " + i);
}
r.close();
pstmt.close();
}
catch (SQLException e)
{
System.out.println("FAILED: Fetch statement failed: " +
e.getMessage());
}
System.out.println("onstat -h his or onstat -m to check if
success ...");
}
}
=3D=3D=3D=3D=3D=3D CUT =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Kind regards
Sandor
-----------------------------------------------------------------------=
--------------------------------------------------------------------
IBM Deutschland Research & Development GmbH /
Vorsitzende des Aufsichtsrats: Martina Koederitz
Gesch=E4ftsf=FChrung: Dirk Wittkopp Sitz der Gesellschaft: B=F6blingen =
/
Registergericht: Amtsgericht Stuttgart, HRB 243294
=
"LICU GARCIA" =
<licu99@gmail.com =
> =
To
Sent by: ids@iiug.org, =
ids-bounces@iiug. =
cc
org =
Subj=
ect
Informix-IWA and Java [32196] =
13/01/2014 16:53 =
=
=
Please respond to =
ids@iiug.org =
=
=
Hello Guys:
I have a issue with my java application where I try to connect to infor=
mix
or
IWA and i can't execute the query because I need establish previously t=
he
following environment variable:
set environment use_dwa '1'; (For example).
i've trying with createStatement but return the next error: Cannot use =
a
select or any of the database statements in a multi-query prepare.
Do you have any idea to resolve this situation?
Regards.
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=