ODBC Error
lazarus / 16-Databases

----------------------------------------------------------------------
Caravelle · 2014-10-24 23:07

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:
QuoteCould 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?

Code: [Select]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

----------------------------------------------------------------------
Never · 2014-10-25 00:13

most likely you have somewhere in your db a column defined as type x
(usualy int)
but your trying to access it as type y (usualy string)
so double check where you are adding the quots
and before executing the query print it to see what exactly you  are
executing
Edit**: also a spelling error in a Field name might produce this error

----------------------------------------------------------------------
Caravelle · 2014-10-25 21:24

Thank you for taking the time and trouble to respond. 

Quotemost likely you have somewhere in your db a column defined as
type x (usualy int)
but your trying to access it as type y (usually string)
Yes, in all the tables one of the columns is a LongInt, but that
doesn't stop the first procedure from running to completion and
producing a csv.

Quotedouble check where you are adding the quots
I can't see that I have added any quotes anywhere.

Quotebefore executing the query print it to see what exactly you  are
executingI tried that earlier.  I had a ShowMessage box popping up
telling me what the query is. The first one through the loop is
"SELECT keyno,reg,c/n,actype,section,current,status,hexcode,miscode
FROM air_index", exactly as expected.

Quotealso a spelling error in a Field name might produce this
errorYou'd never know from the error message !  However, all the field
names have been checked again and again.  And again.

I had a bright idea - bright for me, anyway.  In the _index tables,
one of the fields is called "c/n". Obviously Access doesn't object to
the forward slash because the tables open properly in Access. But
maybe something else doesn't like it ?  I removed this field from the
procedure, changed the looping to cope with the reduced number of
fields. I did NOT get the error !  After correcting a minor error in
the loop the procedure successfully produced a 79 megabyte csv file,
albeit without one of the columns I wanted.

But my question now is how do I form the SQL query to get the field
"c/n" ?  I can't rename the field in each of the three _index tables
because then the third-party program that uses the database wouldn't
work with them.  

Caravelle

----------------------------------------------------------------------
Never · 2014-10-25 21:59

hello glad you solved your problem
 AFAIK  to avoid trouble shooting all the time
have your fields named properly from the begining after all is one
time job
this might help [ http://support.microsoft.com/kb/826763 ]
Edit**:
QuoteYes, in all the tables one of the columns is a LongInt, but that
doesn't stop the first procedure from running to completion and
producing a csv. imho if i was in your place i had these fixed also
because while your app goes bigger it will be really hard to locate
potential errors it will be always an open wound for you
Edit**2:
Quote I can't rename the field in each of the three _index tables
because then the third-party program that uses the database wouldn't
work with them.    excuse me just noticed this one
checkout this [ http://www.w3schools.com/sql/sql_alias.asp ] hope it
will help
Eidt**3 :and  [field name] but i think you are aware of this is the
same with ticks in MySql SELECT `field name`

----------------------------------------------------------------------
Caravelle · 2014-10-25 23:01

Thanks.  I read the article at http://support.microsoft.com/kb/826763
, which suggests the construction QuoteSELECT column_name AS
alias_name
FROM table_name;.

But surely if my query is:  SELECT c/n AS con ..., I am going to hit
the same brick wall with the expression "c/n" ? 

I have been Googling and several sites give advice on escaping
"special characters" in SQL queries but I cannot find anything
specifically referring to the forward slash.  c\/n doesn't make any
difference, I still get the same error.

It would be possible to copy the entire database with a new name,
change the relevant column name in the three tables and then point my
program at the revised database, but this would have to be done twice
a week when the data in the table is updated.   The whole idea of my
Lazarus program is to automate what is a very tedious procedure to do
manually using Access.

Caravelle

----------------------------------------------------------------------
Never · 2014-10-26 00:01

try both and make up your opinion
SELECT c/n AS con
select [c/n]
if everything fails you can stil make use of the build in access
export functionality
1) can use com automation with free pascal [
http://wiki.lazarus.freepascal.org/Office_Automation ]
2) write a vba procedure
3)record and play a macro
i think i have covered everything
Edit***:4)you can use access db query editor and create a view since
access handles the / with no problems you will be able to create a
view with AS or [] with a proper name for the field
then use your ready made code and use the view as your source table

----------------------------------------------------------------------
Nebula · 2014-10-26 09:21

Square brackets should fix it -
http://stackoverflow.com/questions/153861/brackets-in-sql-statements

----------------------------------------------------------------------
Caravelle · 2014-10-26 22:09

Thanks Never and Nebula.

Putting the fieldname "c/n" between square brackets in my SQL query
resulted in a message that there was no such field as "[c/n]". 
Putting the entire list of fieldnames between square brackets, as
Code: [Select]SELECT
[keyno,reg,c/n,actype,section,current,status,hexcode,miscode] FROM
airl_index resulted in the same ODBC error as previously.

Frankly, I really do not want to be tied to using Access, with which I
am not very familiar.  I have no time to learn com automation, vba or
macros.  I would much rather find a way to form the SQL query so that
it works, and achieve my one-click solution to dealing with these
files.  I thought the brackets suggestion might do this, but alas not.

So, time for another bright idea.  When things get too complex,
simplify.  How about avoiding  field names altogether ?
Code: [Select]SELECT * FROM airl_indexAnd it worked immediately !  
This makes the query dataset somewhat bigger, but not too much, and I
can just pick out the fields I want to keep when creating the CSV
file.   Job done as far as the current task is concerned.

I'd still like to know how to form a SQL query to successfully select
specific fields including "c/n" as I might need this for future work,
and it may help someone else faced with the same problem.

Caravelle