Maximum length of cell when importing sql database.

Hi,

Im am importing a Databricks sql-based database that includes some cells with a 500 value array.

example:

'[-0.08398959, -0.08354805, -0.08310095, -0.08264837, -0.08219037, -0.08172707, -0.0812584, -0.08078446, -0.08030521, -0.07982059, -0.0793306, -0.07883536, -0.078335, -0.07782964, -0.0773192, -0.07680371, -0.07628322, -0.07575784, -0.0752276, -0.07469248, ...']

The issue im facing is that whenever I import this data with fetch() or sqlread() the data is cut off to 255 characters. Is there any way to set the max length to infinity or a very high value? I am expecting it to be at least 20x longer.

current code looks like this:

conn=odbc('DSN=Database');
vars = [];
%Set query to execute on the database
% query = ['SELECT ','Order_No, Product, Annealing_No, Specification, Protocol, Frequency, Bmax, Hmax, Ps, H, B, Grade, f, B_rounded, Test',' FROM lamellastack_dev.s_remacomp_metadata'];
query = 'SELECT * FROM anonymizedDatabase';
tablename = 'anonymizedDatabase.table';

%% Execute query and fetch results
data = fetch(conn,query);

%% Alternative
data = sqlread(conn,tablename,'MaxRows',100);

%% Close connection to database
close(conn)

Thanks in advance.

Answer 1

[Riya]

Hello Joep,

As per my understanding, you want to get the maximum length of a cell when importing SQLdatabase.

Please know that in this case, you can use the “options.MaxVarCharLength” which is a property that can be set when using the “fetch” or “sqlread” functions to specify the maximum length of character data that should be retrieved from a SQL-based database.

In some cases, the default behavior of these functions is to limit the length of fetched data to a certain number of characters, which can result in truncation of longer strings or arrays. By setting “options.MaxVarCharLength” to a higher value, you can increase the maximum length of the fetched data.

To set the maximum length for importing data from a Databricks SQL-based database, you can modify the “fetch” or “sqlread” functions to specify the desired maximum length. By default, these functions may limit the length of fetched data to a certain number of characters, such as 255 characters.

Here's a sample code to demonstrate how you can modify your code to set a higher maximum length:

conn = odbc('DSN=Database'); 
vars = []; 
query = 'SELECT * FROM anonymizedDatabase'; 
tablename = 'anonymizedDatabase.table'; 

% Set the maximum length for data retrieval 
options = fetchOptions(); 
options.MaxVarCharLength = 10000;  % Set a high value for maximum length 

%% Execute query and fetch results 
data = fetch(conn, query, options); 

%% Alternative 
data = sqlread(conn, tablename, 'MaxRows', 100, 'MaxVarCharLength', 10000); 

%% Close connection to the database 
close(conn) 

In the above code, “options.MaxVarCharLength” is set to a higher value i.e. 10000 to allow for longer data to be fetched. You can adjust this value as per your requirements.

I hope this helps!

[Joep van de Ven]

fetchOptions() doesnt seem to be a valid function. Using databaseImportOptions I can get a selection of options. But MaxVarCharLength is not one of the choices. I think i found a fix in the ODBC driver interface.

Under Advanced Option I can edit the Default String Column Length. This was set to 255. I set it to 20.000 and the issue seems to be gone. Thanks for the help though. I will see if I can find another fix.