Hello JMP-Community,
I am searching your help here because I didn't found a sufficent solution for myself in other posts. It is about writing content of a data table into a table of a database.
For that I wrote this little function:
myInsertInto = function({dt, dbConnection, destinationName},
cols = dt << GetColumnNames(String);
rows = dt << Select All Rows << GetSelectedRows;
show(cols, rows);
//prepare sql head
sqlHead = "Insert Into " || destinationName || " (";
for each({col, index}, cols,
if(index == Length(cols),
sqlHead = sqlHead || col ||")",
sqlHead = sqlHead || col ||",";
)
);
//prepare sql body
sqlBody = "Values\!n";
ForEach({row, index}, rows,
sqlBody = sqlBody || "(";
show(row);
values = dt[row,0];
show(values);
for each({val, index}, values,
if(type(val) == "String",
if(IsMissing(val),
tmpVal = "null",
tmpVal = "'"||val||"'"
)
,
if(IsMissing(val),
tmpVal = "null",
tmpVal = char(val)
)
);
if(index == Length(values),
sqlBody = sqlBody || tmpVal ||")",
sqlBody = sqlBody || tmpVal ||",";
)
);
if(index == Length(rows),
sqlBody = sqlBody || ";",
sqlBody = sqlBody || ",\!n"
);
);
sqlQurey = sqlHead || "\!n" || sqlBody;
New SQL Query(
Version( 130 ),
Connection( myDbHandle ),
QueryName( "messwert.test_struktur_dennis" ),
Custom SQL(
Substitute(sqlQurey,"\!n", "", "\!t", "")
)
)<< run;
return(sqlQurey)
);
The function does what it should, but there are limitations that I don't know how to solve. For example, we are using a postgresql database in which tables have timestamp, date or other formatted-columns. I don't have an idea how to include that into my function, because JMP is seeing all these formatted types as a number.
Another issue is to handle data duplicates. With my function the content of a data table would be blindly inserted into our database. That can lead to duplicates, because I don't know what is already there. I found in another post this function:
dt << Save Database( dbc,
"dbo.fake",
Replace
);
Unfortunately, that would replace my whole table, which i can not use.
Therefore I'd like to ask the community for help. Does anyone know about an add-in or a script solution, that solves these issues?
