?
Solved

from db.struct  to SQL

Posted on 2005-03-01
8
Medium Priority
?
196 Views
Last Modified: 2010-04-05
Hi,
based on the paradox db.struct table I want to create tables with an SQl Statement. Who can complete the
select case PDXFieldType of.... statement in the function below ??

{****************************************************
 *  Reading the Struc.db information of a Paradox Table
 *  and Create a New Table
 *  Version:
 ******************************************************}
function TDBWizardForm.MakeSqlStatment (value :  Integer; TableName  :  String) : TStringList;
var  SQLStatement   :   TStringList;
     FieldName0     :   String;
     FieldName1     :   String;
     FieldName2     :   String;
     FieldName3     :   String;
     FValue0        :   String;
     FValue1        :   String;
     FValue2        :   String;
     FValue3        :   String;
     PDXFieldType   :   Char;
     PDXField       :   String;
     AddRemark      :   Boolean;
begin
    //  Starting values ...
    SQLStatement :=TStringList.Create;
    AddRemark := false;

    //  set Table Cursor
    DBStructTable.First;

    //  Analyse the Structure Table
    FieldName0 := DBStructTable.Fields[0].FieldName;   //  FieldName
    FieldName1 := DBStructTable.Fields[1].FieldName;   //  FieldType
    FieldName2 := DBStructTable.Fields[2].FieldName;   //  Field Value
    FieldName3 := DBStructTable.Fields[3].FieldName;   //  Field Value


    //  remarks
     if AddRemark then
     begin
       SQLStatement.Add('/*');
       SQLStatement.Add(' Table Type  ');
       SQLStatement.Add(' SQL Generated : '+ DateToStr(Date));
       SQlStatement.Add('*/');
     end;

    //  Write SQL Header
    SQLStatement.Add('Create Table  '  + TableName +  '  (');

    repeat

       // Read the Table
       FValue0:= DBStructTable.FieldByName(FieldName0).AsString;
       FValue1:= DBStructTable.FieldByName(FieldName1).AsString;
       FValue2:= DBStructTable.FieldByName(FieldName2).AsString;
       FValue3:= DBStructTable.FieldByName(FieldName3).AsString;


       PDXFieldType:=FValue1[1];
       case PDXFieldType of
       'A': PDXField := '  CHAR(' + FValue2 + ')' ;       //  Alpha-String
       'N': PDXField := '  NUMERIC';      //  Numeric
       '$': PDXField := '?';      //  Money
       'D': PDXField := '  DATE';      //  Date
       'S': PDXField := '  SMALLINT';      //  Short
       'M': PDXField := '  MEMO( '+ FValue2 + ',' + FValue3+  ' )';      //  Memo    1-240
       'B': PDXField := '?';      //  Binary Data
       'F': PDXField := '?';      //  Formatted Text   0..240
       'O': PDXField := '?';      //  OLE Object       0..240
       'G': PDXField := '?';      //  Graphics Object  0..240
       'I': PDXField := '  INTEGER';      //  Long  Integer
       'T': PDXField := '  TIME';      //  Time
       '@': PDXField := '  TIMESTAMP';      //  Time Stamp
       'L': PDXField := '  BOOLEAN';      //  Logical
       '+': PDXField := '?';      //  AutoIncrement (Long)
       'Y': PDXField := '?';      //  Fixed Number of Bytes  1..255
       '#': PDXField := '?';      //  BCD  (32 Digits)
       else
           begin
              //  This Code should never be executed ...
           end;
       end;


        //  Move Table and Make SQL Statement
        DBStructTable.Next;
        if Not DBStructTable.EOF then SQLStatement.Add(FValue0 + PDXField + ',')
                                  else  SQLStatement.Add(FValue0 + PDXField + ')')


    until DBStructTable.Eof ;

    //   save the data for debugging and controlling purpose ...
    SqlStatement.SaveToFile(FDebugPath +'Log'+ TableName + '_' + IntToStr(Value));
   

    Result:= SQLStatement;

    // SQLStatement.Free;

end;

0
Comment
Question by:BdLm
[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
  • 2
  • 2
8 Comments
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13428830
Here are a couple of them

       'B': PDXField := 'Binary';      //  Binary Data
       '$': PDXField := 'Money';      //  Money

       '+': PDXField := '?';      //  AutoIncrement (Long)
The auto increment field just set to be an integer and set the Identity to be Yes
0
 
LVL 8

Author Comment

by:BdLm
ID: 13429380
Can help me with the memo, my code does not work here.
Is there a link with all informations ?
0
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13431452
I just opened up SQL server to grab the correct references for you Im afraid.

Well if your memo is only 240 characters long, you could create it as a VarChar(240)
0
 
LVL 8

Author Comment

by:BdLm
ID: 14534297
still there is no 100 % code solution avail., the current code goes like this:


{****************************************************************************
 *  Reading the Struc.db information of a Paradox Table
 *  and Create a New Table
 *  Version:    EE Question Q_21332777.html
*******************************************************************************}
function TDBWizardForm.MakeSqlStatment (value :  Integer; TableName  :  String) : TStringList;
var  SQLStatement   :   TStringList;
     FieldName0     :   String;
     FieldName1     :   String;
     FieldName2     :   String;
     FieldName3     :   String;
     FValue0        :   String;
     FValue1        :   String;
     FValue2        :   String;
     FValue3        :   String;
     PDXFieldType   :   Char;
     PDXField       :   String;
     AddRemark      :   Boolean;
begin
    //  Starting values ...
    SQLStatement :=TStringList.Create;
    AddRemark := false;

    //  set Table Cursor
    DBStructTable.First;

    //  Analyse the Structure Table
    FieldName0 := DBStructTable.Fields[0].FieldName;   //  FieldName
    FieldName1 := DBStructTable.Fields[1].FieldName;   //  FieldType
    FieldName2 := DBStructTable.Fields[2].FieldName;   //  Field Value
    FieldName3 := DBStructTable.Fields[3].FieldName;   //  Field Value


    //  remarks
     if AddRemark then
     begin
       SQLStatement.Add('/*');
       SQLStatement.Add(' Table Type  ');
       SQLStatement.Add(' SQL Generated : '+ DateToStr(Date));
       SQlStatement.Add('*/');
     end;

    //  Write SQL Header
    SQLStatement.Add('Create Table  '  + TableName +  '  (');

    repeat

       // Read the Table
       FValue0:= DBStructTable.FieldByName(FieldName0).AsString;
       FValue1:= DBStructTable.FieldByName(FieldName1).AsString;
       FValue2:= DBStructTable.FieldByName(FieldName2).AsString;   //  Lenght
       FValue3:= DBStructTable.FieldByName(FieldName3).AsString;   //  Typ


       PDXFieldType:=FValue1[1];
       case PDXFieldType of
       'A': PDXField := '  CHAR(' + FValue2 + ')' ;       //  Alpha-String
       'N': PDXField := '  NUMERIC ';                     //  Numeric  Option lenght, decimals
       '$': PDXField := '  Money ';                       //  Money
       'D': PDXField := '  DATE ';                        //  Date
       'S': PDXField := '  SMALLINT ';                    //  Short
       'B': PDXField := '  BINARY';                       //  Binary Data
       'M': PDXField := '  BLOB( 1, 1   )';               //  Memo                     0-240 ,1
       'F': PDXField := '  BLOB( 1, 3   )';               //  Formatted Text           0..240,3
       'O': PDXField := '  BLOB( 1, 4   )';               //  OLE Object               0..240,4
       'G': PDXField := '  BLOB( 1, 5   )';               //  Graphics Object, Binary  0..240,5
       'I': PDXField := '  INTEGER';                      //  Long  Integer
       'T': PDXField := '  TIME';                         //  Time
       '@': PDXField := '  TIMESTAMP';                    //  Time Stamp
       'L': PDXField := '  BOOLEAN';                      //  Logical
       '+': PDXField := '  AUTOINC';                      //  AutoIncrement (Long)
       'Y': PDXField := '  ?';                            //  Fixed Number of Bytes  1..255
       '#': PDXField := '  BYTES(' + FValue2 + ')';       //  BCD  (32 Digits)
       else
           begin
              //  This Code should never be executed ...
           end;
       end;


        //  Move Table and Make SQL Statement
        DBStructTable.Next;
        if Not DBStructTable.EOF then SQLStatement.Add(FValue0 + PDXField + ',')
                                  else  SQLStatement.Add(FValue0 + PDXField + ')')


    until DBStructTable.Eof ;

    //   save the data for debugging and controlling purpose ...
    if DebugModeActivate then
      SqlStatement.SaveToFile(FDebugPath +'Log'+ TableName + '_' + IntToStr(Value));
    ErrorStrList.Add('save sql as : '+ FDebugPath +'Log'+ TableName + '_' + IntToStr(Value));

    Result:= SQLStatement;

    // SQLStatement.Free;

end;
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 14560770
PAQed with points refunded (100)

modulo
Community Support Moderator
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
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 Month15 days, 6 hours left to enroll

743 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