Changing SQLite to OleDB Adapter

Keith Weatherhead
Topic Author
Offline
New Member

More

4 years 5 months ago #3466 by Keith Weatherhead

Changing SQLite to OleDB Adapter was created by Keith Weatherhead

Greetings,

The included the FHD file which was originally a TEXTFILE to SQLite3 transformation...
I tried changing the WRITE Adapter to OleDB in order to create a DBF file.

I get the error in the screenshot along with there configuration screenshots.
I have tried to enter a filename on the: Fields / Data Types tab ... I have left that blank...
no matter what I try it get the error shown....

It is almost like the Change of Adapter did not work or complete?

Keith

Attachments:

Please Log in or Create an account to join the conversation.

FlowHeater-Team
Offline
Administrator

More

4 years 5 months ago - 4 years 5 months ago #3470 by FlowHeater-Team

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

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;
}

File Attachment:

File Name: ar_atb_jun...-dbf.zip
File Size:6 KB

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Attachments: