cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar

Writing to an SQL database

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
vince_faller
Super User (Alumni)

Re: Writing to an SQL database

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'

          ,1

          ,1

          ,'JMP'

          ,14.14

          ,14.14

          ,1

          ,'JMP'

          ,'JMP'

          ,'JMP'

          ,14

          ,14

              )"

);

close database connection(dbc);


10955_pastedImage_4.png


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


Vince Faller - Predictum

View solution in original post

vince_faller
Super User (Alumni)

Re: Writing to an SQL database

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. 

Vince Faller - Predictum

View solution in original post

7 REPLIES 7
msharp
Super User (Alumni)

Re: Writing to an SQL database

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.

vince_faller
Super User (Alumni)

Re: Writing to an SQL database

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'

          ,1

          ,1

          ,'JMP'

          ,14.14

          ,14.14

          ,1

          ,'JMP'

          ,'JMP'

          ,'JMP'

          ,14

          ,14

              )"

);

close database connection(dbc);


10955_pastedImage_4.png


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


Vince Faller - Predictum

Re: Writing to an SQL database

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?

ian_jmp
Staff

Re: Writing to an SQL database

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.

vince_faller
Super User (Alumni)

Re: Writing to an SQL database

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. 

Vince Faller - Predictum
wu
wu
Level III

Re: Writing to an SQL database

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

ron_horne
Super User (Alumni)

Re: Writing to an SQL database

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.