Query base64 data from SQL into the jmp table

[#1]

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

[#2]

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.

[#3]

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

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

[#4]

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.

[#5]

What brand of database might help.

https://community.jmp.com/t5/Discussions/Byte-Array-Image-from-Database-to-JMP/m-p/16612#M15136 seems like it answers a similar question

***make sure your odbc drivers are up-to-date***

I'm not a database expert, but if the data is in the database and the query is truncating it, maybe something here will help.

https://www.sqlservercentral.com/forums/topic/255-char-limitation

https://stackoverflow.com/questions/19984934/why-a-long-text-field-is-returning-only-255-characters-...

https://board.phpbuilder.com/d/10329740-mssql-255-characters-limit-in-select/9

https://learn.microsoft.com/en-us/office/troubleshoot/access/linked-table-data-truncated

[#6]

Thank you so much for the solution.

[#7]

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