FlowHeater - The Data Import / Export Specialist
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

Table Name if doesn't exist

  • Alan Sawyer
  • Topic Author
  • Offline
  • New Member
  • New Member
More
4 years 6 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

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

More
4 years 6 months ago #3438 by FlowHeater-Team
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
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; }

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.

Time to create page: 0.337 seconds

other Languages

de

FlowHeater Home

de en

Imprint/Contact

Privacy Statement

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

Follow us on

twitter  facebook

YouTube

 de en