LazSqlX

[BigChimp]

Ok, I'd guess the connection string is wrong... but my crystal ball needs coffee, too

I'd be happy to look at the code and see if I can get it going. I'll send you a PM.... thanks.

[flamer0n]

Thanks. I just found out that main library "fbclient.dll" wasn't in the directory, after I put it there it can connect. So, the next version lazsqlx will have Firebird that will work with SqlDb.
 

[flamer0n]

LazSqlx v4.2.2

Changes:
-Query Execution with Firebird runs with SQLDB
-Bugfix: Autocomplete wouldn’t invoke when pressing “.” after table name or table alias.

[BigChimp]

Thanks a lot!

Some notes/remarks/issues:
- when installing into program files with Win 7, I get an error createeprocess failed error 740 (or something, sorry, from memory). Probably a permissions thing in the installer... you could perhaps remove the run exe from installer functionality entirely
- connected to Firebird db ok. Then, while connected, clicked File/Connect again which crashed the application.

Now going to actually use the program - the importer tool looks exactly what I need btw

[BigChimp]

Hi,

Fiddled around some. Please find some remarks below in case they are useful.

Imported a ; separated text file using tools/import. ; was autodetected - nice!
- delimiter: there doesn't seem to be a way to choose the tab (ASCII 9) character or other low ascii characters as delimiter?

- end row was 1 while it was a large file. Tried to set it to 0, didn't work. Perhaps add an "Everything" option or something? (Note: pressing "validate" correctly popuplated end row with the max row number from the file, but the GUI is a bit confusing)

- Mapping: if first row as schema is deselected, perhaps show the field content of the field in question below the Field1 combobox. Or perhaps in the memo with field mappings? (Not much of a GUI guy) Makes it easier to choose/visualise.

- Sample line of the import file:
410680;"8255PE";82558069;8255;"PE";2;78;"even";"De Kolk";"Swifterbant";2431;"Dronten";303;"Flevoland";"FL";"52.5733977717603";"5.6500083564955";"172815.72730555555555555556";"509565.93941666666666666667";"postcode";"2014-04-10 13:20:28"
Unfortunately, the mapping only shows Field1 to 4 while there are many more fields in the line. Suggestion (haven't looked at the code): you could use http://wiki.lazarus.freepascal.org/CsvDocument for reliable import of CSV-type file

Thanks a lot for all your work - I have installed Zeos and am going to look at the source code next...

[flamer0n]

 Thank you for your feedback BigChimp. I haven't done much testing, I'll look into DataImporter tool now, considering your suggestions.

[BigChimp]

Mmmm, was just busy doing the same. But it's your code, and you're probably much quicker.

In any case, please find attached my current version... was busy implementing csvdocument...

(csvdocument.pas to be placed in 3rdparty\csvdocument\)

[flamer0n]

Thanks! I need to get familiar with this component to see how it works and how to use it and I'll post any news here

[flamer0n]

Ok, had some other work, then got back to LazSqlx and I replaced the CSVDataset with CsvDocument as you suggested and did some testing and it worked good. Now, let's talk about GUI... I just want to know if this is ok:
- when the file being imported contains no column names (no 1st row definition), should I display the first data row cells as column names so the user can figure out what goes with what and do the mapping himself?
- I'm also thinking to get rid of "Validate" button, so that the event happens when user accepts the file to be imported

[BigChimp]

- when the file being imported contains no column names (no 1st row definition), should I display the first data row cells as column names so the user can figure out what goes with what and do the mapping himself?

Yes, that sounds great.

- I'm also thinking to get rid of "Validate" button, so that the event happens when user accepts the file to be imported

Mmmm... only problem: at the moment Validate populates the end line spinbutton with the maximum line number.
Perhaps allow users to enter 0 for last line number and modify import code accordingly.

Apart from that: good idea; it makes things more simple.
(However, I'm wondering how you read the file etc and provide the field mappings as that does depend on delimiter etc)

[flamer0n]

Mmmm... only problem: at the moment Validate populates the end line spinbutton with the maximum line number.
Perhaps allow users to enter 0 for last line number and modify import code accordingly.

Yes, I have to change it, spinedits will be limited to maxrows but also allow user to enter 0 on both.

(However, I'm wondering how you read the file etc and provide the field mappings as that does depend on delimiter etc)

After populating cmbSourceColumns and cmbDestColumns and user has finished mappings

Something like this:

Code: [Select]


{...foreach row}
  for J := 0 to lstColumnsMapping.Count - 1 do
    begin
      SrcColumn:= TAsStringUtils.SplitString(lstColumnsMapping.Items[J], '=')[0];
      SrcValue:=FCSVDocument.Cells[cmbSourceColumns.Items.IndexOf(SrcColumn),I];
      DestColumnName:= TAsStringUtils.SplitString(lstColumnsMapping.Items[J], '=')[1];
      if FDestQuery.FieldByName(DestColumnName).DataType <> ftAutoInc then
      begin
        if not FDestQuery.FieldByName(DestColumnName).ReadOnly then
        FDestQuery.FieldByName(DestColumnName).Value := SrcValue;
      end;
    end;     
{...}         

-I'm trying to do some Autodetection of Delimiter reading first row of the file but it seems hard to make it smart, I'll check only for most common delimiters [; , #9 |]

[BigChimp]

Totally agreed.

[flamer0n]

uploaded v4.2.3.1343

Changes
-DataImport tool improvements thanks to BigChimp for suggestions and CVSDocument
-GetFields (in MainForm) improved
-Zip Archive (apart from setup.exe), just in case you have problems with setup

Source

I know there are a lot of bugs that need fixing, I'm sorry for inconvenience.

[flamer0n]

This is an old problem in LazSqlX, I hope someone can help.
When new tab is created all components needed to run queries and display results are children of the new tab created. I want to free TabSheet when user clicks "Close Tab" to free memory, but it throws an exception (the moment I call tab.free) and I cannot trace it, so currently (as a workaround) I only make TabVisible:=False and I free children components , but not the TabSheet. Is there any solution to this? Thank you in advance.

[minesadorada]

I came across this a long time ago in another app and did the same fudge as you did (hid the created tab)
After a bit of twiddling, I found the reason for my Exception after calling .Free - I had failed to adjust the array via SetLength.

Here's my test unit code which may or may not help in your situation:

Code: [Select]

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, ComCtrls,
  StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { private declarations }
    ExtraTabs:Array of TTabSheet;
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
Var i:Integer;
begin
  i:=High(ExtraTabs);
  ShowMessageFmt('High before create = %d',[i]);
  Inc(i);
  SetLength(ExtraTabs,i+1);
  ExtraTabs[i]:=TTabSheet.Create(PageControl1);
  ExtraTabs[i].Parent:=PageControl1;
  ExtraTabs[i].Caption:='New Tab';
  ExtraTabs[i].Visible:=TRUE;
  i:=High(ExtraTabs);
  ShowMessageFmt('High after create = %d',[i]);
end;

procedure TForm1.Button2Click(Sender: TObject);
Var i:Integer;
begin
  i:=High(ExtraTabs);
  ShowMessageFmt('High before free = %d',[i]);
  If i >= 0 then
  begin
       ExtraTabs[i].Free;
       SetLength(ExtraTabs,i);
  end;
  i:=High(ExtraTabs);
  ShowMessageFmt('High after free = %d',[i]);
end;

end.