I should be grateful if someone more experienced than me could take a look at the code below. I would attach the entire project but it doesn't work without the database which has over a million records, and ODBC data sources set up on the PC.
The Access database layout, which can't be altered as it's designed to work with a third-party program, is as follows:
pb_execairl.mdb - requires password
tables: Jets, Props, Russian, Bizjets, Bizprops; exec_index, airl_index.
pb_ga.mdb - no password
tables: genav; genav_index.
pb_mil.mdb - no password
tables: military; mil_index.
The purpose of the code below is to make two big csv files. Procedure btnMainCSVClick puts wanted fields from the non-index tables into PlaneBaseMain.csv, while Procedure btnIndexCSVClick puts fields from the _index tables into PlaneBaseIndex.csv.
btnMainCSVClick works perfectly. btnIndexCSVClick produces this error:
Could not execute statement. ODBC error details:
LastReturnCode: SQL_ERROR; Record1: SqlState: 07002;
NativeError: -3010; Message: [Microsoft][ODBC
Microsoft Access Driver] Too few parameters. Expected
2.;.
But the second procedure is just a clone of the first, it accesses the same mdbs using the same components and my "ODBC.DataBaseName := 'LazConnect' + IntToStr(MDB); " construct - LazConnect1 to LazConnect3 are set up via my Vista ODBC Data Source Administrator. So if the first procedure works, why does the second produce a "too few parameters" error ? What is it exactly which is supposed to have 2 parameters but hasn't in the second procedure?
unit PBMainunit1;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, odbcconn, sqldb, FileUtil, Forms, Controls, Graphics,
Dialogs, StdCtrls, strutils;
type
{ TForm1 }
TForm1 = class(TForm)
btnMainCSV: TButton;
btnIndexCSV: TButton;
memResult: TMemo;
ODBC: TODBCConnection;
Query: TSQLQuery;
Transaction: TSQLTransaction;
procedure btnIndexCSVClick(Sender: TObject);
procedure btnMainCSVClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ private declarations }
public
{ public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.lfm}
{ TForm1 }
procedure TForm1.FormCreate(Sender: TObject);
begin
// Ini procedures due to go here
end;
procedure TForm1.btnMainCSVClick(Sender: TObject);
var
MainFields: string; //Names of fields to import
Q: string; //fixed part of Query string
CSVString: string; //string holding single csv record
FieldList: TStringList; //List of fields to be imported
CSVList: TStringList; //holds csv data lines
TBLArray: Array[1..3, 1..5] of string; //holds table names for each mdb
MDB, TBL, FLD, TblCount: integer; //loop counters
begin
CSVString := '';
MainFields := 'Section,KeyNo,CurrentID,Constructor,AcType,Series,Con,Status,ICAO,Operator,Hexcode';
FieldList := TStringList.Create;
FieldList.CommaText := MainFields;
CSVList := TStringList.Create;
Q := 'SELECT ' + MainFields + ' FROM ';
TBLArray[1,1] := 'Jets';
TBLArray[1,2] := 'Props';
TBLArray[1,3] := 'Russian';
TBLArray[1,4] := 'Bizjets';
TBLArray[1,5] := 'Bizprops';
TBLArray[2,1] := 'Genav';
TBLArray[3,1] := 'Military';
try
for MDB := 1 to 3 do //cycle through the 3 databases
begin
ODBC.DataBaseName := 'LazConnect' + IntToStr(MDB);
{LazConnect1 to LazConnect3 are ODBC sources set up in Windows}
if MDB = 1 then ODBC.Password := 'egwu';
ODBC.UserName := 'Admin';
ODBC.Transaction := Transaction;
Transaction.Database := ODBC;
Query.Database := ODBC;
Query.Transaction := Transaction;
Query.UsePrimaryKeyAsKey := FALSE;
Query.Close;
ODBC.Open;
if MDB = 1 then TblCount :=3 else TblCount :=1;
For TBL := 1 to TblCount do begin
Query.SQL.Text := Q + TblArray[MDB, TBL];
Query.Open;
While not Query.Eof do begin
CSVString := '';
If MDB = 1 then TblCount :=3 else TblCount :=1;
CSVString := CSVString + Trim(Query.FieldByName(FieldList[0]).AsString);
For FLD := 1 to 9 do begin
CSVString := CSVString + Trim(Query.FieldByName(FieldList[FLD]).AsString) + '|';
end;
CSVString := CSVString + Trim(Query.FieldByName(FieldList[10]).AsString);
// processing below is necessary to prevent malformed data in subsequent import to sqlite
CSVString := DelChars(CSVString, #10);
CSVString := DelChars(CSVString, #13);
CSVString := DelChars(CSVString, '"');
CSVList.Append(CSVString);
Query.Next
end;
Query.Close;
end;
ODBC.Close;
end;
finally
ODBC.Close
end;
CSVList.SaveToFile('D:Log/PlaneBaseMain.csv');
memResult.Lines.Append(IntToStr(CSVList.Count) + ' main records');
CSVList.Destroy;
FieldList.Destroy;
end;
procedure TForm1.btnIndexCSVClick(Sender: TObject);
var
IndexFields: string; //Names of fields to import
Q: string; //fixed part of Query string
CSVString: string; //string holding single csv record
FieldList: TStringList; //List of fields to be imported
CSVList: TStringList; //holds csv data
TBLArray: Array[1..3, 1..2] of string; //holds table names for each mdb
MDB, TBL, FLD, TblCount: integer; //loop counters
begin
CSVString := '';
IndexFields := 'keyno,reg,c/n,actype,section,current,status,hexcode,miscode';
FieldList := TStringList.Create;
FieldList.CommaText := IndexFields;
CSVList := TStringList.Create;
Q := 'SELECT ' + IndexFields + ' FROM ';
TBLArray[1,1] := 'airl_index';
TBLArray[1,2] := 'exec_index';
TBLArray[2,1] := 'genav_index';
TBLArray[3,1] := 'mil_index';
try
for MDB := 1 to 3 do //cycle through the 3 databases
begin
ODBC.DataBaseName := 'LazConnect' + IntToStr(MDB);
{LazConnect1 to LazConnect3 are ODBC sources set up in Windows}
if MDB = 1 then ODBC.Password := 'egwu';
ODBC.UserName := 'Admin';
ODBC.Transaction := Transaction;
Transaction.Database := ODBC;
Query.Database := ODBC;
Query.Transaction := Transaction;
Query.UsePrimaryKeyAsKey := FALSE;
Query.Close;
ODBC.Open;
if MDB = 1 then TblCount :=3 else TblCount :=1;
For TBL := 1 to TblCount do begin
Query.SQL.Text := Q + TblArray[MDB, TBL];
Query.Open;
While not Query.Eof do begin
CSVString := '';
If MDB = 1 then TblCount :=2 else TblCount :=1;
CSVString := CSVString + Trim(Query.FieldByName(FieldList[0]).AsString);
For FLD := 1 to 9 do begin
CSVString := CSVString + Trim(Query.FieldByName(FieldList[FLD]).AsString) + '|';
end;
CSVString := CSVString + Trim(Query.FieldByName(FieldList[10]).AsString);
// processing below is necessary to prevent malformed data in subsequent import to sqlite
CSVString := DelChars(CSVString, #10);
CSVString := DelChars(CSVString, #13);
CSVString := DelChars(CSVString, '"');
CSVList.Append(CSVString);
Query.Next
end;
Query.Close;
end;
ODBC.Close;
end;
finally
ODBC.Close
end;
CSVList.SaveToFile('D:Log/PlaneBaseIndex.csv');
memResult.Lines.Append(IntToStr(CSVList.Count) + ' index records');
CSVList.Destroy;
FieldList.Destroy;
end;
end.
Many thanks in advance
Caravelle
Vista 32 bit, Lazarus 1.2.2.ver 44758 FPC 2.6.4