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 ?
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:
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:
BeginTragsaction:
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.
PreparedStatement:
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:
USE [master]
GO
CREATE LOGIN [MYUSER] WITH PASSWORD=N'VerySecret', DEFAULT_DATABASE=[MyDb], CHECK_POLICY=OFF
GO
USE [MyDb]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER]
GO
ALTER ROLE [db_datareader] ADD MEMBER [MYUSER]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [MYUSER]
GO
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?)
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 .
