Moving Data from Paradox to Oracle

Hello,

    Because I decided to migrate my Application from BDE, Paradox to Oracle, I made a small tool to move Data from Paradox tables to Oracle,  to make it easy moving my customers Data to the new database.

   I use ODAC Components from Devart to access Oracle database from delphi.
 
  When my tool start with the first paradox table, the first record, it stops on a field called 'ETime" and it is a string field.

  The Error Says: ORA-00928: missing SELECT keyword

find below my code
var
 I, J : Integer;
begin
  BDE_Database.GetTableNames(ListBox1.Items);
  Gauge2.MaxValue := ListBox1.Count;
  I := 0;
  Gauge2.Progress := 0;
  While I < ListBox1.Count do
  begin
    Label1.Caption := 'Copying Table: ' + ListBox1.Items.Strings[I];
    ParadoxTable.TableName := ListBox1.Items.Strings[I] + '.DB';
    ParadoxTable.Open;
    Gauge1.MaxValue := ParadoxTable.RecordCount;
    OraTable1.TableName := ListBox1.Items.Strings[I];
    OraTable1.Open;
    OraTable1.First;
    Gauge1.Progress := 0;
    ParadoxTable.First;
    while not ParadoxTable.EOF do
    begin
      OraTable1.Append;
      for J := 0 to ParadoxTable.FieldCount - 1 do
      begin
        Label3.Caption := 'Processing Field : ' + ParadoxTable.Fields[J].FieldName + #13 +
                          'Record Number : ' + IntToStr(ParadoxTable.RecNo);
        OraTable1.Fields[J].Value := ParadoxTable.Fields[J].Value;
      end;
      ParadoxTable.Next;
      Gauge1.Progress := ParadoxTable.RecNo;
    end;
    ParadoxTable.Close;
    OraTable1.Close;
    I := I + 1;
    Gauge2.Progress := I;
    Application.ProcessMessages;
  end;
  Label1.Caption := 'Done!';
  ShowMessage(Label1.Caption);
end;

Open in new window

Error.jpg
i7madAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

8080_DiverCommented:
Is ETime the first column in the table?  If not, what is the data type of the ETime column and what is the datatype of the column into which you asre trying to insert it?
Try putting a break point on the statement that is setting the value of the Oracle column and then running the app in debug mode.  When it stops there, check to see if the Oracle table shows Nil, if not, then check to see what the current value of that column is.  That should give you an idea as to what is happening.  
If it looks okay, the step (<f8>) through that setp and keep stepping slowly until you find where the exception is thrown.  You may have to go back through the code and set another break point where the exception is thown so that you can further analyze the various parameters, variables, etc.
0
i7madAuthor Commented:
The whole table defs are in the attached image
AdjustDesign.jpg
0
8080_DiverCommented:
Okay, ETime is not the first column but, rather, the last one.  (That may or may not be important. :-/ )
I noticed that several of your columns in the Paradox table are Varchar(255) . . . are you sure that the data in the ETime, for instance, isn't corrupted and actually does represent a time?
Also, what is the nature of the Oracle table's columns?  I notice that you are accessing them (as well as the Paradox columns) based upon position.  Are you sure that the column orders match?
Finally, have you tried stepping through the application as I suggested?
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

JonasMalmstenCommented:
I'm not familiar with ODAC (I use Direct Oracle Access from www.allroundautomations.com). But if they are somewhat similar then...

The error message doesn't make sense to me unless it was thrown when you open the table (but looking at the screen shot this appears not to be the case). Maybe you just need to put OraTable1.Post before you move to the next record?

Another consideration, how can you be certain that OraTable1.Fields[J].Value correspond to the correct field for ParadoxTable.Fields[J].Value? It may be more safe if you use something like

OraTable1.FieldByName(ParadoxTable.Fields[J].FieldName) := ParadoxTable.Fields[J].Value;

Then again, all fields can't use the same name because I see in your other screenshot a field called "date" which is a reserved word in oracle and cannot be used as a field name. Maybe a single if-statement can solve this case.
0
JonasMalmstenCommented:
correction, missed the .Value:
OraTable1.FieldByName(ParadoxTable.Fields[J].FieldName).Value := ParadoxTable.Fields[J].Value;

or maybe even
OraTable1.FieldByName(ParadoxTable.Fields[J].FieldName).AsString := ParadoxTable.Fields[J].AsString;

or some cases may even require that you check the field type, something like

if ParadoxTable.Fields[J] is TStringField then
  OraTable1.FieldByName(ParadoxTable.Fields[J].FieldName).AsString := ParadoxTable.Fields[J].AsString;
if ParadoxTable.Fields[J] is TDateField then
  OraTable1.FieldByName(ParadoxTable.Fields[J].FieldName).AsDateTime := ParadoxTable.Fields[J].AsDateTime;

0
i7madAuthor Commented:
well guys, I replaced :

OraTable1.Fields[J].Value := ParadoxTable.Fields[J].Value;


WITH:

        S := ParadoxTable.Fields[J].FieldName;
        OraTable1.FieldByName(S).Value := ParadoxTable.FieldByName(S).Value;


THE RESULT: same ERROR!
0
i7madAuthor Commented:
I removed Adjust Table, and run the tool again, the tool stopped on other Table on same field name (ETime)

I think ETime is something like oracle reserved word!!

really?
0
8080_DiverCommented:
Now, this is probably going to sound like a radical approach but have you considered creating an INI file that has a name=value pair to provide the Oracle to Paradox (or vice versa) column name mapping?  That would let you change the name of the "Data" column, for instance, so that it is not a problem in the Oracle table but still access it from the Paradox table.
If you try using the Paradox names for the Oracle table columns, you'll probably have some problems.  Similarly, if you rename a column in Oracle but do not rename it in Paradox, then the column won't exist in the Paradox table and you'll get an error.
I also believe that you should use a Post after you finish setting the columns in the Oracle table.  That could explain why everything is fine until the end of the insertion of the data.  
0
senadCommented:
ETIME is not oracle reserved word.
0
senadCommented:
also it should go :  FieldByName('S').Value
0
i7madAuthor Commented:
FieldByName('S').Value ??

no , 'S' is a constant String , so there is no fields named S in my tables
0
i7madAuthor Commented:
ETime is the last column in all tables, that means the Error message appears only when trying to insert a new record in Oracle table
8080 driver, yes I did the .Post method just before going in paradoxtable.next , same error
after debugging , the For J loop is finished, and the error appears after finishing the J loop and trying to post the Oracle Table.
0
i7madAuthor Commented:
go on guys, it is 250 points now :D
0
JonasMalmstenCommented:
Open SQL*Plus and type

DESC ADJUST

what do you get?

You can also try this. Import one table only (for example Adjust since this is not working). Before importing, double click both source and destination tables in delphi IDE and "add all fields". Drag and drop the fields to make sure that
OraTable.Fields[n] = ParadoxTable.Fields[n] for all n.

I have a program that does a similar task, imports data from paradox to oracle, but is using DOA components to access oracle. It has been working great for years. I don't see any big difference from your code, but just in case I'm blind I have attached a copy :). In my program I have added all fields for each source/destination table pair as described above.
procedure TForm1.Import(tbl: TTable; tbl2: TOracleDataset; pgb: TProgressBar);
  procedure CopyContent;
  var
    i: Integer;
  begin
    for i := 0 to tbl.FieldCount - 1 do
    begin
      if tbl.Fields[i] is TDateField then
      begin
        tbl2.Fields[i].AsDateTime := tbl.Fields[i].AsDateTime;
      end
      else
      if tbl.Fields[i] is TStringField then
      begin
        tbl2.Fields[i].AsString := Trim(tbl.Fields[i].AsString);
      end
      else
      if tbl.Fields[i] is TSmallIntField then
      begin
        tbl2.Fields[i].AsInteger := tbl.Fields[i].AsInteger;
      end
      else
      if tbl.Fields[i] is TFloatField then
      begin
        tbl2.Fields[i].AsFloat := tbl.Fields[i].AsFloat;
      end
      else
      if tbl.Fields[i] is TIntegerField then
      begin
        tbl2.Fields[i].AsInteger := tbl.Fields[i].AsInteger;
      end
      else
      if tbl.Fields[i] is TMemoField then
      begin
        tbl2.Fields[i].AsString := tbl.Fields[i].AsString;
      end
      else
        raise Exception.Create('Unrecognised fieldtype: ' + tbl.Fields[i].ClassName);
    end;
  end;
begin
  tbl.Open;
  tbl2.Open;        
  pgb.Max := tbl.RecordCount;
  pgb.Position := 0;
  tbl.First;
  while not tbl.Eof and not FImportStopped do
  begin
    try
      AddLog('New Record: ' + tbl.Fields[0].AsString);
      tbl2.Insert;
      CopyContent;
      tbl2.Post;
    except
      on e: Exception do
        AddLog('Import Error: ' + e.Message + ' One record skipped: ' + tbl.Fields[0].AsString, clRed);
    end;
    tbl.Next;
    pgb.Position := pgb.Position + 1;
    Application.ProcessMessages;
  end;
  tbl.Close;
  tbl2.Close;
end;

Open in new window

0
i7madAuthor Commented:
This is DESC ADJUST result.
DESC-ADJUST.jpg
0
JonasMalmstenCommented:
Try to rename the field "DATE" to something else, maybe this is what is causing the problem. Like date_dt or something. You then need an if-statement before using FieldByName, something like:

  S := ParadoxTable.Fields[J].FieldName;
  if s = 'DATE' then s := 'DATE_DT';
  OraTable1.FieldByName(S).Value := ParadoxTable.FieldByName(S).Value;

When selecting a field name DATE, unless you have quotes around it oracle will think it is a reserved word and throw an exception.
0
JonasMalmstenCommented:
This is my reasoning:

SQL> create table test (DATE date);
create table test (DATE date)
                   *
ERROR at line 1:
ORA-00904: : invalid identifier
--------------------------------------
SQL> create table test ("DATE" date);

Table created.
--------------------------------------
SQL> select DATE from test;
select DATE from test
       *
ERROR at line 1:
ORA-00936: missing expression
--------------------------------------
SQL> select "DATE" from test;

no rows selected        
--------------------------------------
SQL> insert into test ("DATE") values (null);

1 row created.
--------------------------------------
SQL> insert into test (DATE) values (null);
insert into test (DATE) values (null)
                  *
ERROR at line 1:
ORA-00928: missing SELECT keyword

That's the error you get!!!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.