topic Re: Query base64 data from SQL into the jmp table in Discussions

Query base64 data from SQL into the jmp table

Jackie_ — Wed, 03 Apr 2024 19:27:28 GMT

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

Re: Query base64 data from SQL into the jmp table

txnelson — Wed, 03 Apr 2024 21:45:17 GMT

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.

Re: Query base64 data from SQL into the jmp table

Craige_Hales — Thu, 04 Apr 2024 02:37:26 GMT

Is this a database or CSV import?

If an SQL database, these might be useful.

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

Re: Query base64 data from SQL into the jmp table

Jackie_ — Thu, 04 Apr 2024 13:44:29 GMT

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.

Re: Query base64 data from SQL into the jmp table

Craige_Hales — Thu, 04 Apr 2024 14:15:37 GMT

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-in-a-ms-access-2013-query

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

Re: Query base64 data from SQL into the jmp table

MorganRamirez — Sat, 27 Apr 2024 12:12:50 GMT

Thank you so much for the solution.

Re: Query base64 data from SQL into the jmp table

Craige_Hales — Sat, 27 Apr 2024 14:47:32 GMT

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