Generic In database connection : please stop inventing and changing SQL type

[simonaubert_bd]

Hello,

My issue is very easy to solve. I want to use the generic ODBC In database for a specific base (monetdb here but it isn't important).

The connexion works just fine. However, I cannot create table because the data types are changed and does not even exist. Here is my data with some Date type :

And here the error in my data stream in give me this very interesting message  :

Error: Entrée du flux de données (2): Erreur lors de la création de la table "formation.temp1" : [MonetDB][ODBC Driver 11.31.11]Type (datetime) unknown in: "create  table "formation"."temp1" ("AccountNumber" varchar(255),"BillToAddressID"
syntax error, unexpected IDENT in: ""Freight""

CREATE  TABLE "formation"."temp1" ("AccountNumber" varchar(255),"BillToAddressID" float,"BusinessEntityID" float,"Comment
" float,"CreditCardApprovalCode" varchar(255),"CreditCardID" float,"CurrencyRateID" float,"CustomerID" float,"DueDate" datetime,"Freight" real,"OnlineOrderFlag" float,"OrderDate" datetime,"OrderDate_Month" varchar(255),"OrderDate_Year" float,"PurchaseOrderNumber" varchar(255),"RevisionNumber" float,"SalesOrderID" float,"SalesOrderNumber" varchar(255),"ShipDate" datetime,"ShipMethodID" float,"ShipToAddressID" float,"Status" float,"SubTotal" float,"TaxAmt" float,"TotalDue" float)

1/ My field is a date, why do you want to convert it in Datetime??

2/ Datetime is not even a usual field type in sql database (at least not supported by monetdb, vertica, postgresql, oracle, etc, etc...)... it should obviously be timestamp

Currently, this non-specific in database ODBC connexion cannot be used at all!

[simonaubert_bd]

Same isse here with a w_varchar changed in ... nvarchar??

[simonaubert_bd]

it appears nvarchar is very specific to MSQL Server... while other bases only use varchar :

https://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes

http://www.datajaguar.com/doc/JaguarUserManual.pdf
https://mariadb.com/kb/en/library/data-types/

etc, etc.

I think it makes no sense to put the MSQL Server specific types here because we can already create a MSQL Server connexion. What is interesting is to wok with other databases.

[simonaubert_bd]

After a (brief^^) survey, I can safely state the generic in-db connection is not used. Given what I wrote previously, it's not surprising. However, I relly love the idea of having a generic in-db connection. I hope to have an improvement on this topic to not waste the developer work : ) We're reelly close to a working implementation.

🙂https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Do-you-use-generic-in-database-connect...