Lazarus and MS Access problem
lazarus / 16-Databases

----------------------------------------------------------------------
Ruffus · 2018-01-24 17:53

I have some code that used to work on Lazarus 1.6, but does not any
more.  The code extracts information from an MS Access file and puts
it into a text file.  There seems to be a file not open error and I am
having problems debugging.  Any help would be appreciated.

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, odbcconn, sqldb, db, FileUtil, Forms, Controls,
Graphics,
  Dialogs, DBGrids, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Datasource1: TDatasource;
    DBGrid1: TDBGrid;

    Edit1: TEdit;
    Edit2: TEdit;
    Edit3: TEdit;
    Label1: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    ODBCConnection1: TODBCConnection;
    SQLQuery1: TSQLQuery;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure Datasource1DataChange(Sender: TObject; Field: TField);
    procedure Edit1Change(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure ODBCConnection1AfterConnect(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;
  DateT: String;

implementation
//uses Unit2;

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
var
    S: String;
    conn: TODBCConnection;
    query: TSQLQuery;
    transaction: TSQLTransaction;
    fileout : TextFile;
    gcat_1 : String;
    gcat_2 : String;
    tabout : String;

  begin
    conn := TODBCCOnnection.Create(nil);
    query := TSQLQuery.Create(nil);
    transaction := TSQLTransaction.Create(nil);

     // *** Change date for Experation yyyy-mt-dy-hr:sec:min
    DateT :=  Edit1.Text + 'T01:01:01';

    try
      try
        conn.Transaction := transaction;
        conn.Params.Add ('DBQ='+ Edit2.Text);
        conn.Driver := 'Microsoft Access Driver (*.mdb)';
        query.DataBase := conn;
        query.UsePrimaryKeyAsKey:=false;
        query.SQL.Text := 'SELECT * FROM msgoogle';
        query.Open;

        S := '';
        gcat_1 := 'Software';
        gcat_2 := 'Computer Software';
        tabout := 'title' + #09 + 'description'+ #09 + 'link' + #09 +
'id' + #09 + 'image_link' + #09 + 'price' + #09 + 'brand' + #09 +
'expiration_date' + #09 + 'condition' + #09 + 'product_type' + #09 +
'mpn' + #09 + 'upc' + #09 + 'isbn' + #09 + 'identifier_exists' + #09 +
'product_category' + #09 + 'availability';

        assignfile(fileout, Edit3.Text);
        ReWrite(fileout);
        writeln(fileout, tabout);

        while not query.EOF do
        begin
         S := query.FieldByName('title').AsString + #09 +
query.FieldByName('description').AsString;
         S := S + #09 + query.FieldByName('link').AsString + #09 +
query.FieldByName('id').AsString;
         S := S + #09 + query.FieldByName('image_link').AsString + #09
+ query.FieldByName('price').AsString + ' USD';
         S := S + #09 + query.FieldByName('brand').AsString + #09 +
DateT;
         ///*date to check*/
         S := S + #09 + query.FieldByName('condition').AsString + #09
+ query.FieldByName('product_type').AsString;

       If length (trim(query.FieldByName('mpn').AsString)) = 0 Then
       S:= S + #09
        Else
       S:= S + #09 + query.FieldByName('mpn').AsString;

       If length (trim(query.FieldByName('upc').AsString)) = 0 Then
       S:= S + #09
        Else
       S:= S + #09 + query.FieldByName('upc').AsString;

      If length (trim(query.FieldByName('isbn').AsString)) = 0 Then
       S:= S + #09
        Else
       S:= S + #09 + query.FieldByName('isbn').AsString;

       If query.FieldByName('identifier_exists').AsString = 'True'
Then
       S:= S + #09 + 'TRUE'
        Else
       S:= S + #09 + 'FALSE';

      Case trim(query.FieldByName('category').AsString) of
    'Software' :
    S:= S + #09 + gcat_1;
    'Software > Computer' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' ;
    'Antivirus' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Antivirus &
Security Software';
    'Business & Productivity':
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Business &
Productivity Software';
    'Children''s':
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Children''s
Software';
    'Compilers & Programming Tools':
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Compilers &
Programming Tools';
    'Computer Utilities & Maintenance':
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Computer
Utilities & Maintenance';
    'Utilities & Maintenance' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Computer
Utilities & Maintenance';
    'Educational':
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Educational
Software';
    'Education':
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Educational
Software';
    'Handheld & PDA':
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Handheld & PDA
Software';
    'Multimedia & Design Software':
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Multimedia &
Design Software';
    'Multimedia & Design' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Multimedia &
Design Software';
    'Animation Editing' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Multimedia &
Design Software > Animation Editing Software';
    'Graphic Design'  :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Multimedia &
Design Software > Graphic Design Software';
    'Home Publishing' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Multimedia &
Design Software > Home Publishing Software';
    'Printing' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Multimedia &
Design Software > Home Publishing Software';
    'Music Composition'  :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Multimedia &
Design Software > Music Composition Software';
    'Sound Editing' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Multimedia &
Design Software > Sound Editing Software';
    'Video Editing' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Multimedia &
Design Software > Video Editing Software';
    'Web Design'  :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Multimedia &
Design Software > Web Design Software';
    'Network'  :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Network
Software';
    'Office Application' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Office
Application Software';
    'Operating Systems' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Operating
Systems';
    'Reference' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Reference
Software';
    'Dictionary & Translation' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Reference
Software > Dictionary & Translation Software';
    'GPS Map Data' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Reference
Software > GPS Map Data & Software';
    'Tax & Accounting' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Tax & Accounting
Software';
    'Digital Content' :
    S := S + #09 + gcat_1 + ' > ' + gcat_2 + ' > ' + 'Digital Content
Software';
    'Clip Art' :
    S := S + #09 + gcat_1 + ' > ' + 'Digital Content Software > Clip
Art';
    'Computer Icons' :
    S := S + #09 + gcat_1 + ' > ' + 'Digital Content Software >
Computer Icons';
    'Desktop Wallpaper':
    S := S + #09 + gcat_1 + ' > ' + 'Digital Content Software >
Desktop Wallpaper';
    'Fonts' :
    S := S + #09 + gcat_1 + ' > ' + 'Digital Content Software >
Fonts';
    'Video Game' :
    S := S + #09 + gcat_1 + ' > ' + 'Video Game Software';
    'Computer Games' :
    S := S + #09 + gcat_1 + ' > ' + 'Video Game Software > Computer
Games';
    'Games':
    S := S + #09 + gcat_1 + ' > ' + 'Video Game Software > Computer
Games > PC Games';
    Else
    S := S + #09 + 'Software > Computer Software';
   End;

    S:= S + #09 + 'in stock';

    //    Memo1.Append (S);
         writeln(fileout,S);

        query.Next;
        end;
      finally
        query.Free;
        closefile (fileout);
        conn.Free;

      end;
    except
      on E: Exception do
        ShowMessage(E.message);
    end;

end;

procedure TForm1.Button2Click(Sender: TObject);
 begin
    close;
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  showmessage(Edit1.Text + 'T01:01:01');
end;

procedure TForm1.ComboBox1Change(Sender: TObject);
begin

end;

procedure TForm1.Datasource1DataChange(Sender: TObject; Field:
TField);
begin

end;

procedure TForm1.Edit1Change(Sender: TObject);
begin

end;

procedure TForm1.FormCreate(Sender: TObject);
 begin

end;

procedure TForm1.ODBCConnection1AfterConnect(Sender: TObject);
begin

end;

end.

----------------------------------------------------------------------
dsiders · 2018-01-24 18:07

Quote from: Ruffus on January 24, 2018, 06:53:37 pmI have some code
that used to work on Lazarus 1.6, but does not any more.  The code
extracts information from an MS Access file and puts it into a text
file.  There seems to be a file not open error and I am having
problems debugging.  Any help would be appreciated.

Code: [Select]        conn.Transaction := transaction;
        conn.Params.Add ('DBQ='+ Edit2.Text);
        conn.Driver := 'Microsoft Access Driver (*.mdb)';
        query.DataBase := conn;
        query.UsePrimaryKeyAsKey:=false;
        query.SQL.Text := 'SELECT * FROM msgoogle';
        query.Open;

You never opened the ODBC connection before trying to use the query.

Code: [Select]conn.Connected := True;

----------------------------------------------------------------------
Ruffus · 2018-01-24 18:48

Thanks... I still am getting a pascal exception runerror 103.  File
not open.  For some reason when I run the compiled program it goes to
pascals ASM feature and bombs.