topic Re: downloading large columns database from database via odbc in Discussions

downloading large columns from database via odbc

vince_faller — Fri, 09 Jun 2023 21:59:04 GMT

I'm have a sql column that is varbinary(MAX). It seems that the column width for JMP is 65536, so I can't download the whole column. It doesn't seem to be an ODBC only issue.

Currently I'm doing these one at a time by just grabbing substrings until they're all empty and concatting into an array.

This takes FOREVER.

Does anyone have any suggestions for ways to optimize this? I'm trying to avoid using python/sqlcmd/etc. to grab the blobs directly (avoiding JMP).

Re: downloading large columns database from database via odbc

Jeff_Perkinson — Tue, 24 Nov 2020 17:38:29 GMT

@vince_faller wrote:

It seems that the column width for JMP is 65536, so I can't download the whole column. It doesn't seem to be an ODBC only issue.

I don't have any immediate thoughts on your actual inquiry, but I do want to clarify the statement above. The max width for a character column in JMP is MUCH larger than 65536.

New Table( "untitled 15",
	Add Rows( 1 ),
	New Column( "repeat",
		Character,
		"Nominal",
		Formula( Repeat( "a", 128000000 ) ),
	),
	New Column( "length",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Length( :repeat ) ),
	)
)

I'm sure this detail isn't relevant to solving your speed/query problem and I may have misunderstood what you meant by column width.

Re: downloading large columns database from database via odbc

vince_faller — Tue, 24 Nov 2020 17:58:00 GMT

VERY relevant. My bad. Something weird is going on then.

Thanks @Jeff_Perkinson ,

Re: downloading large columns database from database via odbc

vince_faller — Mon, 14 Dec 2020 18:26:45 GMT

@Jeff_Perkinson

Looking more into this I can't explain this behavior. I'm able to get values from the exact same table and connection string through python. For whatever reason JMP blanks out the column as soon as it hits 65537.

Names default to here(1);
driver = "ODBC Driver 17 for SQL Server"; // using SQL Server
server = "localhost";
database = "Test";
username = "sa";
password = "REDACTED";

cxn_string = "DRIVER="||driver||";SERVER="||server||";UID="||username||";PWD="||
	password||";Database="||database;

cxn = create database connection(cxn_string);
executesql(cxn, 
	"
	CREATE TABLE [dbo].[LargeChar](
		[LargeCharID] [bigint] IDENTITY(1,1) NOT NULL,
		[SomeBigChar] [varchar](MAX) NOT NULL,
	 CONSTRAINT [PK_Blob] PRIMARY KEY CLUSTERED 
	(
		[LargeCharID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
	"
);

executeSQL(cxn, 
	"INSERT INTO [LargeChar]
	VALUES('"||REPEAT("A", 100000)||"')"
);

dt_download = executeSQL(cxn, 
	"SELECT SomeBigChar
	, SubString(Convert(VARCHAR(MAX), SomeBigChar), 1, 65536) AS Char65536 -- this works
	, SubString(Convert(VARCHAR(MAX), SomeBigChar), 1, 65537) AS Char65537 -- this doesn't
	FROM [LargeChar]", 
	"This should work?"
);
show(length(dt_download:SomeBigChar[1]), // full text doesn't show
	length(dt_download:Char65536[1]), // text that is only 65536 shows
	length(dt_download:Char65537[1]) // text that is one higher? nope
);

//execute SQL(cxn, "DROP TABLE [LargeChar]");
close database connection(cxn);

//Python script returns
/*
0    AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA...
Name: SomeBigChar, dtype: object
0    AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA...
Name: Char65536, dtype: object
0    AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA...
Name: Char65537, dtype: object
*/

Is there something that I'm missing? Or maybe underneath python is adding some functionality.

*EDIT* sorry for so many edits. Hope you don't get an email for each one.

Re: downloading large columns database from database via odbc

Jeff_Perkinson — Mon, 14 Dec 2020 19:34:39 GMT

This gets beyond my ability to test to understand what's happening.

@briancorcoran?

Re: downloading large columns database from database via odbc

bryan_boone — Mon, 14 Dec 2020 20:21:12 GMT

Can you try using:

Preferences( ODBC Disable Block Fetch( 1 ) );

Sometimes the ODBC Driver doesn't report the length of the column.

SQL Server is one of the drivers that supports block fetch (which is much more efficient than a row at a time), but if the length of the column isn't reported by the driver, the default value of 65536 is used.

Re: downloading large columns database from database via odbc

stan_koprowski — Mon, 14 Dec 2020 20:21:56 GMT

@vince_faller,

Are you using the same ODBC driver for Python and JMP?

Stan

Re: downloading large columns database from database via odbc

vince_faller — Mon, 14 Dec 2020 23:06:35 GMT

Ah, okay. so since I'm using VARCHAR(MAX), maybe there is no column length?

Unfortunately, even with the block fetch off it doesn't work.

Re: downloading large columns database from database via odbc

vince_faller — Mon, 14 Dec 2020 23:06:56 GMT

Yep. Using the exact same connection string.

Re: downloading large columns from database via odbc

Dahlia_Watkins — Mon, 09 Jan 2023 20:12:09 GMT

Per bryan_boone the behavior is due to these drivers not retuning the column length. Subsequently, JMP sets a default lenght of 65536 when this data is not provided.

I've added a new feature request to have this value increase since JMP can handle this data.

In the interim, using SQL Server (native) seems to work.

Re: downloading large columns from database via odbc

vince_faller — Thu, 14 Jan 2021 21:39:29 GMT

Thanks @Dahlia_Watkins

Anyone in internet land know if there's a way to force the size to be given from the transact?

My current implementation is abysmally slow (Subscripting in groups of 65536 and concatting together until the longest one has been taken care of) and I'm not allowed to change the driver; so I'm in a bit of a pickle.

Re: downloading large columns from database via odbc

vince_faller — Fri, 21 Jan 2022 18:33:13 GMT

Hey again @Dahlia_Watkins , any news if that feature request has gotten any traction?

Re: downloading large columns from database via odbc

Dahlia_Watkins — Wed, 26 Jan 2022 18:06:23 GMT

Hello, @vince_faller.

This issue should have been addressed in JMP 16.1. Please email support if you have tested this in 16.1 and still see the same behavior.

Re: downloading large columns from database via odbc

vince_faller — Wed, 26 Jan 2022 18:33:10 GMT

Ope, my bad. Will do. Thanks.

Re: downloading large columns from database via odbc

hardner — Thu, 05 Jan 2023 17:09:31 GMT

Just a note that this limitation does seem to have been removed in JMP16.1. Thanks!

Re: downloading large columns from database via odbc

vince_faller — Thu, 05 Jan 2023 19:38:58 GMT

Yep yep. Much appreciated.