PostgreSQL ODBC SQL_CONVERT_* in SQLGetInfo support

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

PostgreSQL ODBC SQL_CONVERT_* in SQLGetInfo support

Xuesong Gao

Hi,

 

I am using PostgreSQL ODBC with Power BI and got this error in Power BI log:

 

Failed to convert from wchar to wvarchar for expression \"Snapshot Year Month\". You can override this by using SqlGetInfo for converting from WCHAR to WVARCHAR.

 

It turned out that info.c does not return correct values even though PostgreSQL support conversions between different data type. So I put following line there and it starts working:

 

                                case SQL_CONVERT_BIT:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_BIT |

                                                                SQL_CVT_INTEGER |

                                                                SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR;

                                                break;

                                case SQL_CONVERT_TINYINT:

                                                len = sizeof(SQLUINTEGER);

                                                value = 0; /* PostgreSQL does not support tinyint */

                                                break;

                                case SQL_CONVERT_INTEGER:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_BIT |

                                                                SQL_CVT_SMALLINT | SQL_CVT_INTEGER |  SQL_CVT_BIGINT |

                                                                SQL_CVT_REAL | SQL_CVT_FLOAT | SQL_CVT_DOUBLE | SQL_CVT_NUMERIC | SQL_CVT_DECIMAL |

                                                                SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR;

                                                break;

                                case SQL_CONVERT_SMALLINT:

                                case SQL_CONVERT_BIGINT:

                                case SQL_CONVERT_REAL:

                                case SQL_CONVERT_FLOAT:

                                case SQL_CONVERT_DOUBLE:

                                case SQL_CONVERT_NUMERIC:

                                case SQL_CONVERT_DECIMAL:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_SMALLINT | SQL_CVT_INTEGER |  SQL_CVT_BIGINT |

                                                                SQL_CVT_REAL | SQL_CVT_FLOAT | SQL_CVT_DOUBLE | SQL_CVT_NUMERIC | SQL_CVT_DECIMAL |

                                                                SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR;

                                                break;

                                case SQL_CONVERT_CHAR:

                                case SQL_CONVERT_VARCHAR:                                /* ODBC 1.0 */

                                case SQL_CONVERT_LONGVARCHAR:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_BIT |

                                                                SQL_CVT_SMALLINT | SQL_CVT_INTEGER |  SQL_CVT_BIGINT |

                                                                SQL_CVT_REAL | SQL_CVT_FLOAT | SQL_CVT_DOUBLE | SQL_CVT_NUMERIC | SQL_CVT_DECIMAL |

                                                                SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR |

                                                                SQL_CVT_DATE | SQL_CVT_TIME | SQL_CVT_TIMESTAMP;

                                                break;

                                case SQL_CONVERT_DATE:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR |

                                                                SQL_CVT_DATE | SQL_CVT_TIMESTAMP;

                                                break;

                                case SQL_CONVERT_TIME:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR |

                                                                SQL_CVT_TIME;

                                                break;

                                case SQL_CONVERT_TIMESTAMP:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR |

                                                                SQL_CVT_DATE | SQL_CVT_TIME | SQL_CVT_TIMESTAMP;

                                                break;

                                case SQL_CONVERT_BINARY:

                                case SQL_CONVERT_VARBINARY:                            /* ODBC 1.0 */

                                                len = sizeof(SQLUINTEGER);

                                                value = 0; /* PostgreSQL does not support binary, varbinary */

                                                break;

                                case SQL_CONVERT_LONGVARBINARY:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_LONGVARBINARY;

                                                break;

#ifdef UNICODE_SUPPORT

                                case SQL_CONVERT_WCHAR:

                                case SQL_CONVERT_WLONGVARCHAR:

                                case SQL_CONVERT_WVARCHAR:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_BIT |

                                                                SQL_CVT_SMALLINT | SQL_CVT_INTEGER |  SQL_CVT_BIGINT |

                                                                SQL_CVT_REAL | SQL_CVT_FLOAT | SQL_CVT_DOUBLE | SQL_CVT_NUMERIC | SQL_CVT_DECIMAL |

                                                                SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR |

                                                                SQL_CVT_DATE | SQL_CVT_TIME |  SQL_CVT_TIMESTAMP;

                                                break;

#endif /* UNICODE_SUPPORT */

 

 

 

<img width="110" height="105" id="_x0000_i1025" src="file:///C:\Users\Song\AppData\Roaming\Microsoft\Signatures\senturus-signature_files\senturus-logo-small.jpg">

Song  X. Gao
Solutions Architect

Email [hidden email]
Mobile 732 406 6347
senturus.com

 

 

Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL ODBC SQL_CONVERT_* in SQLGetInfo support

Inoue, Hiroshi-2
Hi,

I would take care of this issue.

regards,
Hiroshi Inoue

On 2020/07/02 2:14, Xuesong Gao wrote:

Hi,

 

I am using PostgreSQL ODBC with Power BI and got this error in Power BI log:

 

Failed to convert from wchar to wvarchar for expression \"Snapshot Year Month\". You can override this by using SqlGetInfo for converting from WCHAR to WVARCHAR.

 

It turned out that info.c does not return correct values even though PostgreSQL support conversions between different data type. So I put following line there and it starts working:

 

                                case SQL_CONVERT_BIT:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_BIT |

                                                                SQL_CVT_INTEGER |

                                                                SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR;

                                                break;

                                case SQL_CONVERT_TINYINT:

                                                len = sizeof(SQLUINTEGER);

                                                value = 0; /* PostgreSQL does not support tinyint */

                                                break;

                                case SQL_CONVERT_INTEGER:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_BIT |

                                                                SQL_CVT_SMALLINT | SQL_CVT_INTEGER |  SQL_CVT_BIGINT |

                                                                SQL_CVT_REAL | SQL_CVT_FLOAT | SQL_CVT_DOUBLE | SQL_CVT_NUMERIC | SQL_CVT_DECIMAL |

                                                                SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR;

                                                break;

                                case SQL_CONVERT_SMALLINT:

                                case SQL_CONVERT_BIGINT:

                                case SQL_CONVERT_REAL:

                                case SQL_CONVERT_FLOAT:

                                case SQL_CONVERT_DOUBLE:

                                case SQL_CONVERT_NUMERIC:

                                case SQL_CONVERT_DECIMAL:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_SMALLINT | SQL_CVT_INTEGER |  SQL_CVT_BIGINT |

                                                                SQL_CVT_REAL | SQL_CVT_FLOAT | SQL_CVT_DOUBLE | SQL_CVT_NUMERIC | SQL_CVT_DECIMAL |

                                                                SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR;

                                                break;

                                case SQL_CONVERT_CHAR:

                                case SQL_CONVERT_VARCHAR:                                /* ODBC 1.0 */

                                case SQL_CONVERT_LONGVARCHAR:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_BIT |

                                                                SQL_CVT_SMALLINT | SQL_CVT_INTEGER |  SQL_CVT_BIGINT |

                                                                SQL_CVT_REAL | SQL_CVT_FLOAT | SQL_CVT_DOUBLE | SQL_CVT_NUMERIC | SQL_CVT_DECIMAL |

                                                                SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR |

                                                                SQL_CVT_DATE | SQL_CVT_TIME | SQL_CVT_TIMESTAMP;

                                                break;

                                case SQL_CONVERT_DATE:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR |

                                                                SQL_CVT_DATE | SQL_CVT_TIMESTAMP;

                                                break;

                                case SQL_CONVERT_TIME:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR |

                                                                SQL_CVT_TIME;

                                                break;

                                case SQL_CONVERT_TIMESTAMP:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR |

                                                                SQL_CVT_DATE | SQL_CVT_TIME | SQL_CVT_TIMESTAMP;

                                                break;

                                case SQL_CONVERT_BINARY:

                                case SQL_CONVERT_VARBINARY:                            /* ODBC 1.0 */

                                                len = sizeof(SQLUINTEGER);

                                                value = 0; /* PostgreSQL does not support binary, varbinary */

                                                break;

                                case SQL_CONVERT_LONGVARBINARY:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_LONGVARBINARY;

                                                break;

#ifdef UNICODE_SUPPORT

                                case SQL_CONVERT_WCHAR:

                                case SQL_CONVERT_WLONGVARCHAR:

                                case SQL_CONVERT_WVARCHAR:

                                                len = sizeof(SQLUINTEGER);

                                                value = SQL_CVT_BIT |

                                                                SQL_CVT_SMALLINT | SQL_CVT_INTEGER |  SQL_CVT_BIGINT |

                                                                SQL_CVT_REAL | SQL_CVT_FLOAT | SQL_CVT_DOUBLE | SQL_CVT_NUMERIC | SQL_CVT_DECIMAL |

                                                                SQL_CVT_CHAR | SQL_CVT_VARCHAR | SQL_CVT_LONGVARCHAR | SQL_CVT_WCHAR | SQL_CVT_WVARCHAR | SQL_CVT_WLONGVARCHAR |

                                                                SQL_CVT_DATE | SQL_CVT_TIME |  SQL_CVT_TIMESTAMP;

                                                break;

#endif /* UNICODE_SUPPORT */

 

 

 

<img id="_x0000_i1025" src="file:///C:\Users\Song\AppData\Roaming\Microsoft\Signatures\senturus-signature_files\senturus-logo-small.jpg" moz-do-not-send="true" width="110" height="105">

Song  X. Gao
Solutions Architect

Email [hidden email]
Mobile 732 406 6347
senturus.com