Re: Query base64 data from SQL into the jmp table

Report Inappropriate Content · Apr 3, 2024 03:20 PM

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

txnelson · Apr 3, 2024 05:45 PM

I suspect you will want to use

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

Jim

Craige_Hales · Apr 3, 2024 10:37 PM

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_ · Apr 4, 2024 6:44 AM

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:

JMP reads limits til 255 char

"89504E470D0A1A0A0000000D4948445200000DAC000007D0080600000094E2F9EB0000200049444154785EECDD07905545DA3FE0171024A818565D3388A262465D54CC3960045D54740D8062C29C3127CC8B8A39E79CF39A139815B3A288620EA0E420FFEABB9FFB37CCBD3373A7679881A7ABBEFAB68AD36F773FA7E7CE19E"

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