[SOLVED] Reading records sequentially

[TyneBridges]

The following code is largely based on an example sent to me by BigChimp as a response to my thread "Create (MSSQL) Database in code". Once again I'm pulling out my hair over something that looks simple - opening a database and reading a single field's content into each row of a grid.

I tried initially using a TDBGrid but, when that didn't work, switched to a TStringGrid which also failed. Theoretically the TDBGrid will just show the content automatically when correctly connected but, for whatever reason, mine didn't.

With the TStringGrid the problem doesn't seem to be with the grid itself, but the way the data is read. At the point of opening my query (Q), Q.Eof immediately returns true, so my grid is empty and my counter remains at zero. However, Q.RecordCount correctly shows 6 - the number of tables in my database, whose names I want to display.

As far as I can tell, I've set all the important parameters and controls exactly as in BigChimp's example, which is working for me. Can anyone see if I've missed something really obvious?

{ Q (TSQLQuery) Database already set to Conn
        Q.Datasource set to DS
        Tr (TSQLTransaction) Database set to Conn
        DS.Dataset set to Q }
      Conn.Connected:= True;
      MsgText.Text:= 'Connected to master database';
      // Check for existence of the Bookings database.
      Conn.Connected:= False;
      Try
         // Attempt to connect to Bookings
         Conn.DatabaseName:= DN;
         // Enable autocommit so that Create will work
         Conn.Params.Add('AutoCommit=True');
         Conn.Connected:= True;
         // Error 911 indicates database doesn't exist
         // If no error (lines below would be skipped), show tables in Bookings Database
         // Close connection and query before reopening to ensure we can read data sequentially
         MsgText.Text:= 'Connected to ' + DN + ' database';
         Conn.Connected:= False;
         Q.Close;

         Conn.Open;
         Instruc:= 'Select Name from Sys.Tables order by Name';
         Q.SQL.Text:= Instruc;
         Tr.StartTransaction;
         Q.Open;
         Counter:= 0;
         // Recordcount shows 6 records here but Eof is true as soon as query is opened & grid is blank
         While Not Q.Eof do
         begin
              Inc(Counter);
              DBG.Cells[0, Counter]:= Q.FieldByName('Name').AsString;
              Q.Next;
         end;
         ShowMessage(IntToStr(Q.RecordCount));
         Q.Close;
         Tr.Commit;
         ShowMessage('Rows returned: ' + IntToStr(Counter));

Thanks.

[taazz]

1) MSSQL database connections have problems with the data cursor, they say that you should not rely on it being on the 1st record after a successful data retrieval.

so changing your to add
Q.first;
While Not Q.Eof do

should solve that problem.
Keep in mind that recordcount might not be very reliable as well it depends on the type of cursor you have.

[TyneBridges]

Thanks for your advice, taazz. I can understand the principle. However, adding the line "Q.First" immediately before "While Not Q.Eof do" just throws the error "Operation cannot be performed on an inactive dataset".

I don't understand why - as far as I can see, the dataset ISN'T inactive - but removing it gives me the program as before; no errors, but no data shown either.

[TyneBridges]

I still get the "inactive dataset" error if I amend my code to the following: even more baffling.

Q.Open;
Counter:= 0;
Q.Active:= True;
Q.First;
While Not Q.Eof do   

Does anyone know if there's a tutorial that explains the syntax in detail? I don't understand the difference between (for example) TQuery.Open and TQuery.Active:= True; or TMSSQLTransaction.StartTransaction and TMSSQLTransaction.Open

[taazz]

from the SQL component set source code.

Code: [Select]

Procedure TDataset.Open;
begin
  Active:=True;
end;

Also even if you activate the query with open try to see what the code does on Q.IsEmpty

[TyneBridges]

from the SQL component set source code.

Code: [Select]

Procedure TDataset.Open;
begin
  Active:=True;
end;

Also even if you activate the query with open try to see what the code does on Q.IsEmpty

The following gives me 'Query is empty', even if I move the test to the end of my While loop, where the Recordcount shows as 6. (Actually, this is what I'd expect - Eof is true at the beginning of the loop, so it just gets skipped.)

Instruc:= 'Select Name from Sys.Tables order by Name';
Q.SQL.Text:= Instruc;
Tr.StartTransaction;
Q.Open;
Counter:= 0;
If Q.IsEmpty then ShowMessage('Query is empty') else ShowMessage('Query is not empty');       

I gather from your quotation that opening a dataset should automatically make it active. However, I don't understand how I would open the dataset, as I'm not using a TDataSet directly - I thought its use was implicit in the other components. I am using a TDatasource, a TSQLTransaction and a TMSSQLConnection, which the documentation seems to say are all I need.   

[taazz]

TMSSQLConnection = manages connections to the server some servers call it sessions.
TSQLTransaction     = a database transaction for action on the server a transaction is opened and closed.
TSQLQuery              = sends an sql command to the server through the TConnection option and caches
                                   the data rows locally so you can browse through them. Manages data changes
                                   and applys them back to server when the Applyupdate method is called
Tdatasource            = The bridge between the TSQLQuery and your Dataaware components it sits
                                   in between, monitors the SQLQuery for changes and informs the data aware
                                   controls when they appear.
TSQLQuery descends from TDataset which means it has all its methods and properties. TDataset is a semi abstract class that is used only as a base for other specific classes like the SQLQuery or the Database specific datasets like the IBX/MDO components for the firebird server.

with out a query there is no way you can retrieve data from the server and if you use a SQLQuery then that is a TDataset component. In any event calling open and returning an inactive dataset I think might be a problem I have no access to MSSQL Server at the moment so I cant check any farther for you.

[TyneBridges]

Thanks, taazz. It's really starting to look like a bug in the components: from what you say, the line Q.Open should open the underlying dataset and make it active (meaning that the line Q.Active:= True is redundant). However, if I then try to set the cursor to the first record, I get the error saying the dataset is inactive. If it's a bug, then I would be grateful for any suggestions as to how I can work around it.

[BigChimp]

Haven't looked closely, but I see you're using the autocommit parameter (which you needed for creating a db).

Haven't ever used that; perhaps that influences things. I'd suggest reopening the connection without that param.

As for a bug in the components: could be; if the previous approach doesn't help, please supply a simple test program (e.g. against the master or AdventureWorks database), e.g. via project/publish project/zip up the result.
That can be helpful in seeing what's going on as well as adding to a bug report and using as a test case/base for a test case in the FPC database test framework.

Oh BTW, if you want to have a simple text-based test case for testing db errors, try the program here:
http://wiki.lazarus.freepascal.org/Database_bug_reporting#FreePascal

Thanks.

[TyneBridges]

Thanks, BigChimp. I've tried amending the relevant bit of code to the following, but still get the error. Will try creating a simpler test program as you suggest.

         Conn.Close;
         Q.Close;
         // Switch off autocommit to allow reading rows one by one
         Conn.Params.Add('AutoCommit=False');
         Conn.Open; //This is equivalent to setting Conn.Connected:= True;
         Instruc:= 'Select Name from Sys.Tables order by Name';
         Q.SQL.Text:= Instruc;
         Tr.StartTransaction;
         Q.Open;
         Counter:= 0;
         Q.First; // Still gives error "This operation cannot be performed on an inactive dataset"       

[TyneBridges]

Problem solved! Once again, I was looking in the wrong place for the solution. When I narrowed things down, I realised that my project contained an unnecessary TDataSource component. For some reason, this didn't interfere with the bits of the program that created my database initially; however, it was the cause of the "inactive dataset" error and the apparent issue where 6 records were returned but nothing was readable. Once I had removed that control and all references to it, my project worked.

In case they are useful to anyone else, the following are my observations on projects that create and read MS SQL Server databases (and presumably, can also be used to edit them). To me, this was not very clear in any documentation:

The following should be the only components needed: MSSQLConnection (if using MS SQL Server), SQLQuery and SQLTransaction

    FConn: TMSSQLConnection;
    FQuery: TSQLQuery;
    FTran: TSQLTransaction;           
   
The transaction property of the connection is set to the transaction object we've created; the database property of the query is set to the connection object.
   
   FConn.Transaction:=FTran;
   FQuery.DataBase:=FConn;   
   
   FQuery.DataSource can be left blank
   
For FConn here we need to set Hostname, DatabaseName, UserName and Password

If I've got this wrong, please feel free to post any corrections.

[BigChimp]

It's all there in sqldb tutorial 1:
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1
(and perhaps 2).
Please feel free to make corrections/create updates.

[taazz]

Actually you got it right, You never told us that you linke the query to the datasource though.
The idea is to link the datasource to the query and the data aware controls to the datasource. If you link the query to the datasource then you instruct the query to read data from the datasource and present them as rows.