Subscribe Bookmark RSS Feed

Writing to an SQL database

stephen_pearson

Community Trekker

Joined:

Oct 6, 2014

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?

1 ACCEPTED SOLUTION

Accepted Solutions
vince_faller

Super User

Joined:

Mar 17, 2015

Solution

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. 

5 REPLIES
msharp

Super User

Joined:

Jul 28, 2015

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

Joined:

Mar 17, 2015

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


stephen_pearson

Community Trekker

Joined:

Oct 6, 2014

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

Joined:

Jun 23, 2011

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

Joined:

Mar 17, 2015

Solution

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.