Solved

Moving Data from Paradox to Oracle

Posted on 2009-07-01
17
804 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now