Help with querys at Studio Creator.
Hi,
I need to know how to do a query with many criterias.
I try to use "=?" in edit window at studio creator, but I need put diferent criterias in query,
and sometimes, some values still empty. So, if they still empty, the result of query
show some results wrong. I try to use "or" in place of "and", but don't work.
My query:
SELECT ALL PUBLIC.s_tabela1.idsolic,
PUBLIC.s_tabela1.matricula,
PUBLIC.s_tabela1.nome,
PUBLIC.s_tabela1.diretoria,
PUBLIC.s_tabela1.setor,
PUBLIC.s_tabela2.descricao,
PUBLIC.s_tabela3.status
FROM PUBLIC.s_tabela1, PUBLIC.s_tabela2, PUBLIC.s_tabela3
WHERE ( PUBLIC.s_tabela1.matricula = ? OR PUBLIC.s_tabela1.diretoria = ? OR PUBLIC.s_tabela1.setor = ? OR
PUBLIC.s_tabela3.status = ?
AND PUBLIC.s_tabela1.idlocalidade = PUBLIC.s_tabela2.id
AND PUBLIC.s_tabela1.idsolic = PUBLIC.s_tabela3.id_solicitante )
ORDER BY PUBLIC.s_tabela1.idsolic DESC,
PUBLIC.s_tabela3.status DESC
When I don't have any value to one (or many) criterias, query must ignore other criterias.
Sorry for my poor english, but I try... =D
I don't speak (or write) english.... sorry again...
Thanx
Glauber
# 1
I'm not an expert by any means, but I think you have to create your query in your program. I'm faced with the same situation and that's what I do.
If you want to execute a query with parameters and the number of parameters is not known or can change at runtime, you have to write a custom query.
Get a reference to the row set, call setCommand and pass the query you want to execute, then set the parameters, and refresh the associated data provider.
For example,
RowSet bidRS = getSessionBean1().getBidRS();
bidRS.setCommand("<Query>");
bidRS.setInt(1, <Parameter_1>);
bidRS.setInt(2, <Parameter_2>);
bidDP.refresh();
Here, bidRS is the result set whose underlying query has a variable number of parameters. Once you have defined the query, you then set the parameters (making sure you define the correct number and data type). Finally, you refresh the data provider which forces the query to be executed.
# 2
Hello, there is a way to build sql querys without using a rowset and sending parameters to build dynamically your query, here is an example:
com.sun.sql.rowset.CachedRowSetXImpl pkRowSet4 = new com.sun.sql.rowset.CachedRowSetXImpl();
int temp1= 0;
float valor = 0;
String value ="":
try {
pkRowSet4.setDataSourceName("java:comp/env/jdbc/database");
pkRowSet4.setCommand("SELECT * FROM table where id_table=" + value);
pkRowSet4.execute();
temp1 = pkRowSet4.size();
pkRowSet4.first();
for(int x = 0; x < temp1; x++){
valor = valor + pkRowSet4.getInteger("id_table");
staticText19.setText(pkRowSet4.getString("name"));
pkRowSet4.next();
} } catch (Exception ex) {
error("detail " + ex);
}
if u r creative u can use this code to genereate a lot of querys, catching all the data u want from a table, i hope it helps!.
Belthazor
