Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Moving Data from Paradox to Oracle

Posted on 2009-07-01
17
Medium Priority
?
825 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

824 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