1#include "ColumnInfoHandler.h"
2#include "getIdentifierQuote.h"
3#if USE_POCO_SQLODBC || USE_POCO_DATAODBC
4
5#if USE_POCO_SQLODBC
6#include <Poco/SQL/ODBC/ODBCException.h>
7#include <Poco/SQL/ODBC/SessionImpl.h>
8#include <Poco/SQL/ODBC/Utility.h>
9#define POCO_SQL_ODBC_CLASS Poco::SQL::ODBC
10#endif
11#if USE_POCO_DATAODBC
12#include <Poco/Data/ODBC/ODBCException.h>
13#include <Poco/Data/ODBC/SessionImpl.h>
14#include <Poco/Data/ODBC/Utility.h>
15#define POCO_SQL_ODBC_CLASS Poco::Data::ODBC
16#endif
17
18#include <Poco/Net/HTTPServerRequest.h>
19#include <Poco/Net/HTTPServerResponse.h>
20#include <Poco/Net/HTMLForm.h>
21#include <Poco/NumberParser.h>
22#include <DataTypes/DataTypeFactory.h>
23#include <DataTypes/DataTypeNullable.h>
24#include <IO/WriteBufferFromHTTPServerResponse.h>
25#include <IO/WriteHelpers.h>
26#include <Parsers/ParserQueryWithOutput.h>
27#include <Parsers/parseQuery.h>
28#include <common/logger_useful.h>
29#include <ext/scope_guard.h>
30#include "validateODBCConnectionString.h"
31
32namespace DB
33{
34namespace
35{
36 DataTypePtr getDataType(SQLSMALLINT type)
37 {
38 const auto & factory = DataTypeFactory::instance();
39
40 switch (type)
41 {
42 case SQL_TINYINT:
43 return factory.get("Int8");
44 case SQL_INTEGER:
45 return factory.get("Int32");
46 case SQL_SMALLINT:
47 return factory.get("Int16");
48 case SQL_BIGINT:
49 return factory.get("Int64");
50 case SQL_FLOAT:
51 return factory.get("Float64");
52 case SQL_REAL:
53 return factory.get("Float32");
54 case SQL_DOUBLE:
55 return factory.get("Float64");
56 case SQL_DATETIME:
57 return factory.get("DateTime");
58 case SQL_TYPE_TIMESTAMP:
59 return factory.get("DateTime");
60 case SQL_TYPE_DATE:
61 return factory.get("Date");
62 default:
63 return factory.get("String");
64 }
65 }
66}
67
68namespace ErrorCodes
69{
70 extern const int ILLEGAL_TYPE_OF_ARGUMENT;
71}
72
73void ODBCColumnsInfoHandler::handleRequest(Poco::Net::HTTPServerRequest & request, Poco::Net::HTTPServerResponse & response)
74{
75 Poco::Net::HTMLForm params(request, request.stream());
76 LOG_TRACE(log, "Request URI: " + request.getURI());
77
78 auto process_error = [&response, this](const std::string & message)
79 {
80 response.setStatusAndReason(Poco::Net::HTTPResponse::HTTP_INTERNAL_SERVER_ERROR);
81 if (!response.sent())
82 response.send() << message << std::endl;
83 LOG_WARNING(log, message);
84 };
85
86 if (!params.has("table"))
87 {
88 process_error("No 'table' param in request URL");
89 return;
90 }
91 if (!params.has("connection_string"))
92 {
93 process_error("No 'connection_string' in request URL");
94 return;
95 }
96 std::string schema_name = "";
97 std::string table_name = params.get("table");
98 std::string connection_string = params.get("connection_string");
99
100 if (params.has("schema"))
101 {
102 schema_name = params.get("schema");
103 LOG_TRACE(log, "Will fetch info for table '" << schema_name + "." + table_name << "'");
104 }
105 else
106 LOG_TRACE(log, "Will fetch info for table '" << table_name << "'");
107 LOG_TRACE(log, "Got connection str '" << connection_string << "'");
108
109 try
110 {
111 const bool external_table_functions_use_nulls = Poco::NumberParser::parseBool(params.get("external_table_functions_use_nulls", "false"));
112
113 POCO_SQL_ODBC_CLASS::SessionImpl session(validateODBCConnectionString(connection_string), DBMS_DEFAULT_CONNECT_TIMEOUT_SEC);
114 SQLHDBC hdbc = session.dbc().handle();
115
116 SQLHSTMT hstmt = nullptr;
117
118 if (POCO_SQL_ODBC_CLASS::Utility::isError(SQLAllocStmt(hdbc, &hstmt)))
119 throw POCO_SQL_ODBC_CLASS::ODBCException("Could not allocate connection handle.");
120
121 SCOPE_EXIT(SQLFreeStmt(hstmt, SQL_DROP));
122
123 /// TODO Why not do SQLColumns instead?
124 std::string name = schema_name.empty() ? table_name : schema_name + "." + table_name;
125 std::stringstream ss;
126 std::string input = "SELECT * FROM " + name + " WHERE 1 = 0";
127 ParserQueryWithOutput parser;
128 ASTPtr select = parseQuery(parser, input.data(), input.data() + input.size(), "", 0);
129
130 IAST::FormatSettings settings(ss, true);
131 settings.always_quote_identifiers = true;
132
133 auto identifier_quote = getIdentifierQuote(hdbc);
134 if (identifier_quote.length() == 0)
135 settings.identifier_quoting_style = IdentifierQuotingStyle::None;
136 else if (identifier_quote[0] == '`')
137 settings.identifier_quoting_style = IdentifierQuotingStyle::Backticks;
138 else if (identifier_quote[0] == '"')
139 settings.identifier_quoting_style = IdentifierQuotingStyle::DoubleQuotes;
140 else
141 throw Exception("Can not map quote identifier '" + identifier_quote + "' to IdentifierQuotingStyle value", ErrorCodes::ILLEGAL_TYPE_OF_ARGUMENT);
142
143 select->format(settings);
144 std::string query = ss.str();
145
146 LOG_TRACE(log, "Inferring structure with query '" << query << "'");
147
148 if (POCO_SQL_ODBC_CLASS::Utility::isError(POCO_SQL_ODBC_CLASS::SQLPrepare(hstmt, reinterpret_cast<SQLCHAR *>(query.data()), query.size())))
149 throw POCO_SQL_ODBC_CLASS::DescriptorException(session.dbc());
150
151 if (POCO_SQL_ODBC_CLASS::Utility::isError(SQLExecute(hstmt)))
152 throw POCO_SQL_ODBC_CLASS::StatementException(hstmt);
153
154 SQLSMALLINT cols = 0;
155 if (POCO_SQL_ODBC_CLASS::Utility::isError(SQLNumResultCols(hstmt, &cols)))
156 throw POCO_SQL_ODBC_CLASS::StatementException(hstmt);
157
158 /// TODO cols not checked
159
160 NamesAndTypesList columns;
161 for (SQLSMALLINT ncol = 1; ncol <= cols; ++ncol)
162 {
163 SQLSMALLINT type = 0;
164 /// TODO Why 301?
165 SQLCHAR column_name[301];
166
167 SQLSMALLINT is_nullable;
168 const auto result = POCO_SQL_ODBC_CLASS::SQLDescribeCol(hstmt, ncol, column_name, sizeof(column_name), nullptr, &type, nullptr, nullptr, &is_nullable);
169 if (POCO_SQL_ODBC_CLASS::Utility::isError(result))
170 throw POCO_SQL_ODBC_CLASS::StatementException(hstmt);
171
172 auto column_type = getDataType(type);
173 if (external_table_functions_use_nulls && is_nullable == SQL_NULLABLE)
174 {
175 column_type = std::make_shared<DataTypeNullable>(column_type);
176 }
177
178 columns.emplace_back(reinterpret_cast<char *>(column_name), std::move(column_type));
179 }
180
181 WriteBufferFromHTTPServerResponse out(request, response, keep_alive_timeout);
182 writeStringBinary(columns.toString(), out);
183 }
184 catch (...)
185 {
186 process_error("Error getting columns from ODBC '" + getCurrentExceptionMessage(false) + "'");
187 tryLogCurrentException(log);
188 }
189}
190}
191#endif
192