BdLm
asked on
from db.struct to SQL
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.MakeSqlStatm ent (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].Fi eldName; // FieldName
FieldName1 := DBStructTable.Fields[1].Fi eldName; // FieldType
FieldName2 := DBStructTable.Fields[2].Fi eldName; // Field Value
FieldName3 := DBStructTable.Fields[3].Fi eldName; // 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(FD ebugPath +'Log'+ TableName + '_' + IntToStr(Value));
Result:= SQLStatement;
// SQLStatement.Free;
end;
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.MakeSqlStatm
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].Fi
FieldName1 := DBStructTable.Fields[1].Fi
FieldName2 := DBStructTable.Fields[2].Fi
FieldName3 := DBStructTable.Fields[3].Fi
// 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(
FValue1:= DBStructTable.FieldByName(
FValue2:= DBStructTable.FieldByName(
FValue3:= DBStructTable.FieldByName(
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(FD
Result:= SQLStatement;
// SQLStatement.Free;
end;
ASKER
Can help me with the memo, my code does not work here.
Is there a link with all informations ?
Is there a link with all informations ?
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)
Well if your memo is only 240 characters long, you could create it as a VarChar(240)
ASKER
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.MakeSqlStatm ent (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].Fi eldName; // FieldName
FieldName1 := DBStructTable.Fields[1].Fi eldName; // FieldType
FieldName2 := DBStructTable.Fields[2].Fi eldName; // Field Value
FieldName3 := DBStructTable.Fields[3].Fi eldName; // 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(FD ebugPath +'Log'+ TableName + '_' + IntToStr(Value));
ErrorStrList.Add('save sql as : '+ FDebugPath +'Log'+ TableName + '_' + IntToStr(Value));
Result:= SQLStatement;
// SQLStatement.Free;
end;
{*************************
* Reading the Struc.db information of a Paradox Table
* and Create a New Table
* Version: EE Question Q_21332777.html
**************************
function TDBWizardForm.MakeSqlStatm
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].Fi
FieldName1 := DBStructTable.Fields[1].Fi
FieldName2 := DBStructTable.Fields[2].Fi
FieldName3 := DBStructTable.Fields[3].Fi
// 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(
FValue1:= DBStructTable.FieldByName(
FValue2:= DBStructTable.FieldByName(
FValue3:= DBStructTable.FieldByName(
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(FD
ErrorStrList.Add('save sql as : '+ FDebugPath +'Log'+ TableName + '_' + IntToStr(Value));
Result:= SQLStatement;
// SQLStatement.Free;
end;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
'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