OpenEuphoria

2. Re: MySql handler syntax

• Posted by Matt Lewis <matthewwalkerlewis at yahoo.com> Oct 25, 2004
• 632 views
• Last edited Oct 26, 2004

George Walters wrote:
> 
> Matt, I'm still having memory problem with a large inventory database
> (22,000 records). I'm concerned because I do have some customers with
> more than 80,000 records.I've increased the computer memory to 750MB but
> although
> it reads farther, it still crashed w/o enough memory. So there's a problem 
> somewhere that's way over my head. Would it help if I emailed you the mysql
> table to play with?

OK, I found a couple of memory leaks in getColumnHeaders() and getData().
I allocate(h_ptr) but never free it unless there is an error.  Add in a
call to free in both of those functions after the line:

cols = peek4u( h_ptr )
free( h_ptr )

Since you make lots more calls than just the 22,000, this could be the
issue.

> I'm also wondering if I can use mysql "HANDLER syntax" (page 521 in the mysql
> manual). It seems it would be more appropriate to the type of interactive
> file maintenance my software is using.  The reports should still use a cursor.
> This would be for the file maitenance and data entry programs. It would solve
> my "READPREVIOUS" problem which there's no realistic SQL syntax to handle it.

I tried using HANDLER, and it works fine...faster, in fact, than a series
of SELECTs.  Basically, it's just another piece of SQL that returns a
recordset, as far as my code is concerned.  I used:

void = execDirectODBC( mysql, "handler test open;")
void = execDirectODBC( mysql, "handler test read first;")
puts(1,"\n")
for i = 1 to 100000 do
	void = execDirectODBC( mysql, sprintf("handler test read next;", {i-1}))

end for

Also, if you use the sequence.e file I sent you to compress/decompress
Euphoria objects, you can store them in BLOB fields.  I created 
a table named test:

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| number | varchar(50) | YES  |     | NULL    |       |
| seq    | mediumblob  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

I added 100000 records (while trying to run myself out of memory), 
before I actually created the seq field.  Then I did the following 
to put some BLOB data in there:

stmt = prepareSQL( mysql, "update test set seq=? where id=?")
void = bindParameter( stmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_BINARY,
#FFFF, 0, 0 )
void = bindParameter( stmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 4, 0, 0
)
for i = 1 to 100000 do
	setParameter( stmt, 1, compress({sprint( i )}), SQL_C_DEFAULT, SQL_BINARY )
	setParameter( stmt, 2, i, SQL_C_LONG, SQL_INTEGER )
	void = executeSQL( stmt )
end for

The data comes and goes just fine.  Also tried running through records 
with HANDLER and with a cursor, and HANDLER seems faster up to about
3000 records (on my machine), but this is basically the overhead
of the initial select statement.  Subsequent calls to moveToRecord()
are extremely fast, since the data is already prefetched.  Of course,
this may cause memory problems, and your speed might be fine as it
is, so I'll leave this to you.  Just thought it was interesting, because
I've never worked with MySQL before, and the HANDLER statement was 
interesting.

Matt Lewis


Matt Lewis

4. Re: MySql handler syntax

• Posted by George Walters <gwalters at sc.rr.com> Oct 27, 2004
• 665 views

Just wanted to post the rest of this story. The memory problem I've been 
having was in my program, not freeing some handles. Matt's ODBC routines
were solid and not the culprit. My lack of understanding how they should
be used was the problem....So on with development. Matt found the problem 
in my code. Thanks for the help Matt!!

george