Hi,

I have a script that uses dynamic sql and tough the results are correct the script (Oracle driver) runs out of memory. After a few thousand output records the Baan Oracle driver reaches it's allocated limit of 256Mb.
I think the dynamic query is correct but maybe I'm doiing something wrong with the sql statements.

The dynamic sql looks like :
string sql_query_1(1500)
long sql_id_1

sql_query_1 = "select tdsls030.cuno, tdsls030.item, tdsls030.pric, tdsls030.stdt, tdsls030.tdat, tdsls030.gnpr, tdsls030.disc from tdsls030" &
" where tdsls030.item = " & DQ & i.item & DQ & " and tdsls030.cuno in (" & g.cuno.lijst & ")" &
" and tdsls030.stdt <= " & str$(huidige.datum) & " and tdsls030.tdat >= " & str$(huidige.datum) &
" order by tdsls030.tdat desc as set with 1 rows"

sql_id_1 = sql.parse(sql_query_1)

rv = sql.select.bind(sql_id_1, 1, tdsls030.cuno)
rv = sql.select.bind(sql_id_1, 2, tdsls030.item)
rv = sql.select.bind(sql_id_1, 3, tdsls030.pric)
rv = sql.select.bind(sql_id_1, 4, tdsls030.stdt)
rv = sql.select.bind(sql_id_1, 5, tdsls030.tdat)
rv = sql.select.bind(sql_id_1, 6, tdsls030.gnpr)
rv = sql.select.bind(sql_id_1, 7, tdsls030.disc)

rv = sql.exec(sql_id_1)

while (true)
on case ( sql.fetch(sql_id_1) )
case eendfile:
break
case enorec:
break
case 0:
| Record gevonden.
#IF TRACING
rep.line = sprintf$("cuno.item.level.1 cuno:%s item:%s gnpr:%s pric:%15.6f disc:%13.2f std:%D(%d-%m-%Y) tdat:%D(%d-%m-%Y)",
tdsls030.cuno, tdsls030.item, enum.descr$("tcgnpr",tdsls030.gnpr), tdsls030.pric, tdsls030.disc, tdsls030.stdt, tdsls030.tdat)
rprt_send()
#ENDIF
o.gnpr = enum.descr$("tcgnpr",tdsls030.gnpr)
voeg.toe.aan.prijs.array(tdsls030.pric, tdsls030.disc, o.gnpr, sprintf$("cuno.item-%s-%s", tdsls030.cuno, tdsls030.item) )
continue
endcase
break
endwhile

sql.break(sql_id_1)
sql.close(sql_id_1)


Which parses to :

select tdsls030.cuno, tdsls030.item, tdsls030.pric, tdsls030.stdt, tdsls030.tdat, tdsls030.gnpr, tdsls030.disc from tdsls030 where tdsls030.item = " 000W" and tdsls030.cuno in ("B06760","ZXXXX4","E00023","E00075","E00123","E00137","E00138","E00145","E00153","E00187","E00211","E00313","E00349") and tdsls030.stdt <= 734021 and tdsls030.tdat >= 734021 order by tdsls030.tdat desc as set with 1 rows

I hope someone can help me with this.

The function "voeg.toe.aan.prijs.array" adds the results to a list.

regards,
Walter

Anyone ??

In the meanwhile...I removed the while loop since the query only produces 1 or 0 records per fetch.

For anyone who is interested.

I changed the code "tdsls030.cuno in (client1, client2, etc)" to a number of "tdsls030.cuno = client1 or tdsls030.cuno = client2" statements and the problem (I'll call it a memory leak) has disappeared.

Walter

Thanks for posting the solution. Not sure why it happens with the original everything looked good to me.