Recent

Author Topic: ODBC Error  (Read 5900 times)

Caravelle

  • Jr. Member
  • **
  • Posts: 52
ODBC Error
« on: October 25, 2014, 01:07:03 am »
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:
Quote
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?

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
« Last Edit: October 25, 2014, 01:12:23 am by Caravelle »

Never

  • Sr. Member
  • ****
  • Posts: 409
  • OS:Win7 64bit / Lazarus 1.4
Re: ODBC Error
« Reply #1 on: October 25, 2014, 02:13:41 am »
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

« Last Edit: October 25, 2014, 02:30:32 am by Never »
Νέπε Λάζαρε λάγγεψων οξωκά ο φίλοσ'ς αραεύσε

Caravelle

  • Jr. Member
  • **
  • Posts: 52
Re: ODBC Error
« Reply #2 on: October 25, 2014, 11:24:05 pm »
Thank you for taking the time and trouble to respond. 

Quote
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 (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.

Quote
double check where you are adding the quots

I can't see that I have added any quotes anywhere.

Quote
before executing the query print it to see what exactly you  are executing
I 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.

Quote
also a spelling error in a Field name might produce this error
You'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
« Last Edit: October 25, 2014, 11:28:24 pm by Caravelle »

Never

  • Sr. Member
  • ****
  • Posts: 409
  • OS:Win7 64bit / Lazarus 1.4
Re: ODBC Error
« Reply #3 on: October 25, 2014, 11:59:07 pm »
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**:
Quote
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.
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`
« Last Edit: October 26, 2014, 12:26:05 am by Never »
Νέπε Λάζαρε λάγγεψων οξωκά ο φίλοσ'ς αραεύσε

Caravelle

  • Jr. Member
  • **
  • Posts: 52
Re: ODBC Error
« Reply #4 on: October 26, 2014, 01:01:18 am »
Thanks.  I read the article at http://support.microsoft.com/kb/826763 , which suggests the construction
Quote
SELECT 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

  • Sr. Member
  • ****
  • Posts: 409
  • OS:Win7 64bit / Lazarus 1.4
Re: ODBC Error
« Reply #5 on: October 26, 2014, 02:01:51 am »
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
« Last Edit: October 26, 2014, 02:14:01 am by Never »
Νέπε Λάζαρε λάγγεψων οξωκά ο φίλοσ'ς αραεύσε

Nebula

  • Jr. Member
  • **
  • Posts: 88
Re: ODBC Error
« Reply #6 on: October 26, 2014, 10:21:42 am »
Newbie testing Lazarus v1.0 - very impressed
Win 7 at work, XP and Linux Mint at home.
It all started with a ZX80 on a b/w telly........
Code: [Select]
Uses Smiles, GoodHumour, WantsToHelp;
{ never liked C - curly brackets are for comments! }

Caravelle

  • Jr. Member
  • **
  • Posts: 52
Re: ODBC Error
« Reply #7 on: October 26, 2014, 11:09:00 pm »
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 !   ;D
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

 

TinyPortal © 2005-2018