Hello,

I'm using mimer DB and because of performance problems, I want to do
the following:

I have the following example table:
create table MyTable
(
ID INT PRIMARY KEY DEFAULT NEXT_VALUE OF someSequence,
-- additional fields...
);

I want to fetch all id's of a certain range, but I don't want to
return them like regular rows, i.e. one row for each result.
Is it possible to write a procedure to collect all the id's and store
them into a blog or something and then just perform one return. This
way I'll only need one "fetch" from my C code, which fetches all the
resulting id's at once.

Thankfull for any kind of help or hints.
BR
//Kid

It's possible (but maybe not not abvisable...)

Anyway, here's a function that returns all the id's of a specified
range:

create function MyTable_IDs (minval int, maxval int)
returns varchar(15000)
reads sql data
begin
declare resstr varchar(15000) default '';
declare ival integer;
declare c cursor for select id from mytable
where id >= minval
and id <= maxval;
open c;
begin
declare exit handler for not found close c;
loop
fetch c into ival;
set resstr = resstr || ',' || cast(ival as varchar(11));
end loop;
end;
return substring(resstr from 2);
end


HTH,
Jarl



dos.fishing (AT) gmail (DOT) com wrote in news:1174045095.927451.191090
@b75g2000hsg.googlegroups.com:

Thanks alot Jarl! =)
I think this is exactly what I was searching for. We'll see next week
if it works in reality!

BR
//Kid

Thinking of performance and memory usage:
Anyone know a way of returning the id's in a blog as binary values.
Returning the id's in a varchar is an option, but requires parsing the
resulting string and perhaps more memory compared to storing the
binary values.

BR
//Kid

Varchar is fine. (You can't even convert from integer to binary - that
CAST combination is not valid.)

/Jarl


On Mar 20, 10:34 am, dos.fish... (AT) gmail (DOT) com wrote:

Thanks for the info Jarl.

Since you seem to work for mimer and have some insights into the core
of the db, you might help me with the following:
I'm using mimer sql mobile and want to fetch some id's as mentioned
above.

Every fetch from C simply takes too much time considering the amount
of id's the procedure could return.

Do you have idéas how I could improve this?

//Kid


On 20 Mar, 13:02, j... (AT) mimer (DOT) com wrote:

Have you tried array-fetch? If you set the rowset size to e.g. 50, you
will get 50 ID's in just one FETCH. (Rowset size is set by a call to
SQLSetStmtAttr with an Attribute argument of SQL_ATTR_ROW_ARRAY_SIZE.
Read more about this in the ODBC documentation included in the Mimer
SQL Mobile documentation.)

You can also get some performance tricks here:
http://developer.mimer.com/howto/howto_26.htm

Good luck!
Jarl

On Mar 21, 8:54 am, dos.fish... (AT) gmail (DOT) com wrote: