Query Builder - Issue Clipping Large Text Strings

[#1]

I've been using Query Builder and File->Connect To->ODBC->Open Database Table to get data from a PostgreSQL data base.

The table I am getting from the database contains a column with large text strings.

My text strings are getting clipped when they exceed a length of 524,587. (2^19 -1)

Is there a setting in JMP and/or JSL that I can change to enable querying larger string values?

Motivating Situation and Debug Findings

I have been exploring the capabilities of the Torch Deep Learning add-in.

I have downloaded a kaggle data set of Dogs vs Wolves.

I used the Import Multiple Files JMP feature to create a data table that contains all of these files (2000 files).

I used JMP to clean up the data, create a response column, and validation columns, etc...

I created a PostgreSQL database, schema, and table.

I used JMP to connect to the database table, concatenated my Dogs vs Wolves data, and saved the information back to the database. The picture column is saved as text in the database using new image( char to blob("...the image info...") )

I have reviewed the data in the database, and everything appears as expected.

I used Query Builder to connect to my database and query the entire Dogs vs Wolves table.

I converted the Picture column from data type Character to Expression to get the images to show in the data table. Of the 2,000 entries, 1986 of them work as expected. 14 of the pictures don't display correctly. These entries include the following pictures:

• wolves/wolf-names-e1528831052890.jpg
• wolves/wolf-cubs.jpg
• wolves/wolf-4191896__480.png
• wolves/pexels-photo-682375.jpeg
• wolves/pexels-photo-682361.jpeg
• wolves/nature-animal-wolf-wilderness.jpg
• wolves/michael-mazzone-xvLRLLfP22o-unsplash.jpg
• wolves/hero_gray_wolf_animals.jpg
• wolves/gray-wolf_thumbJPG.jpg
• wolves/gray-wolf.jpg
• wolves/Winter-wolf-snow_-_West_Virginia_-_ForestWander.jpg
• wolves/SRX_WOLF_TRAPPING1.JPG.jpg
• wolves/Eurasian_wolfJPG.jpg
• dogs/n02106382_3756.jpg

I have reviewed the Picture information and found that the rows with issues don't have all of their information. The entries end in the middle of the image information.

I have reviewed the length of the Picture information and found that the corrupted Pictures all have a character length count of exactly 524,287. (2^19 - 1).

I have attempted to change the JSL parameters created by Query Builder, but I have not been successful in resolving the text length clipping. I'm not an SQL expert so I'm hoping that changing some of these settings can resolve my issue.

New SQL Query(
	Connection(
		"
		ODBC:AB=0;
		BATCHSIZE=100;
		BI=0;
		BOOLSASCHAR=0;
		BYTEAASLONGVARBINARY=1;
		COMMLOG=2;
		CONNSETTINGS=;
		D6=-101;
		DEBUG=2;
		DRIVER={PostgreSQL Unicode(x64)};
		EXTRASYSTABLEPREFIXES=;
		FAKEOIDINDEX=0;
		FETCH=100;
		FETCHREFCURSORS=0;
		IGNORETIMEOUT=0;
		KEEPALIVEINTERVAL=-1;
		KEEPALIVETIME=-1;
		LFCONVERSION=1;
		LOWERCASEIDENTIFIER=0;
		MAXLONGVARCHARSIZE=8092;
		MAXVARCHARSIZE=255;
		OPTIONALERRORS=0;
		PARSE=0;
		PQOPT=;
		PROTOCOL=7.4;
		READONLY=0;
		ROWVERSIONING=0;
		SHOWOIDCOLUMN=0;
		SHOWSYSTEMTABLES=0;
		SSLMODE=disable;
		TEXTASLONGVARCHAR=0;
		TRUEISMINUS1=0;
		UNIQUEINDEX=1;
		UNKNOWNSASLONGVARCHAR=0;
		UNKNOWNSIZES=0;
		UPDATABLECURSORS=1;
		USEDECLAREFETCH=1;
		USESERVERSIDEPREPARE=1;
		XAOPT=1;
		"
	),
	QueryName( "dogs_v_wolves" ),
	Select(
		Distinct,
		Column( "Picture", "t1" ),
		Column( "Label", "t1" ),
		Column( "File Path", "t1" ),
		Column( "Short File Name", "t1" ),
		Column( "FoldA", "t1" ),
		Column( "FoldB", "t1" ),
		Column( "FoldC", "t1" ),
		Column( "Validation", "t1" )
	),
	From( Table( "dogs_v_wolves", Schema( "dogs_v_wolves" ), Alias( "t1" ) ) )
) << Run;

I have attempted to change the JSL parameters created by Open Database Table, but I have not been successful in resolving the text length clipping. Again, I'm not an SQL expert so I'm hoping that changing some of these settings can resolve my issue.

Open Database(
	"
	AB=0;
	BATCHSIZE=100;
	BI=0;
	BOOLSASCHAR=0;
	BYTEAASLONGVARBINARY=1;
	COMMLOG=2;
	CONNSETTINGS=;
	D6=-101;
	DEBUG=2;
	DRIVER={PostgreSQL Unicode(x64)};
	EXTRASYSTABLEPREFIXES=;
	FAKEOIDINDEX=0;
	FETCH=100;
	FETCHREFCURSORS=0;
	IGNORETIMEOUT=0;
	KEEPALIVEINTERVAL=-1;
	KEEPALIVETIME=-1;
	LFCONVERSION=1;
	LOWERCASEIDENTIFIER=0;
	MAXLONGVARCHARSIZE=8092;
	MAXVARCHARSIZE=255;
	OPTIONALERRORS=0;
	PARSE=0;
	PQOPT=;
	PROTOCOL=7.4;
	READONLY=0;
	ROWVERSIONING=0;
	SHOWOIDCOLUMN=0;
	SHOWSYSTEMTABLES=0;
	SSLMODE=disable;
	TEXTASLONGVARCHAR=0;
	TRUEISMINUS1=0;
	UNIQUEINDEX=1;
	UNKNOWNSASLONGVARCHAR=0;
	UNKNOWNSIZES=0;
	UPDATABLECURSORS=1;
	USEDECLAREFETCH=1;
	USESERVERSIDEPREPARE=1;
	XAOPT=1;
	",
	"SELECT * FROM dogs_v_wolves.dogs_v_wolves"
);

To further investigate the situation, I created a python script that performs an SQL query and saves the results into a pandas dataframe. Then I created a JSL file that runs the python script file and converts the pandas dataframe into a JMP data table.

The python file is named "sqlQuery.py" and contains:

import pandas as pd
import sqlalchemy as db

engine = db.create_engine("postgresql+psycopg2://##connection information##")

query = """
SELECT DISTINCT *
FROM dogs_v_wolves.dogs_v_wolves;
"""

df = pd.read_sql_query(query, engine)

The JSL file contains:

Names Default To Here( 1 );

pythonFilePath = "sqlQuery.py";

Python Submit File( pythonFilePath );

dt = python get(df);

dt:Picture << set data type(expression);

When I run this code the data table contains the full text strings. When I convert the Picture column to an Expression data type, the images are displayed correctly.

Conclusions

JMP is successfully writing large text strings to the database.

JMP is clipping large text strings when querying from the database using Query Builder and/or Open Database Table.

Running an SQL query outside of JMP returns the full-length text strings that are stored in the database.

Desire

I would like to be able to utilize the convenience of Query Builder while working on my image processing projects.

Question

Is there a setting in JMP and/or JSL that I can change to enable querying larger string values?

[#2 · Accepted solution]

Have you tried with postgresql connector (this will use Python and require few python packages, psycopg2+pandas) PostgreSQL Data Connector (JMP Marketplace) ? 

[#3]

I had not tried the PostgreSQL Data Connector.  I was only using the Built-in ODBC connection.

Based on your recommendation, I have tried the PostgreSQL Data Connector, and I can confirm that this method resolves the long text clipping issue. 

I knew there should be an easy solution, but I couldn't find it on my own. 

Thanks for the guidance.

[#4]

There could be solution with the ODBC but it might require a bit more looking around as I would assume these would affect the data you get

MAXLONGVARCHARSIZE=8092;
MAXVARCHARSIZE=255;
TEXTASLONGVARCHAR=0;

and they clearly do not match with your 524287 so it is most likely being overwritten somewhere else (while still being too small for your use case).

@briancorcoran 

[#5]

That's exactly where I got stuck. I became a bit too focused on parameters like:

BOOLSASCHAR=0;
BYTEAASLONGVARBINARY=1;
MAXLONGVARCHARSIZE=8092;
MAXVARCHARSIZE=255;
TEXTASLONGVARCHAR=0;
UNKNOWNSASLONGVARCHAR=0;
UNKNOWNSIZES=0;

But even with ChatGPT and Claude trying to help, I couldn't find a parameter that affected the text string length limit that I was getting. I tried many combination of settings recommended by the AIs.

ChatGPT did help me create the python code I used for my basic SQL query provided in my initial post, but it never recommended using the PostgreSQL Data Connector.

Even though I could get it done with my own python SQL query, it felt like reinventing the wheel and it would never be as easy as it should be. 

[#6]

You want to turn off Block Fetching

Preferences(ODBC Disable Block Fetch(1))

using JSL before connecting to the ODBC data source.

JMP uses block fetching to improve performance when importing data from ODBC sources.
A block fetch is an N x N contiguous block of memory that will get populated on each read from the data source, rather than line by line. With large amounts of character data this will get clipped due to the size of the block. Toggling this preference will have JMP read line by line and get the entire string.

[#7]

I have tried this recommendation and unfortunately it does not appear to work.

I ran the jsl code and then queried my table using File -> Connect To -> ODBC -> Open Database Table.

Despite the preference change, I continue to get long text strings clipped to a character length of 524,287.

[#8]

OK thanks for the update. Also remember that each character is a unicode character (UTF8).
In the case where the ODBC driver doesn't know the size, JMP will use 4MB.

Can you update the wishlist?