--- title: "How to: Insert Rows into Geography Column (ODBC) | Microsoft Docs" ms.custom: "" ms.date: "03/14/2017" ms.prod: sql ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.reviewer: "" ms.technology: native-client ms.topic: "reference" ms.assetid: 0b6516f7-1fc0-4b01-a2d0-add0571070d5 author: markingmyname ms.author: maghan monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current" --- # How to: Insert Rows into Geography Column (ODBC) [!INCLUDE[appliesto-ss-asdb-asdw-pdw-md](../../includes/appliesto-ss-asdb-asdw-pdw-md.md)] This sample inserts two rows into a table with a geography column from a WellKnownBinary (WKB) using 2 different bindings (SQLCCHAR and SQLCBINARY). Then it selects one row from that table and uses ::STAsText() to display it.The WKB is 0x01010000000700ECFAD03A4C4001008000B5DF07C0 and the application prints to the console: POINT(56.4595 -2.9842). This sample does not require an ODBC data source, but the sample runs, by default, on the local instance of SQL Server. This sample will not work with any version of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] earlier than [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)]. For more information about spatial storage, see [Spatial Data (SQL Server)](../../relational-databases/spatial/spatial-data-sql-server.md). ## Example The first ( [!INCLUDE[tsql](../../includes/tsql-md.md)]) code listing creates a table used by this sample. Compile the second (C++) code listing with odbc32.lib and user32.lib. Make sure your INCLUDE environment variable includes the directory that contains sqlncli.h. If you will build and run this sample as a 32-bit application on a 64-bit operating system, you must create the ODBC data source with the ODBC Administrator in %windir%\SysWOW64\odbcad32.exe. This sample connects to your computer's default [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance. To connect to a named instance, change the definition of the ODBC data source to specify the instance using the following format: server\namedinstance. By default, [!INCLUDE[ssExpress](../../includes/ssexpress-md.md)] installs to a named instance. The third ( [!INCLUDE[tsql](../../includes/tsql-md.md)]) code listing deletes the table used by this sample. ```sql use tempdb GO IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SpatialSample') DROP TABLE SpatialSample CREATE TABLE SpatialSample (Name varchar(10), Geog Geography) GO ``` ```cpp // compile with: odbc32.lib user32.lib #include #include #include #include "sqlncli.h" #include #include #define MAX_DATA 1024 #define MYSQLSUCCESS(rc) ( (rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO) ) SQLCHAR szDSN[] = "Driver={SQL Server Native Client 10.0};Server=.;Database=tempdb;Trusted_Connection=Yes;"; class direxec { RETCODE rc; // ODBC return code HENV henv; // Environment HDBC hdbc; // Connection Handle HSTMT hstmt; // Statement Handle SQLHDESC hdesc; // Descriptor handle SQLCHAR szData[MAX_DATA]; // Returned Data Storage SDWORD cbData; // Output Length of data SQLCHAR szConnStrOut[MAX_DATA + 1]; SWORD swStrLen; public: void sqlconn(); // Allocate env, stat and conn void sqldisconn(); // Free pointers to env, stat, conn and disconnect void error_out(); // Display errors void check_rc(RETCODE rc); // Checks for success of the return code void SqlInsertFromChar(); // Insert a WKB in character form void SqlInsertFromBinary(); // Insert a WKB in binary form void SqlSelectGeogAsText(); // Retrieve the geography as Text. }; // Allocate environment handles, connection handle, connect to data source, and allocate statement handle void direxec::sqlconn() { // Allocate the environment handle rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); check_rc(rc); // Set the ODBC version to version 3 rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER); check_rc(rc); // Allocate the database connection handle rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); check_rc(rc); // Connect to the database rc = SQLDriverConnect(hdbc, NULL, szDSN, SQL_NTS, szConnStrOut, MAX_DATA, &swStrLen, SQL_DRIVER_NOPROMPT); check_rc(rc); // Allocate the statement handle rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); check_rc(rc); // Allocate the descriptor handle rc = rc = SQLAllocHandle(SQL_HANDLE_DESC, hdbc, &hdesc); check_rc(rc); } // Display error message from the DiagRecord void direxec::error_out() { // String to hold the SQL State SQLCHAR szSQLSTATE[10]; // Error code SDWORD nErr; // The error message SQLCHAR msg[SQL_MAX_MESSAGE_LENGTH + 1]; // Size of the message SWORD cbmsg; // If hstmt is not null use that for getting the DiagRec if (hstmt) rc = SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, szSQLSTATE, &nErr, msg, sizeof(msg), &cbmsg); // else get the diag record from the env else rc = SQLGetDiagRec(SQL_HANDLE_ENV, henv, 1, szSQLSTATE, &nErr, msg, sizeof(msg), &cbmsg); // If the rc is successful, show the message using a message box if ( rc == SQL_SUCCESS) { printf((char *)szData, "Error:\nSQLSTATE=%s,Native error=%ld, msg='%s'", szSQLSTATE, nErr, msg); MessageBox(NULL, (const char *)szData, "ODBC Error", MB_OK); } } // Checks the return code. If failure, displays the error, free the memory and exits the program void direxec::check_rc(RETCODE rc) { if (!MYSQLSUCCESS(rc)) { error_out(); SQLFreeEnv(henv); SQLFreeConnect(hdbc); exit(-1); } } void direxec::SqlInsertFromBinary() { rc = SQLPrepare(hstmt, (SQLCHAR*) "INSERT INTO SpatialSample(Name,Geog) values('Sample1',Geography::STGeomFromWKB(?,4326))", SQL_NTS); check_rc(rc); SQLCHAR szBytes [] = "\x01\x01\x00\x00\x00\x07\x00\xEC\xFA\xD0\x3A\x4C\x40\x01\x00\x80\x00\xB5\xDF\x07\xC0"; SQLLEN iDataLength = sizeof(szBytes)-1; rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 100, 0, szBytes, sizeof(szBytes), &iDataLength); check_rc(rc); rc = SQLExecute(hstmt); check_rc(rc); } void direxec::SqlInsertFromChar() { rc = SQLPrepare(hstmt, (SQLCHAR*) "INSERT INTO SpatialSample(Name,Geog) values('Sample2',Geography::STGeomFromWKB(?,4326))", SQL_NTS); check_rc(rc); SQLCHAR szBytes [] = "01010000000700ECFAD03A4C4001008000B5DF07C0"; rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARBINARY, 100, 0, szBytes, sizeof(szBytes), NULL); check_rc(rc); rc = SQLExecute(hstmt); check_rc(rc); } void direxec::SqlSelectGeogAsText() { rc = SQLFreeStmt(hstmt, SQL_CLOSE); check_rc(rc); rc = SQLExecDirect(hstmt, (SQLCHAR*) "SELECT geog.STAsText() FROM SpatialSample", SQL_NTS); check_rc(rc); SQLCHAR rgcAsText[MAX_DATA]; SQLLEN cbAsText; rc = SQLBindCol(hstmt, 1, SQL_C_CHAR, rgcAsText, sizeof(rgcAsText), &cbAsText); check_rc(rc); rc = SQLFetch(hstmt); check_rc(rc); rgcAsText[cbAsText] = '\0'; printf("%s\r\n", (LPSTR)rgcAsText); } int main() { direxec x; // Allocate handles, and connect. x.sqlconn(); // Insert 2 samples into the table x.SqlInsertFromChar(); x.SqlInsertFromBinary(); // Select 1 row from the table and display the geography as text x.SqlSelectGeogAsText(); } ``` ```sql use tempdb GO IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SpatialSample') DROP TABLE SpatialSample GO ```