TSQLQuery.OPEN consumes all memory and crashes my program lazarus / 16-Databases ---------------------------------------------------------------------- skinnyhate · 2013-07-21 04:54 Hi. I am using Lazarus v1.0.8, FPC 2.6.2, SVN Revision 40573. I have a 32bit machine running Windows 7. I have a program which uses TSQLQuery to get data from a database. So long as the recordsets returned are small, (such as a few dozen each with say 15 to 20 fields of string values), I have no problem. TSQLQuery.open works and I can get the record count, field count, etc, and dump the data into a TStringGrid. However, as the recordset gets up to somewhere between 50 and 100, sometimes SQLQuery.open fails and I can see on Windows Resource Monitor that it is consuming all of my available RAM (like 1.5GB to 2GB, depending on what is available at the time)! How can that be, with just a few dozen records of string values?? I have seen several posts on this topic, in this forum, some dating back a few years now, saying similar stuff. Is there an issue with SQLQuery.next not releasing memory or something? Some other stupid setting I have missed? I have set PacketRecords to -1, to 10, to whatever, makes no difference. I have tried going into Project Settings/Code Generation/ and playing with Heap size (-Ch) but that makes no difference as memory is allocated on the fly anyway. Below is some source code. There are two chuncks listed here, the form onCreate() procedure, and a procedure called etAudit() which is where the query is run and the records are dumped into a TStringGrid named grdAuditGrid in a loop. Some variable names, usernames, passwords etc are changed to rubbish for obvious reasons (this is for a company). Note, for the SQL stuff, I am using the little icons which you drag and drop on your application form for TSQLQuery, TOracleConnection and TSQLTransaction. I have setup the TSQLTransaction and TOracleConnection to use each other, and the TSQLQuery is setup to use both of those. Don't think I am missing anything there. One more thing... I can delete all the code in the query section except for SQLQuery.open and SQLQuery.close and it will crash on SQLQuery.open... there is something wrong with that step which consumes all my memory. I am desperate here, so contact me via this forum or through skinnyhate@gmail.com if you have any suggestions. onCreate() is where the connection is made and it is always open as the query can be run several times before the program is closed. procedure TfrmEtAudit.FormCreate(Sender: TObject); begin   username:='someusername';   password:='somepassword';   host_name:='somehostname';   database_name:='somedatabasename';   MNDB_OracleConnection.HostName:=host_name;   MNDB_OracleConnection.DatabaseName:=database_name;   MNDB_OracleConnection.UserName:=username;   MNDB_OracleConnection.Password:=password;   MNDB_OracleConnection.Open; end; ---------------------------------------------------------------------- ----------------------------------------------------------------- procedure TfrmEtAudit.cmdAuditClick(Sender: TObject); var   row, col, fieldCount, recordCount:integer;   myCluster:string; begin   myCluster:=cmboCluster.Text;   // initialize the grid row (row 0 is the header row, so start at row 1)   row:=1;   col:=0;   if myCluster='' then   begin     showmessage('Please select a cluster first.');   end   else   begin     try       SQLQueryGetClusterInfo.Params.ParamByName('myCluster').AsString: =myCluster;       // now run the query       SQLQueryGetClusterInfo.Open;  //<--This is where is crashes depending on the recordset size, otherwise the rest works fine.       fieldCount:=SQLQueryGetClusterInfo.FieldCount;       recordCount:=SQLQueryGetClusterInfo.RecordCount;       lblTotalNumberOfRecords.Caption:=inttostr(recordCount);       while not SQLQueryGetClusterInfo.Eof do        begin          grdAuditGrid.RowCount:= row+1; // add another row to the table          for col := 0 to fieldCount-1  do          begin            grdAuditGrid.Cells[col, row] := SQLQueryGetClusterInfo.Fields.FieldByNumber(col+1).AsString; //Write those fields to the TStringGrid          end;          row := row + 1;          application.ProcessMessages;          SQLQueryGetClusterInfo.Next;        end;       SQLQueryGetClusterInfo.Close;     except       on E:Exception do       begin           showmessage(E.ClassName+' error raised with message: '+E.Message);         end;     end;   end; end; ---------------------------------------------------------------------- BigChimp · 2013-07-21 05:29 Thanks for that comprehensive post. Sorry you're having problems. 1. I'm assuming you're working against an Oracle database. What version? 2. Posting snippets may help but in this case a minimum FPC test program that demonstrates the problem on your system may be better so I/others can test it (e.g. using FPC trunk). See http://wiki.lazarus.freepascal.org/Database_bug_reporting#FreePascal 3. Alternatively to 2, you might want to upload a stripped down Lazarus version of your sourcecode with database details (password, host etc) removed. (Menu project/publish project, zip up the directory and post) Please be sure to include the DDL for your tables however otherwise it'll be more work to test (and I won't do it because I'm too lazy ). Option 2 would be preferable because stripping out the Lazarus layer removes an extra layer of complexity and, also, an FPC program can be more easily changed to a sqldb test case (see http://wiki.lazarus.free pascal.org/Databases#Running_FPC_database_tests) 4. The code you posted seems ok. Perhaps you could do a beginupdate...endupdate for the grid control when retrieving the query instead of messing with processmessages. Probably won't help the query problems 5. You could try using the ODBC connector with your Oracle db and see if the same issue occurs; sounds like a good way to isolate the program (either the Oracle driver/sqldb interface to it or the rest of sqldb/Lazarus db handling). HTH ---------------------------------------------------------------------- skinnyhate · 2013-07-21 05:50 Thanks Big Chimp. 1. Oracle11g 2. I will do this later tonight, no worries. 3. See 2, your preference sounds best. 4. I will worry about optimising my tables later, but that sounds like a good idea. I only added the application.processmessages because I thought it might help solve the problem. Later I realized the program was crashing long before it got to that point. 5. I run Oracle SQL Developer on my machine as well. The queries are copy+paste from there into SQLQuery.SQL.text. I have no troubles with SQL Developer... it will happily return 10,000 records (it may take some time but there are no issues). Chat later, and thanks again. ---------------------------------------------------------------------- BigChimp · 2013-07-21 05:59 Thanks for the response. Quote from: skinnyhate on July 21, 2013, 07:50:49 am5. I run Oracle SQL Developer on my machine as well. The queries are copy+paste from there into SQLQuery.SQL.text. I have no troubles with SQL Developer... it will happily return 10,000 records (it may take some time but there are no issues). Just to be sure I made myself clear: I meant rewrite the code to use the SQLDB ODBC connector (http://wiki.lazarus.freepascal.org/ODBCConn). This way you bypass the sqldb Oracle code... (of course, you might hit slqdb ODBC bugs or it may be that the bugs you're hitting are actually in the sqldb query part). ---------------------------------------------------------------------- ludob · 2013-07-21 06:32 Can you share the actual query with us and the schemas of the tables being used? If it is too sensitive, PM it to me.  Oracle has a lot of complex data types that are not supported by sqldb. Types that are unfortunately used in some system views and functions. Since the memory usage is so enormous, I guess one of the types is misinterpreted by sqldb and results in huge memory allocations for the field. You could also try yourself and cast fields that have a "weird" data type to well known data types in your query. Try dropping one field at the time to see what is the culprit. ---------------------------------------------------------------------- BigChimp · 2013-07-21 07:01 @Ludo: would it help to enable logging (http://wiki.lazarus.freepascal .org/SqlDBHowto#Troubleshooting:_TSQLConnection_logging) to see whether requests for unsupported datatypes get sent to the server/what system views are requested from the server? (He did write in his OP that he's just querying string values though - hoping that would be char or varchars then) ---------------------------------------------------------------------- skinnyhate · 2013-07-21 08:45 Hi, thanks all. Big Chimp, dude. I followed #5 and it works. I used ODBC and it works. So far, anyway. I have to push it a bit harder to know for sure. Thanks man. However, this leaves me with another problem. The app I am working on will be used by several others, perhaps nationally, and they will not have this ODBC connection setup. All of our programs use the Oracle driver. Sigh. I still kind of need to know where the limitation is with the other driver... While poking around in the ODBC manager, I checked out my settings for my Oracle driver. There are some fields, Fetch Buffer Size and Cache Buffer Size, set to 64000 and 20, respectively. Is there any chance these setting might have something to do with it? Have you any other clues on the issue? Cheers ---------------------------------------------------------------------- BigChimp · 2013-07-21 08:56 Quote from: skinnyhate on July 21, 2013, 10:45:48 amBig Chimp, dude. I followed #5 and it works. I used ODBC and it works. So far, anyway. I have to push it a bit harder to know for sure. Thanks man. Good news Quote from: skinnyhate on July 21, 2013, 10:45:48 amHowever, this leaves me with another problem. The app I am working on will be used by several others, perhaps nationally, and they will not have this ODBC connection setup. All of our programs use the Oracle driver. Sigh. I still kind of need to know where the limitation is with the other driver... Depends on what you install. Even with the Oracle "instant" client, IIRC, you can install the ODBC driver as well. You might be able to set up a DSN-less connection (see that wiki page) so users don't need to set up connections in ODBC manager. Quote from: skinnyhate on July 21, 2013, 10:45:48 amWhile poking around in the ODBC manager, I checked out my settings for my Oracle driver. There are some fields, Fetch Buffer Size and Cache Buffer Size, set to 64000 and 20, respectively. Is there any chance these setting might have something to do with it? Have you any other clues on the issue? Sorry, don't know about that one. If you want to, you might still follow #2 above so we can have a look and see if those issues can be solved in a later FPC 2.6.x release for sqldb Oracle - perhaps the problems are fixed in FPC trunk as well. ---------------------------------------------------------------------- ludob · 2013-07-21 11:09 Quote from: BigChimp on July 21, 2013, 09:01:21 am@Ludo: would it help to enable logging (http://wiki.lazarus.freepascal.org/SqlDBHowto#Troub leshooting:_TSQLConnection_logging) to see whether requests for unsupported datatypes get sent to the server/what system views are requested from the server? (He did write in his OP that he's just querying string values though - hoping that would be char or varchars then) The log wouldn't give any extra information. The contents of SQLQueryGetClusterInfo.SQL.Text would already be fine. Oracle's system views are well documented. If it uses other tables then the schema is needed. The Fetch buffer size determines how many rows the ODBC driver would load in one go. Like indicated before, I don't believe this is a problem with getting too many data but a problem of TBufDataset allocating too much memory for a field. Caused by misinterpreting the size of a field for example. ---------------------------------------------------------------------- BigChimp · 2013-08-05 14:28 @Skinnyhate: did you get any further in figuring out what was wrong/writing a test program for us to grind our teeth on ? ---------------------------------------------------------------------- skinnyhate · 2013-08-07 04:14 Hi BigChimp et al, no, I have not gone further with this yet, but I have been meaning to. The ODBC driver solution you recommended worked as I mentioned before, without changing a single line of code, and I have not had time to do anything else so I ran with that. Next week I am going on vacation for three weeks overseas... perhaps when I get back I can spend more time on this. There is one other thing I intend to try when I get the time. When I first started using Lazarus, I used version 0.9 something or other, and then went to 1.0.8. I am certain, looking back, that I wrote similar queries using the TOracleConnection and never had this outofmemory error. I need to go back and compile this new code using the old version and see if I have the same problem. I assume the results of that would interest you so I will let you know when I do it, but not likely for a few weeks. ---------------------------------------------------------------------- BigChimp · 2013-08-07 05:42 Hi Skinnyhate, Thanks for the post. Enjoy your holidays & yes, having info on whether the error occurred on older versions would also be helpful. ---------------------------------------------------------------------- mith · 2013-12-31 08:34 Hi, I know it's old topic, but it is always worth to try asking. I unfortunately have the same memory problem - posted here: http://forum.lazarus.freepascal.org/index.php/topic,23078.0.html. Has anyone solved it without using odbc? Thanks in advance for reply.