FlowHeater - The Data Import / Export Specialist
Welcome, Guest
Username: Password: Remember me
See also the general examples available, which may answer your question: Hello World 1 / 2 / 3 , format numbers as character strings , simple If-Then-Else , complex If-Then-ElseIf , filtering and grouping data , consolidate grouped values
  • Page:
  • 1


Table Name if doesn't exist 4 years 2 months ago #3437

  • Alan Sawyer
  • Alan Sawyer's Avatar Topic Author
  • Offline
  • Posts: 5
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 4 years 2 months 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
			o = adapter.Execute(sql, true);			
			// Table already exists, no more to do.
			return null;
			// 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 + "] ";
    				case DataType.String:
     				len = field.Length;
     				if (len == 0)
      				len = 100; // default length

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

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

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

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

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

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

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

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

  		// create table

	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.

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

  • Page:
  • 1
Time to create page: 0.140 seconds

other Languages


FlowHeater Home

de en


Privacy Statement

Copyright © 2009-2022 by FlowHeater GmbH.
All rights reserved.

Follow us on

twitter  facebook


 de en