sql select not returning full string from query

My sql select / fetch / sqlread statements are not returning the full string of a field in a table. It also incorrectly identifies the type of the fields. In the first figure below, you can see the data type for the field 'data', and its contents.

in the next figure, you can see the query and the result. It is only giving me the single quote character. Previously, I did not have quotes around this data, and it was giving me the first character (0, -, 1, etc...), so adding the quotes didn't change anything.

When querying with raw sql or python or c# I do not have this issue. Please help! Thanks.

[Devin Jean]

Have you already tried changing nvarchar(max) to just varchar(max)? As I understand it, nvarchar stores a UTF-16 sequence, meaning the first character (ascii ') will be suffixed with what basically amounts to a null terminator. It's possible that their specific implementation expects a UTF-8 sequence and stops on the first null (which it shouldn't do, but maybe does).

[Tim Darrah]

Thanks for the suggestion, there is no difference. I even tried retrieving just 1 record (each record is 35,640 characters long).

Is this an X and Y problem? The application is as follows: a c# program queries an accelerometer for a 1 second reading every 10 seconds. The data is received as a float array, converted to a string, and inserted into the table. Should I store it differently?

[Devin Jean]

I mean, you could store it differently (depending on db, it might support arrays, or you could store as 32- or 16-bit floating point binary), but it should still work without all that. It's especially weird that it works from raw sql/c#/python... I assume when you (successfully) read them back from c#/python they have the full content that was inserted. It might be a size issue (but I don't know why). At this point I'm suspicious of max as the extent, since it's handled differently than a fixed max size. But if you really need the full 35,640 chars, I would think max is probably better. Since they're so big, you could also try making them blobs; maybe the code that handles those will work. It's sad, but I'm mostly just coming up with hacks since it sounds like their impl is just plain broken.

Is the full array needed for each entry? You could try doing something like a 1s reading every 10s, but group them into e.g. 100ms chunks and store an array of averages. If that would be enough, it could at least help with scalability, and switching to varchar(10*n) might get around their bug (or just being smaller might fix it, idk).

[Tim Darrah]

Thank you for your advice, I will look into these suggestions. Yeah, I get the entire data sample with other languages.

[Tim Darrah]

Changed the datatype to text, works. Thanks for pointing me in the right direction!

Answer 1

[Tim Darrah]

Changed the datatype to text, works. Thanks for pointing me in the right direction!