47331_en-US

liliconcom

Specify for SQL datatypes when saving from JMP

I'm saving to a MySQL database from JMP tables. Can I control the MySQL column datatype that gets created when I save?

 

A;lso, if I design my schema within MySQL and attempt to add rows to it from JMP, does JMP have awareness of/respect for the datatype of the existing MySQL columns?

Re: Specify for SQL datatypes when saving from JMPThank you Stephen!!!!
Re: Specify for SQL datatypes when saving from JMP

This is for MS SQL but it should be the same idea for MySQL. I have a stored procedure which is expecting the data from the 29 columns in the table. As you can see dates need a bit of work. I am building a string using JSL that I would type in any SQL management program. The end of the code swaps JMP missing values for NULL.

 

I have code which fetches the data from the table (only those rows where the ignore column = 0). The post Query Builder code then appends this JSL to the data table as a script called "Update selected values". As far as the user is concerned they are editing the data table directly.

 

The stored procedure checks if value1 is NULL, in which case it adds a new row and generates an ID. If an ID is specified in value1 then it sets the old entry Ignore = 1 then added a new entry with the specified ID and Ignore = 0.

 

dbc = Create Database Connection(
 "ODBC:..."
);
value1 = Column( Current Data Table(), 1 ) << Get Values;
value2 = Column( Current Data Table(), 2 ) << Get Values;
value3 = Column( Current Data Table(), 3 ) << Get Values;
value4 = Column( Current Data Table(), 4 ) << Get Values;
value5 = Column( Current Data Table(), 5 ) << Get Values;
value6 = Column( Current Data Table(), 6 ) << Get Values;
value7 = Column( Current Data Table(), 7 ) << Get Values;
value8 = Column( Current Data Table(), 8 ) << Get Values;
value9 = Column( Current Data Table(), 9 ) << Get Values;
value10 = Column( Current Data Table(), 10 ) << Get Values;
value11 = Column( Current Data Table(), 11 ) << Get Values;
value12 = Column( Current Data Table(), 12 ) << Get Values;
value13 = Column( Current Data Table(), 13 ) << Get Values;
value14 = Column( Current Data Table(), 14 ) << Get Values;
value15 = Column( Current Data Table(), 15 ) << Get Values;
value16 = Column( Current Data Table(), 16 ) << Get Values;
value17 = Column( Current Data Table(), 17 ) << Get Values;
value18 = Column( Current Data Table(), 18 ) << Get Values;
value19 = Column( Current Data Table(), 19 ) << Get Values;
value20 = Column( Current Data Table(), 20 ) << Get Values;
value21 = Column( Current Data Table(), 21 ) << Get Values;
value22 = Column( Current Data Table(), 22 ) << Get Values;
value23 = Column( Current Data Table(), 23 ) << Get Values;
value24 = Column( Current Data Table(), 24 ) << Get Values;
value25 = Column( Current Data Table(), 25 ) << Get Values;
value26 = Column( Current Data Table(), 26 ) << Get Values;
value27 = Column( Current Data Table(), 27 ) << Get Values;
value28 = Column( Current Data Table(), 28 ) << Get Values;
value29 = Column( Current Data Table(), 29 ) << Get Values;
rowsToUpdate = Current Data Table() << get selected rows;
NrowsToUpdate = N Rows( rowsToUpdate );
If( NrowsToUpdate > 0,
 For( i = 1, i <= NrowsToUpdate, i++,
  insertString =
  "DECLARE @RC int

EXECUTE @RC = [DBNAME].[dbo].[SPNAME] 
           
           ";
  valueString = "";
  j = rowsToUpdate[i];
  valueString = valueString || Char( value1[j] ) || ",'" || Char( value2[j] )
   || "','" || Char( value3[j] ) || "','" || Char( value4[j] ) || "','" ||
  Char( value5[j] ) || "','" || Char( value6[j] ) || "','" ||
  Char( value7[j] ) || "','" || Char( value8[j] ) || "','" ||
  Char( value9[j] ) || "','" || Char( value10[j] ) || "','" ||
  Char( value11[j] ) || "','" || Char( value12[j] ) || "','" ||
  Char( value13[j] ) || "','" || Char( value14[j] ) || "','" ||
  Char( value15[j] ) || "','" || Char( value16[j] ) || "'," ||
  Char( value17[j] ) || "," || Char( value18[j] ) || "," || Char( value19[j] )
   || "," || Char( value20[j] ) || "," || Char( value21[j] ) || "," ||
  Char( value22[j] ) || ",'" || Char( value23[j] ) || "','" ||
  Char( value24[j] ) || "','" || Char( value25[j] ) || "','" ||
  Char( value26[j] ) || "','" || Char(
   Substitute( Format( value27[j], "y/m/d h:m:s" ), "/", "" )
  ) || "','" || Char(
   Substitute( Format( value28[j], "y/m/d h:m:s" ), "/", "" )
  ) || "','" || Char( value29[j] ) || "'";
  insertString = insertString || valueString;
  insertString = Substitute( insertString,
   ".,", "NULL,",
   ",''", ",NULL",
   ",,", ",NULL,",
   "'.'", "NULL"
  );
  dt = Execute SQL( dbc, insertString );
 );
 Close Database Connection( dbc );
);

Re: Specify for SQL datatypes when saving from JMP

Hi Stephen,

Thanks! Would you be able to share a few lines of your code as an example for me? If too much trouble, I understand.

John

Re: Specify for SQL datatypes when saving from JMPI ended up using a stored procedure and passing the updated records row by row to the database. In this way I can precisely control the data types and which columns were written to the database. I added a column which would flag the existing record to be hidden rather than deleting the entry.