Hi I am having similar problems like many other, e.g. https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Input-Tool-Truncating-Fields-to-lt-256-Characters-for-Database/td-p/763752 but no solutions seems to have been provided.I can't figure out what is wrong with the driver or how to circumvent it.I the above thread, the suggestion is to use OLEDB instead of ODBC, but I can't see that the Postgres supports OLEDB, only ODBC
When I look directly at the Metadata from the Input Control, I can see that all the VARCHAR fields are truncated to 255:
And so far my efforts to circumvent that have failed, so I open to any suggestions./Thomas
Okay, after I pushed the Post button, I kind a got an idea.I simply did a cast on the truncated field in the SQL like this
It ain't pretty but it works for now. I guess if the result supersedes 8190 characters, I will have to go with cast to guid 😂If anyone comes up with a better solution, then I am still interested./Thomas
I do not think what you are seeing is what you think you are seeing. I believe there are allocations to columns based upon the max length of data in that columns. For those tables the max length is 256 bytes (in 0 script - 255 characters). In others it's 8192 characters (which for some reason is shown here as 8190) and in others its 4096^2 (16777216 - the max string length in many dbs). in your screenshot type has an allocation of 8190 bytes - while others have 255 - why? because that is the minimum possible memory allocation for that field - ie it does not exceed 255 characters in the current db.
@Verakso have you tried generic OLEDB connection?
Well, that's not correct @apathetichell The driver explicit returns these fields as 255 even though the field clearly exceeds that, because before my "hack" the data was truncated
Hi @Raj Please show me a guide how to use generic OLEDB with Postgress in Alteryx, because I haven't had any success with that.
Not sure - but this is my experience:
I'm connecting to Postgres via Greenplum via In-DB. I have a slew of string fields. The ones which do not exceed 255 characters go to canvas at 255. the larger field is going to canvas at 1024. All are V_WString. I am not performing any casts.
My MaxLongVarChar is 8190. My MaxVarChar is 255 in my driver config.
internally what is the difference between result & type in your db? if you have result set to as varchar(255) - and try to add data to this field in your in-db tools - yes - it should truncate. If both of these fields hvae the same datatype in your DB - I do not see why one is being set at 8190 and one is being set at 255 if both have values which exceed 255 - and there is no max size for this field set at the db level.
and in the metadata what are type and status (if aren't they varchar as well?)
