[Newbe] Help needed.

[Da_eMCe]

Hi there.

I just started to use ZeosLib so I'll have to ask for your patience
Here's the thing: I'm using ZEOSLIB_TESTING_REV368 with LAZARUS 0.9.24 beta and I have to write a console application with these two. The program should read an sql query from a text file and execute it on first MSSQL server, then it has to write an INSERT statement (filled with earlier query results) and throw it to another MSSQL DB. My problem is that since I don't know the exact structure of insert statement, I have to count columns in query result, and IZResultSet doesn't have that functionality. I decided to write a few lines of code to do that and there they go:

Code: Select all

col := 1;
        cond := True;
        while cond do begin 
              try
                 RS.GetBoolean(col);
              except
                 on E : Exception do
                    cond := False;
              end;
              col := col+1;
        end;

But even that simple piece of code gets crashed and I'm unable to read the right columns count. This crashes after 2 iterations and returns "Access Violation" exception.
I thought it maybe something with the GetBoolean function so I changed it to IsNull(ColIndex), but then I recevied "Row buffer is not assigned" exception.

Please help me with this one.

P.S. Maybe I should mention my sql query used for testing: "SELECT * FROM my_table;". This table has 5 columns.

P.S.2 Sorry for my english (most likely I have done some language mistakes).

[mdaems]

Why don't you use the ZQuery component? It does have this functionality by default.
Just set your query, open, loop through the resultset fields and rows.
When using 2 Zconnections and 2 ZQueries the job should be done easily.

- I suppose both systems have the same table structure for every run?
- Is this job to be done regularily with a lot of data? In that case you should maybe look for a more performant solution.
- In case you want to add rows to a non empty target table : select * from targettable where 1 = 0. That initializes the right column structures without retrieving data.

Good luck.

Mark

[Da_eMCe]

Dear Mark, at first I want to thank you for your interest in my problem, but I don't quite get what you're saying... I mean I can't work out how I m supposed to get a resultset of the ZQuery - should I use some kind of GetResultSet() function and store this in another variable or is it just one of Zquery's attributes? Cause I can't find this information in class reference.

About your other suggestions:
- I think so.
- That I don't know. But if that's the case I'm sure I'll be doingt something in that direction later.
- And thx again for a tip

Peace.

[mdaems]

You don't need to get a resultset once you open your query. The ZQuery component is a TZDataset by himself.

What you do is
- Attach a ZQuery to a ZConnection component
- Set sql property with the query you want to execute.
- Call ZQuery.open -> this loads the data in the ZQuery buffers.
- Use TDatasource + TDBgrid (or other components)
OR
Make a loop like this to acces the data by code: (untested code!!)

Code: Select all

ZQuery1.open;
While not ZQuery1.Eof do
  begin
    ShowMessage(ZQuery.FieldByName('<ColumnName1>').asString);
    ZQuery1.Next;
  end;
ZQuery.close;

This kind of loop you can find everywhere in this forum.

Unfortunately we have no Lazarus samples in the distribution. But looking at the text in the examples\design example files (dfm and pas file) you should get a good idea of how to build an easy program to show database content.

Please try to make a simple exe to consult your source db using a db grid before trying to do what you want. That's absolutely the minimum skill you need before you start coding. (why not provide me with a basic lazarus project for the examples section? If this exercise works well, you made one for me )
You 'll need : 1 TZConnection, 1 TZQuery, 1 TDataSource, 1 TDbGrid, 1 TDBNavigator. This should allow you to view/edit your database tables.

After you can do this, start by adding a button to your prog and show some field using showmessage.

Add another button to add a new row using code.
Hint : ZQuery.Append;ZQuery.fields[0].asString := 'string';ZQuery.post;ZQuery.ApplyUpdates;

Final coding exercise : Add a third button to update a field in some record after lookup.
Hint : ZQuery.Locate(...); ZQuery.edit; ZQuery.FieldByName('<name>').AsString :='xx';ZQuery.Post;ZQuery.applyUpdates.

Please, don't ask questions related to your problem before you at least finished the first 3 steps. (the locate/edit stuff isn't really needed for your problem, but it's also a basic db programming skill)

Mark

PS. I did send you back to school in this message. Don't hesitate to ask the teacher however, if you have a question concerning the homework I gave you...

[Da_eMCe]

I did write some db applictaions before But using delphi and dbExpress components... My problem here is that I don't know the colmuns count or names. So I can't really use ZQuery.FieldByName()...
And I promise I'll make U a Lazarus sample program (for your help) but first I got to finish this one.

Nick

[mdaems]

What about ZQuery.Fields.Count? And ZQuery.Fields[0].Name?

These things are just inherited from Lazarus TDataset implementation.

Mark

[Da_eMCe]

Update:

I managed to give the problem some time and I ended up with this code:

Code: Select all

while not Query.Eof do begin
            sql_q := 'INSERT INTO '+ins_tab+' VALUES(';
            for i:=0 to Query.Fields.Count-1 do begin
                sql_q := sql_q+Query.FieldByName(Query.Fields[i].FieldName).asString;
                if i <> Query.Fields.Count-1 then begin
                   sql_q := sql_q+',';
                end
                else
                   sql_q := sql_q+')';
            end;
            Query.Next;
        end; 

Which is exactly what I needed. Now I have to add executing lines for these insert's and it's done And then I will try to do the homework you gave me, Mark

Best regards,
Nick.

[EDIT] I didn't see what you wrote... But anyway I used your advice

[mdaems]

I would try to do it without this string conversions you do.
Just open the target dataset Query2 and append rows.

Code: Select all

Query.Open;
Query2.Open;
while not Query.Eof do begin
            Query2.append;
            for i:=0 to Query.Fields.Count-1 do begin
               Query2.Fields[i].Value := Query.Fields[i].Value;
            end;       
            Query2.Post;
            Query.Next;
        end; 
Query2.ApplyUpdates;
Query.close;
Query2.close;

- If you're not sure about the columns appearing in the same order : use
Query2.FieldByName(Query.Fields.Name).Value := Query.Fields.Value;
- Maybe Query2.FieldByName(Query.Fields.Name).Assign(Query.Fields); is even better
- Don't forget to use the third hint in my first reply when working this way.

Mark

[Da_eMCe]

Wow this is a nice concept Thanks for the idea I will try it for sure.

Nick.

[Da_eMCe]

Hi

I just wanted to say that I did not forget about my homework, and I will do it eventually. But first I've got to change/add some new things in my program (it turns out my "boss" needs a little more complicated app than he thought), so if you still care I can make you an example program in Lazarus, but you'll have to wait a couple of days

Nick.

[mdaems]

Just take the time you need.

Mark