- Posts: 5
Table Name if doesn't exist
- Alan Sawyer
- Topic Author
- Offline
- User
-
Less More
6 years 10 months ago #3437 by Alan Sawyer
Table Name if doesn't exist was created by Alan Sawyer
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
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.
- FlowHeater-Team
-
- Offline
- Admin
-
Less More
- Posts: 444
6 years 10 months ago #3438 by FlowHeater-Team
Best wishes
Robert Stark
Replied by FlowHeater-Team on topic Table Name if doesn't exist
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
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
Code:
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; }
Best wishes
Robert Stark
Attachments:
Please Log in or Create an account to join the conversation.
Time to create page: 0.271 seconds