How to: Insert Rows into Geography Column (ODBC)

Article
04/01/2012

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 SQL Server earlier than SQL Server 2008.

For more information about spatial storage, see Spatial Data (SQL Server).

Example

The first (Transact-SQL) 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 SQL Server 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, SQL Server Express installs to a named instance.

The third (Transact-SQL) code listing deletes the table used by this sample.

use tempdb
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SpatialSample')
   DROP TABLE SpatialSample

CREATE TABLE SpatialSample (Name varchar(10), Geog Geography)
GO

// compile with: odbc32.lib user32.lib
#include <windows.h>
#include <Sqlext.h>
#include <mbstring.h>
#include "sqlncli.h"
#include <string.h>
#include <stdio.h>

#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 Lenght 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 enviroment 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();
}

use tempdb
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SpatialSample')
   DROP TABLE SpatialSample
GO

How to: Insert Rows into Geography Column (ODBC)

Example

Additional resources