{"action":"created","issue":{"url":"https://api.github.com/repos/Kozea/Multicorn/issues/133","repository_url":"https://api.github.com/repos/Kozea/Multicorn","labels_url":"https://api.github.com/repos/Kozea/Multicorn/issues/133/labels{/name}","comments_url":"https://api.github.com/repos/Kozea/Multicorn/issues/133/comments","events_url":"https://api.github.com/repos/Kozea/Multicorn/issues/133/events","html_url":"https://github.com/Kozea/Multicorn/issues/133","id":136470129,"number":133,"title":"sqlalchemy_fdw pyodbc error from PG9.5 to SQLServer 2008","user":{"login":"jkort","id":15162325,"avatar_url":"https://avatars.githubusercontent.com/u/15162325?v=3","gravatar_id":"","url":"https://api.github.com/users/jkort","html_url":"https://github.com/jkort","followers_url":"https://api.github.com/users/jkort/followers","following_url":"https://api.github.com/users/jkort/following{/other_user}","gists_url":"https://api.github.com/users/jkort/gists{/gist_id}","starred_url":"https://api.github.com/users/jkort/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jkort/subscriptions","organizations_url":"https://api.github.com/users/jkort/orgs","repos_url":"https://api.github.com/users/jkort/repos","events_url":"https://api.github.com/users/jkort/events{/privacy}","received_events_url":"https://api.github.com/users/jkort/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":10,"created_at":"2016-02-25T18:23:57Z","updated_at":"2016-02-27T12:47:49Z","closed_at":null,"body":"Hello, trying to connect via fdw from a PG 9.5 database under Ubuntu 14.04 to a remote SQLServer 2008R2SP1 database with hundreds of tables and views over a VPN.  I have no control over the SQLServer db; it belongs to an external party.\r\n\r\nI was successful in getting FreeTDS to work (tested with queries under tsql), able to query my remote SQLServer database using pyodbc, and pyodbc with sqlalchemy under python 2.7.6.  So I feel good about my odbc.ini, odbcinst.ini and freetds.conf files.\r\n\r\nTo be clear - I am inexperienced with postgres, python, freetds and all of these tools.\r\n\r\nWhen I try to use sqlalchemy_fdw, that is where I'm getting stuck.  I *think* my CREATE SERVER statement is ok, as I can get connection errors back if I deliberately get the password wrong.  Here is what I have tried (with dummy strings for sensitive information):\r\n\r\n```\r\ndrop schema XYZrpt cascade;\r\ncreate schema if not exists XYZrpt;\r\n\r\nDROP SERVER IF EXISTS XYZrpt_svr CASCADE;\r\n\r\ncreate server XYZrpt_svr foreign data wrapper multicorn \r\n   options (\r\n    wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw', \r\n    db_url 'mssql+pyodbc://XXXXXXXX:YYYYYYYY@MedidataVPN'\r\n    );\r\n\r\nimport foreign schema \"ABC.dbo\" from SERVER XYZrpt_svr into XYZrpt;\r\n```\r\n\r\nwhere ABC is a database name on the SQLServer.  The dbo schema has perhaps hundreds of tables, and this ran for over 12 minutes before I got this error back:\r\n\r\n```\r\nERROR:  Error in python: TypeError\r\nDETAIL:  %d format: a number is required, not str\r\n********** Error **********\r\n\r\nERROR: Error in python: TypeError\r\nSQL state: XX000\r\nDetail: %d format: a number is required, not str\r\n```\r\n\r\nNext I tried to limit to just a single table that I know is in that ABC database, dbo.Audits, by using this IMPORT FOREIGN SCHEMA statement:\r\n\r\nimport foreign schema \"ABC.dbo\" LIMIT TO (\"Audits\") from SERVER XYZrpt_svr into XYZrpt;\r\n\r\nand that returned this error:\r\n\r\n```\r\nERROR:  Error in python: UnsupportedCompilationError\r\nDETAIL:  Compiler <sqlalchemy.dialects.postgresql.base.PGTypeCompiler object at 0x7fab8e7e5350> can't render element of type <class 'sqlalchemy.dialects.mssql.base.TINYINT'>\r\n********** Error **********\r\n\r\nERROR: Error in python: UnsupportedCompilationError\r\nSQL state: XX000\r\nDetail: Compiler <sqlalchemy.dialects.postgresql.base.PGTypeCompiler object at 0x7fab8e7e5350> can't render element of type <class 'sqlalchemy.dialects.mssql.base.TINYINT'>\r\n```\r\n\r\nthere is a TinyInt field in that SQL Server table.\r\n\r\nAny suggestions on what I should do differently would be greatly appreciated!  If I can provide more information to help determine the cause of the error, please let me know.\r\n\r\nBest Regards,\r\n-Jack\r\n"},"comment":{"url":"https://api.github.com/repos/Kozea/Multicorn/issues/comments/189632307","html_url":"https://github.com/Kozea/Multicorn/issues/133#issuecomment-189632307","issue_url":"https://api.github.com/repos/Kozea/Multicorn/issues/133","id":189632307,"user":{"login":"rdunklau","id":172056,"avatar_url":"https://avatars.githubusercontent.com/u/172056?v=3","gravatar_id":"","url":"https://api.github.com/users/rdunklau","html_url":"https://github.com/rdunklau","followers_url":"https://api.github.com/users/rdunklau/followers","following_url":"https://api.github.com/users/rdunklau/following{/other_user}","gists_url":"https://api.github.com/users/rdunklau/gists{/gist_id}","starred_url":"https://api.github.com/users/rdunklau/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rdunklau/subscriptions","organizations_url":"https://api.github.com/users/rdunklau/orgs","repos_url":"https://api.github.com/users/rdunklau/repos","events_url":"https://api.github.com/users/rdunklau/events{/privacy}","received_events_url":"https://api.github.com/users/rdunklau/received_events","type":"User","site_admin":false},"created_at":"2016-02-27T12:47:49Z","updated_at":"2016-02-27T12:47:49Z","body":"Thank you to both of you for figuring the needed types conversions. I think I was a bit overoptimistic in the ability to match different types from different databases.\r\n\r\nThe way its done now is quite simplistic, maybe there should be an additional way to control the conversions.\r\n\r\nvarbinary should probably be mapped to a bytea object."}}