How to: Insert Rows into Geography Column (ODBC)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
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 (10.0.x).
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 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();
}
use tempdb
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SpatialSample')
DROP TABLE SpatialSample
GO
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for