Solved

Moving Data from Paradox to Oracle

Posted on 2009-07-01
17
819 Views
Last Modified: 2013-11-23
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
0
Comment
Question by:i7mad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +1
17 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24757449
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
 

Author Comment

by:i7mad
ID: 24757492
The whole table defs are in the attached image
AdjustDesign.jpg
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24757614
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:JonasMalmsten
ID: 24758031
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
 
LVL 4

Expert Comment

by:JonasMalmsten
ID: 24758063
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
 

Author Comment

by:i7mad
ID: 24758083
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
 

Author Comment

by:i7mad
ID: 24758144
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24758287
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
 
LVL 22

Expert Comment

by:senad
ID: 24760107
ETIME is not oracle reserved word.
0
 
LVL 22

Expert Comment

by:senad
ID: 24760144
also it should go :  FieldByName('S').Value
0
 

Author Comment

by:i7mad
ID: 24761014
FieldByName('S').Value ??

no , 'S' is a constant String , so there is no fields named S in my tables
0
 

Author Comment

by:i7mad
ID: 24761076
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
 

Author Comment

by:i7mad
ID: 24761092
go on guys, it is 250 points now :D
0
 
LVL 4

Expert Comment

by:JonasMalmsten
ID: 24762137
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
 

Author Comment

by:i7mad
ID: 24762234
This is DESC ADJUST result.
DESC-ADJUST.jpg
0
 
LVL 4

Expert Comment

by:JonasMalmsten
ID: 24762264
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
 
LVL 4

Accepted Solution

by:
JonasMalmsten earned 250 total points
ID: 24762336
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month5 days, 7 hours left to enroll

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question