topic Re: Writing to an SQL database in Discussions

Writing to an SQL database

stephen_pearson — Fri, 12 Feb 2016 15:34:08 GMT

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:

the text columns are saved as in JMP nvarchar with trailing white spaces - this requires a white space trim function to use the uploaded text columns
dates download correctly but the OBDC driver throws an error if you try to write them as dates. You can get round this by first converting the column to text format and then converting back on use but is rather annoying.

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?

Re: Writing to an SQL database

msharp — Fri, 12 Feb 2016 17:10:22 GMT

Sorry I can't help, I'm completely novice to this subject, but I would also appreciate any feedback anyone has on writing to databases.

Re: Writing to an SQL database

vince_faller — Wed, 19 Oct 2016 01:52:41 GMT

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]

VALUES

('TestSQLinsert'

,'JMP'

,14.14

,'JMP'

,14

);

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).

Re: Writing to an SQL database

stephen_pearson — Sat, 13 Feb 2016 16:02:49 GMT

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?

Re: Writing to an SQL database

ian_jmp — Mon, 15 Feb 2016 09:52:31 GMT

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.

Re: Writing to an SQL database

vince_faller — Mon, 15 Feb 2016 15:33:54 GMT

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,

"dbo.fake",

Replace

);

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.

Re: Writing to an SQL database

wu — Tue, 22 Oct 2019 23:53:25 GMT

Hi,

Thanks for your sharing.

Below is my case, and appreciate for help.

1. the sql data base name in server: SQL_machine_yield_db

attached SQL_machine_yield_db.jmp when get update from db connection.

2. in the db, there are 8 columns

3. the data in the table "new_and_update_yield.jmp

Question: how to use jsl/sql to update new_and_yield.jmp to SQL_machine_yield_db in the server?

Best Regards

Re: Writing to an SQL database

ron_horne — Wed, 23 Aug 2023 10:39:10 GMT

thank you @vince_faller

i have used this and also manage to scale it up by running it in a loop. for some reason i had a limit of about 35K rows at a time so i just made a for loop to read 25K rows at a time and enter them into the database.