process a dynamic query row by row, DBQUERY

Alex_sp · Post by **Alex_sp** » Tue Aug 29, 2006 1:56 am

hi all,
I've to make a dynamic query with dbquery (OR dblookup)

Can i send the result of dbquery to a other map in type tree object form with RUN command?

Example:

SELECT name, last_name FROM customer
WHERE name LIKE 'VAR';
'VAR' is the dynamical parameter.

I've a map with an input card that reads a value for 'VAR' from a file, and an output that executes the query with this value.

At this point, I need to send the result set in type tree object format to another map, but the only way to do that i find is to send the result set to a file and later, a second map recovers the result set in type tree object format without errors.

Can i do without this intermediate file? with RUN command or functional map?
thanks.

jvmerc · Post by **jvmerc** » Tue Aug 29, 2006 8:39 pm

You'd use the DBID in junction with the runmap......

Open DBID create your DB inq file, create a DB name assigning DB type which will include your sid, adapter, user and pw.

From there you can create queries and generate your type tree. The queries can include your variables.....

SELECT name, last_name FROM customer
WHERE name LIKE 'v_name'

You'll need to define an initial/default variable before you can generate the tree (s=refer to DBID pdf)

Once you generate the related tree via DBID you add a card to your runmap pointing to the tree and the DBID file (file.mdq)

At that point you can create a runmap command in you control map(again this is explained in the DBID and mapdesigner pdf)......

=run("map_name",
"' -ID1 '-VAR v_name=''" + name_value + "'wildcard if applicable''")

You may have to play with the punctuation etc..... Also, if you use -t in the command line of the runmaps input card you'll get a DB trace file (.dbl) that will summarize what you actually passed to the runmap.... should help you debug things.

Luck!

Alex_sp · Post by **Alex_sp** » Wed Aug 30, 2006 8:25 am

Hi,

This is a good way to do, but I can't use with that query:

DBQUERY("SELECT id AS idsuceso,codigo AS codsuceso,tipo AS tiposuceso, estado_vehiculo AS estadoveh, estado AS estadosuceso FROM Tabla_sucesos WHERE ("+
+ IF(SIZE(codigo_suceso Datos:IN_Datos_suceso) > 1,"codigo LIKE '"+codigo_suceso Datos:IN_Datos_suceso+"%' AND ")
+ IF(SIZE(direccion Datos:IN_Datos_suceso) > 1,"direccion LIKE '"+direccion Datos:IN_Datos_suceso+"%' AND ")
+ IF(SIZE(estado_suceso Datos:IN_Datos_suceso) > 1,"estado = '"+estado_suceso Datos:IN_Datos_suceso+"' AND ")
+ IF(SIZE(id_vehiculo Datos:IN_Datos_suceso) > 1,"recurso LIKE '"+id_vehiculo Datos:IN_Datos_suceso+"%' ) AND ( ")
+ IF( bomberos Datos:IN_Datos_cuerpos = 1,"(cuerpo = 1 OR ","(")
+ IF( policia Datos:IN_Datos_cuerpos = 1,"cuerpo = 0 OR ")
+ IF( movilidad Datos:IN_Datos_cuerpos= 1,"cuerpo = 3 OR")
+" cuerpo = 2))"," -TRACE traza.txt -DBTYPE ORACLE ...")

The problem is that the query changes also its structure. I've 4th input parameters that can contain empty values, and I can't use LIKE with an empty string in a big data base
thanks for your response

jvmerc · Post by **jvmerc** » Wed Aug 30, 2006 9:47 am

A few options....

Create a stored procedure and call that via runmap.

or
------------------------------------------------------------------------------------
Your DBID query would look more like like

SELECT id AS idsuceso,codigo AS codsuceso,tipo AS tiposuceso, estado_vehiculo AS estadoveh, estado AS estadosuceso
FROM Tabla_sucesos
WHERE codigo LIKE #var1#%
AND direccion LIKE #VAR2#%
AND estado = #VAR3#
AND recurso LIKE #VAR4#%
AND ....

Then your runmap command would look more like....

=IF(SIZE(codigo_suceso Datos:IN_Datos_suceso) > 1
AND SIZE(direccion Datos:IN_Datos_suceso) > 1
AND SIZE(estado_suceso Datos:IN_Datos_suceso) > 1
AND SIZE(id_vehiculo Datos:IN_Datos_suceso) > 1
AND.......
run("map_name",
"' -ID1 '-VAR VAR1=''" + value1 +
"' -VAR VAR2=''" + value2 +
....
''")

------------------------------------------
or

Another option os to run the query and check the conditions on the runmap side. it will return more data than you may want but you should be able to filter out the garbage.

Alex_sp · Post by **Alex_sp** » Fri Sep 01, 2006 1:51 am

I like this solution, I am going to try it by this way.

it works!!!!, finally I put this:

= RUN("GET_RESULT_SELECT.mmc",ECHOIN(1,DBQUERY("SELECT * FROM SD_02_SEVCOS WHERE ("+
+ IF(SIZE(TURNO_SV Column:IN_Disponibles) > 0,"TURNO_SV = '"+TURNO_SV Column:IN_Disponibles+"'")
+ IF(WHEN(SIZE(FSVCO_SV_INI Column:IN_Disponibles) > 8,SIZE(FSVCO_SV_FIN Column:IN_Disponibles) > ,
" AND (FSVCO_SV >= '"+DATETOTEXT(FSVCO_SV_INI Column:IN_Disponibles)+"' AND FSVCO_SV <= '"+DATETOTEXT(FSVCO_SV_FIN Column:IN_Disponibles)+"')")
+")"," -TRACE traza.txt -DBTYPE SQLSVR7 -MDQ dbPolicia.mdq -DBNAME dbPolicia")))

thanks you jvmerc, and sorry for my poor English

jvmerc · Post by **jvmerc** » Fri Sep 01, 2006 6:29 pm

Alex_sp · Post by **Alex_sp** » Mon Sep 04, 2006 9:50 am

jeje, well, I've a new problem

first of all, the input source for the invoked map is set to database and its type tree is the same one as the returned type tree when I executed the previous DBQUERY.

If the resulset returned by the dbquery has no record, the invoked map executes the query defined in its input card. I wanted to invoke the map with an empty resulset instead.

I hope that i explain all well.

jvmerc · Post by **jvmerc** » Mon Sep 04, 2006 8:22 pm

I think I understand....

can you change the tree and make the input optional (0:1) or (0:S)? That way if you don't get data back you simply have an empty dataset and the runmap can handle things accordingly. I alter the DBID generated trees all the time.

Alex_sp · Post by **Alex_sp** » Tue Sep 05, 2006 8:49 am

jvmerc wrote:I think I understand....

can you change the tree and make the input optional (0:1) or (0:S)? That way if you don't get data back you simply have an empty dataset and the runmap can handle things accordingly. I alter the DBID generated trees all the time.

well I solved the problem of no records in resultset sending a null value.

all is running ;p, thank you!!!!