- Posts: 16
Changing SQLite to OleDB Adapter
- Keith Weatherhead
- Topic Author
- Offline
- User
-
- FlowHeater-Team
-
- Offline
- Admin
-
6 years 10 months ago - 6 years 10 months ago #3470
by FlowHeater-Team
Best wishes
Robert Stark
Replied by FlowHeater-Team on topic Changing SQLite to OleDB Adapter
Hi Keith,
I´m afraid at present only the SQLite Adapter has the possibility to create not exiting tables before importing. The OleDB or all other database Adapters are currently not able to create tables or add some missing fields.
Note: When changing the type of Adapter only general information such as field names / types and database connection settings etc. as applicable are retained, any settings that specifically apply to an Adapter type are lost.
However, you can use a little workaround
It would be possible to create the desired dBASE table (uisng the OleDB Adapter ) with the help of the .NET Script Heater and a little C# script like below.
The Script checks whether the dBASE data file exist for the table (e.g. C:\Temp\dBase_test.dbf) and if not build a dynamically “SQL CREATE TABLE” statement with the fields information from the present definition. (see my remark in the script below)
You have just align two configuration settings. First in the script you have to define the path were your dBase DBF database files are located. Second, you have to enter in den WRITE Adapter properties the desired dBASE table name. (see screenshot below)
I´ve modified your definition with the script, I guess it should work for you. (see attachment)
C# script to create dynamically dBASE tables befor importing
I´m afraid at present only the SQLite Adapter has the possibility to create not exiting tables before importing. The OleDB or all other database Adapters are currently not able to create tables or add some missing fields.
Note: When changing the type of Adapter only general information such as field names / types and database connection settings etc. as applicable are retained, any settings that specifically apply to an Adapter type are lost.
However, you can use a little workaround
It would be possible to create the desired dBASE table (uisng the OleDB Adapter ) with the help of the .NET Script Heater and a little C# script like below.
The Script checks whether the dBASE data file exist for the table (e.g. C:\Temp\dBase_test.dbf) and if not build a dynamically “SQL CREATE TABLE” statement with the fields information from the present definition. (see my remark in the script below)
You have just align two configuration settings. First in the script you have to define the path were your dBase DBF database files are located. Second, you have to enter in den WRITE Adapter properties the desired dBASE table name. (see screenshot below)
I´ve modified your definition with the script, I guess it should work for you. (see attachment)
C# script to create dynamically dBASE tables befor importing
Code:
// modify here the path were your dBASE files located
string dbfiles = @"D:\CLC_Test";
public object DoWork()
{
// accessing the WRITE Adapter
OleDBAdapter adapter = (OleDBAdapter)AdapterWrite;
// optain the table name
string tablename = adapter.SQL;
if (File.Exists(Path.Combine(dbfiles, tablename + ".dbf")))
{
// if the dBASE table file already exist no further action!
return null;
}
// build the create table statement
string sql = "create table [" + tablename + "] (";
int len = 0;
int count = 0;
foreach(Field f in adapter.Fields)
{
if (count > 0)
sql += ", ";
DatabaseAdapterField field = (DatabaseAdapterField)f;
sql += "[" + field.Name + "] ";
switch(field.DataType)
{
case DataType.String:
len = field.Length;
if (len == 0)
len = 100;
//SQL Datentyp for strings
sql += "varchar(" + len.ToString() + ")";
break;
case DataType.Date:
case DataType.Time:
case DataType.DateTime:
//SQL Datentyp for datetime
sql += "datetime";
break;
case DataType.Integer:
case DataType.Boolean:
//SQL Datentyp for Int (integer)
sql += "Integer";
break;
case DataType.Double:
case DataType.Decimal:
case DataType.Currency:
//SQL Datentyp für Double and Currency perhaps use numeric(18,2) ...
sql += "Float";
break;
}
if (field.PrimaryKey)
sql += "not null ";
count++;
}
sql += " );";
// execute the SQL statement and create the table
adapter.Execute(sql);
// result not needed!
return null;
}
Best wishes
Robert Stark
Attachments:
Last edit: 6 years 10 months ago by FlowHeater-Team.
Please Log in or Create an account to join the conversation.
Time to create page: 0.279 seconds
