Shouldn't case #57978 be reopened?

Just ran into a problem with a program that loads about 70000 records via a prepared statement with 40 parameters (8 character, 32 Floats).
Under 2019R1.1 this would run fast and leak no memory but objects. With 2019R3.1 it runs VERY slowly (factor 10) and consumes memory (but no objects) until it finally crashes because the memory exceeds 4GB (32 Bit app).

This happens on windows 10 Build 1903 using the ODBC plugin for a MAXDB database.
Doing some testing I found that memory will leak in proportion to the number of parameters, on MaxDb more than on SqlServer but still enough to cause trouble.

I found case 57978 and that it was marked as fixed & verified, which does not seem to be the case.

I tested with a stripped down version of my program that uses SqlServer. Should I add my test program here, append it to the existing feedback case or
start a new case ?

have you tested it with “Xojo 2020 Release 1 Build 49308” ?
have you looked if there are newer odbc driver for windows and this MAXDB?

So far tested only with 2019R3.1 because that has a date later than the “fixed and verified” statement.

The odbc drivers I used for SqlServer and MaxDb are pretty current and work perfectly with 2019R1.1

I will eventually have a look at the beta but as you know I may not post about it here.

This post was mainly to see whether there are other users that do NOT have this problem with R3.1 and ODBC.

ok, a better thread title would be “use someone MaxDb with 2019R3.1” :wink:
i await many fixes in next release.
i not have MAXDB for a test :frowning:

No, it’s not!
As I said I stripped things down to a Testprogram that uses SQLSERVER and less parameters (8 string params, 8 float params) which is sufficient to show the memory go up like this:

08:29:47 : TestMemLeak.exe Launched
08:31:00 : *RecNo=500, memDelta=94,292 ObjectCount=50
08:31:02 : *RecNo=1000, memDelta=101,060 ObjectCount=50
08:31:04 : *RecNo=1500, memDelta=108,532 ObjectCount=50
08:31:07 : *RecNo=2000, memDelta=114,732 ObjectCount=50

08:36:38 : *RecNo=69000, memDelta=1,021,892 ObjectCount=50
08:36:40 : *RecNo=69500, memDelta=1,028,644 ObjectCount=50
08:36:43 : *RecNo=70000, memDelta=1,035,668 ObjectCount=50
08:36:55 : TestMemLeak.exe Ended

Every 500 records the test prog would commit and log recordno, memory consumption and object count. The same thing for MaxDb looks very similar.

with SQLSERVER i could test and verify if you link your example xojo project. or just make a new feedback bug open.

Ok, so here is a link to a Zip file containing a small TEst project along with a script to create Database and Test table.

the use of CommitTransaction is wrong, there is no BeginTransaction in your test app.
and i think ODBCPreparedStatement is not needed because ExecuteSql got new parameter ParamArray.
seems you mix old and new classes together.

First of all, thank you for your replies. Now to the points you mention:

Well, the connect to the DB sets Autocommit = OFF which - so far - was sufficient for all ODBC drivers to always be in a transaction.
But just to be sure I tested (did you?) and I found no difference in behavior.

Just because Xojo now allows you to use something like a “single use” prepared statement does not mean true prepared statements are deprecated. Avoiding Sql-injections was and still is just one reason for using them. Dramatically speeding up execution of repeated statements is another benefit (so the optimizer of the dbms has to do its work only once etc.) Finally it allows to pass unusual parameter datatypes. Btw, all this is also stated in the language reference:-)

Nevertheless I did run some tests using BeginTransaction and using Db.ExecuteSql(…, Paramlist) . To my surprise there was no difference to runs using PreparedStatement, not in memory consumption and not in the time required.

This raises the question to the Xojo engineers whether OdbcPreparedStatement really is what it pretends to be or whether it is prepared for every execution call???

jo, i just wrote what i saw,
i had trouble with using the sql server for some reason.
i created the database, add a user but can’t connect at my system via odbc .
i can’t test it yesterday.

@Markus Rauch
assuming that you already created database MyDb, here is how to add a login/sqluser MYUSER. Execute the following lines in SqlServer Management Studio:


Then in the sample prog edit Method ConnectDb. In the connection string
“DRIVER={SQL Server};Provider=MSDASQL;server=;database=MyDb;uid=MYUSER;pwd=VerySecret”
just needs to be replaced by your actual SqlServer’s network name (eg. \\myserver) for a standard (unnamed) instance.

If you still can not connect, you might look at:

  • SqlServer configuration (is TcpIp enabled? Which port?)
  • firewall settings on the server.

at soon u use conDb.ExecuteSQL the runtime.memoryused goes up. Xojo 2019r3.1 & Xojo2020r1_49386_Windows.

btw the name,passwort login mode for sql server 2019 dev is diabled by default^^
my odbc connector tool can’t say that^^

i made a new case #60702
if you open always old issues the evaluating get wrong.

Uups, forgot to mention that you habe to enable SqlLogins for the instance first. Since 2005 it has been that way, Microsoft wants to push you into using windows domain accounts instead of sql users. Thank you for not giving up!

As for the feedback case, thanks again, but in the meantime I myself added case #60679 .