FlowHeater Logo

FlowHeater Support Forum

simply more productive

Download the freeware version (10 Mb)

Up to 100,000 records can be processed with the freeware version!

Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1

TOPIC: Table Name if doesn't exist

Table Name if doesn't exist 1 week 4 days ago #3437

  • Alan Sawyer
  • Alan Sawyer's Avatar Topic Author
  • Offline
  • Posts: 1
I am trying to use Oracle for the read, and Microsoft SQL Server for the write.

I clone the fields from the read side, and I can drop down a list of tables, but in this case, the table doesn't exist.

I have no options to enter the "table name if doesn't eixst". It's not on the screen. I just purchased the product today and downloaded and installed the trial this morning before applying my license key

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

Table Name if doesn't exist 1 week 4 days ago #3438

Hi Alan,

I guess you would like to create the table dynamically? This feature currently only supported in the SQLite Adapter . The SQLServer Adapter and all other Database Adpater does not support this feature by default.

If you want to create the table dynamically you have to use the power of the .NET Script Heater and the little script below.
These script checks first whether the table exists. In case not the table are created based on the defined fied definition of the current WRITE adapter.

Attached you´ll find a little example.

Note: You can set the table name in the Designer below in the Adapter proberties.




.NET C# Script to create SQL tables dynamically
bool bFirst = true;

public object DoWork()
{
	object o = null;

	if (bFirst)
 	{
  		// only for the first row
  		bFirst = false;
		
		// get the WRITE Adapter
    		DatabaseAdapter adapter = (DatabaseAdapter)AdapterWrite;
  		string sql = String.Format("select count(*) from {0}", adapter.SQL);

		// check whether the table exists
		try
		{
			o = adapter.Execute(sql, true);			
			// Table already exists, no more to do.
			return null;
		}
		catch(Exception)
		{
			// nothing :-)
		}

		// create sql create statement
		sql = String.Format("create table {0} (", adapter.SQL);

  		int len = 0;
  		int count = 0; 
  		foreach(Field f in adapter.Fields)
  		{
   			DatabaseAdapterField field = (DatabaseAdapterField)f;

   			if (count > 0)
    				sql += ", ";

   			sql += "[" + field.Name + "] ";
   			switch(field.DataType)
   			{
    				case DataType.String:
     				len = field.Length;
     				if (len == 0)
      				len = 100; // default length

     				//SQL data type for strings
     				sql += "varchar(" + len.ToString() + ")";
     				break;


    				case DataType.Date:
			    	case DataType.Time:
    				case DataType.DateTime:
     					//SQL data type for datetime
     					sql += "datetime";
     					break;

    				case DataType.Boolean:
     					//SQL data type for Bool: for MYSQL use boolean!
     					sql += "bit";
     					break;


    				case DataType.Integer:
     					//SQL data type for Int
     					sql += "Integer";
     					break;


    				case DataType.Double:
				case DataType.Decimal:
    				case DataType.Currency:

     					//SQL data type for Double and Currency, also posible numeric(18,2) ...
     					sql += "Float";
     					break;
   			}
   
   			if (field.AutoIncrement)
				sql += " identity(1,1)";

			if (field.PrimaryKey)
      			sql += " not null";
   
   			count++;
  		}

  		sql += " );";
    
  		// for debug write the SQL statement to file
		// File.WriteAllText("C:\\Temp\\sql.txt", sql);

  		// create table
  		adapter.Execute(sql);
 	}

	return null;
}

File Attachment:

File Name: mssql-crea...able.zip
File Size:3 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:

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

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.
  • Page:
  • 1
Time to create page: 0.231 seconds