cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
Jackie_
Level VI

Query base64 data from SQL into the jmp table

Hi,

 

I'm trying to import base64 encoded data into JMP, but it seems to be truncating the data at 255 characters. The database column containing the base64 string has a length of 546,168 characters. Is there a way to increase the character limit for the jmp table column?

 

Thanks,

Jackie

 

6 REPLIES 6
txnelson
Super User

Re: Query base64 data from SQL into the jmp table

I suspect you will want to use

  

txnelson_0-1712180562871.png

I believe it can handle an input stream of up to 2 Billion characters.  Check in the Scripting Index for examples.

 

Jim
Craige_Hales
Super User

Re: Query base64 data from SQL into the jmp table

Is this a database or CSV import?

 

If an SQL database, these might be useful.

Byte Array Image from Database to JMP 

downloading large columns from database via odbc 

 

If this is CSV data, how are you opening it? Sharing the open() statement or dialog might help. Also the Source script from the bad import. JMP should be able to read very large strings; how did you determine the length of the imported string?

 

Here's a quick test using open(...csv)

x = "1," || Encode64 Blob( Char To Blob( Repeat( "the quick brown fox", 30000 ) ) ) || ",3"; Show( Length( x ) );
y = "4," || Encode64 Blob( Char To Blob( Repeat( "jumps over the lazy", 30000 ) ) ) || ",6"; Show( Length( y ) );
file = Save Text File( "$temp/x.csv", "a,b,c\!n" || x || "\!n" || y );
dt = Open( file );
Show(
	JMP Version(),
	Length( dt:b[1] ),
	Length( dt:b[2] ),
	Blob Peek( Decode64 Blob( dt:b[1] ), 0, 38 ),
	Blob Peek( Decode64 Blob( dt:b[2] ), 0, 38 )
);
/*
Length(x) = 760004;
Length(y) = 760004;
JMP Version() = "17.2.0";
Length(dt::b[1]) = 760000;
Length(dt::b[2]) = 760000;
Blob Peek(Decode64 Blob(dt::b[1]), 0, 38) = Char To Blob( "the quick brown foxthe quick brown fox", "ascii~hex" );
Blob Peek(Decode64 Blob(dt::b[2]), 0, 38) = Char To Blob( "jumps over the lazyjumps over the lazy", "ascii~hex" );
*/

(a lot of) base 64 data in middle column(a lot of) base 64 data in middle column

the Source script looks like this

Open(
	"$TEMP/x.csv",
	columns(
		New Column( "a", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "b", Character, "Nominal" ),
		New Column( "c", Numeric, "Continuous", Format( "Best", 12 ) )
	),
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Comma, CSV( 1 ) ),
		Strip Quotes( 0 ),
		Use Apostrophe as Quotation Mark( 0 ),
		Use Regional Settings( 0 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		CompressNumericColumns( 0 ),
		CompressCharacterColumns( 0 ),
		CompressAllowListCheck( 0 ),
		Labels( 1 ),
		Column Names Start( 1 ),
		First Named Column( 1 ),
		Data Starts( 2 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
)
Craige
Jackie_
Level VI

Re: Query base64 data from SQL into the jmp table

Thanks @Craige_Hales !

I am quering from the Database server into JMP.

 

I tried to convert it into Hexadecimal, yet it only reads up to 255 characters

 

Query:

ODBC = Create Database Connection( "DSN=xxx;" );
SQL= Eval Insert(
	
   "SELECT hex(plot) AS HexadecimalPlot
FROM db.main.plot_table;");
dt_diesort2 = Execute SQL( ODBC, SQL, "Pic_table" );

Here is what I end up with:

Jackie__1-1712238262189.png

 

 

JMP reads limits til 255 char

"89504E470D0A1A0A0000000D4948445200000DAC000007D0080600000094E2F9EB0000200049444154785EECDD07905545DA3FE0171024A818565D3388A262465D54CC3960045D54740D8062C29C3127CC8B8A39E79CF39A139815B3A288620EA0E420FFEABB9FFB37CCBD3373A7679881A7ABBEFAB68AD36F773FA7E7CE19E"

I have attached the original hex values that I want to query in the jmp table.

Craige_Hales
Super User

Re: Query base64 data from SQL into the jmp table

Re: Query base64 data from SQL into the jmp table

Thank you so much for the solution.

Craige_Hales
Super User

Re: Query base64 data from SQL into the jmp table

Welcome, and which part worked for you? Thanks for any details you can share. Everybody struggles with database connections.

Craige