The SQL connection in JMP is great for reading data - is anyone using it to write data?
I have a stored procedure which gathers data and writes it to a new table. It uses a second table to read the list of parameters so it knows what data to collect. I can read this table with JMP but writing to it is far from easy:
Currently I use a stored procedure to copy the correct data from the JMP created table to the master table which overcomes the limitations indirectly.
My question is there a better way to use JSL/SQL together to read a table, add new rows and then write the alterations/amendments back to the server?
This worked Fine for me. I am probably misunderstanding your question.
dbc = Create Database Connection("DRIVER=SQL SERVER;SERVER=fake_database;Database=Test_DB");
sqlreturn = Execute SQL(dbc,
"INSERT INTO [Test_DB].[dbo].[fake]
close database connection(dbc);
I assume your dates problem is because JMP actually stores dates as seconds. You could either do a MDYHMS() on your date in JMP or do a date add(JMPdate, s, '1-1-1970') in SQL.
Any more information you can give? I can pull in data and add rows to the JMP table then update the SQL table but without context I would just skip the data pull and insert directly in SQL (Or have SQL do the addition and leave JMP out entirely).
Thanks for that example. I knew I could write raw SQL and execute it eg INSERT, UPDATE and REPLACE or run a stored procedure and pass parameters to it. I was looking for example SQL/JSL where a user can open an existing database table in JMP. To the end user it appears to be just a JMP table stored on a remote computer. The difference being any changes they made to that JMP table could be uploaded to the original database.
I can now see how I could write JSL code to check if new rows had been added to the table and then use INSERT INTO to write them to the database (provided the data type mismatches can be controlled). Any ideas how to keep track of edits to existing rows?
I have the sense that you want to have the database 'auto update' when edits are made to the JMP table (with no 'Update' button, as such). You might be able to do this using 'Subscribe()'. See 'Help > Scripting Index' for examples.
I assume you're querying what the data table is with some WHERE statement and not just pulling in the whole table?
If you do happen to be pulling in the whole table,
dt << Save Database( dbc,
Would work. Otherwise DON'T do this. As it will delete all information in your table that isn't in your JMP query. I think this is in JMP 12+?
If not, you could split it off of if it has an ID or not. Do an update if it has an ID, do an insert if it doesn't. It would probably be best to lock and hide the ID column though. I'm just spitballing here but I think it should work.