OGRErr OGRMSSQLSpatialTableLayer::CreateFeature( OGRFeature *poFeature )
{
GetLayerDefn();
if( NULL == poFeature )
{
CPLError( CE_Failure, CPLE_AppDefined,
"NULL pointer to OGRFeature passed to CreateFeature()." );
return OGRERR_FAILURE;
}
ClearStatement();
CPLODBCStatement oStatement( poDS->GetSession() );
/* the fid values are retieved from the source layer */
if( poFeature->GetFID() != OGRNullFID && pszFIDColumn != NULL )
oStatement.Appendf("SET IDENTITY_INSERT [%s].[%s] ON;", pszSchemaName, pszTableName );
/* -------------------------------------------------------------------- */
/* Form the INSERT command. */
/* -------------------------------------------------------------------- */
oStatement.Appendf( "INSERT INTO [%s].[%s] (", pszSchemaName, pszTableName );
OGRMSSQLGeometryValidator oValidator(poFeature->GetGeometryRef());
OGRGeometry *poGeom = oValidator.GetValidGeometryRef();
if (poFeature->GetGeometryRef() != poGeom)
{
CPLError( CE_Warning, CPLE_NotSupported,
"Geometry with FID = %ld has been modified.", poFeature->GetFID() );
}
int bNeedComma = FALSE;
if (poGeom != NULL && pszGeomColumn != NULL)
{
oStatement.Append( pszGeomColumn );
bNeedComma = TRUE;
}
if( poFeature->GetFID() != OGRNullFID && pszFIDColumn != NULL )
{
if (bNeedComma)
oStatement.Appendf( ", [%s]", pszFIDColumn );
else
{
oStatement.Appendf( "[%s]", pszFIDColumn );
bNeedComma = TRUE;
}
}
int nFieldCount = poFeatureDefn->GetFieldCount();
int i;
for( i = 0; i < nFieldCount; i++ )
{
if( !poFeature->IsFieldSet( i ) )
continue;
if (bNeedComma)
oStatement.Appendf( ", [%s]", poFeatureDefn->GetFieldDefn(i)->GetNameRef() );
else
{
oStatement.Appendf( "[%s]", poFeatureDefn->GetFieldDefn(i)->GetNameRef() );
bNeedComma = TRUE;
}
}
oStatement.Appendf( ") VALUES (" );
/* Set the geometry */
bNeedComma = FALSE;
if(poGeom != NULL && pszGeomColumn != NULL)
{
char *pszWKT = NULL;
//poGeom->setCoordinateDimension( nCoordDimension );
poGeom->exportToWkt( &pszWKT );
if( pszWKT != NULL && (nGeomColumnType == MSSQLCOLTYPE_GEOMETRY
|| nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY))
{
if (nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY)
{
oStatement.Append( "geography::STGeomFromText(" );
OGRMSSQLAppendEscaped(&oStatement, pszWKT);
oStatement.Appendf(",%d)", nSRSId );
}
else
{
oStatement.Append( "geometry::STGeomFromText(" );
OGRMSSQLAppendEscaped(&oStatement, pszWKT);
oStatement.Appendf(",%d).MakeValid()", nSRSId );
}
}
else
oStatement.Append( "null" );
bNeedComma = TRUE;
CPLFree(pszWKT);
}
/* Set the FID */
if( poFeature->GetFID() != OGRNullFID && pszFIDColumn != NULL )
{
if (bNeedComma)
oStatement.Appendf( ", %ld", poFeature->GetFID() );
else
{
oStatement.Appendf( "%ld", poFeature->GetFID() );
bNeedComma = TRUE;
}
}
for( i = 0; i < nFieldCount; i++ )
{
if( !poFeature->IsFieldSet( i ) )
continue;
if (bNeedComma)
oStatement.Append( ", " );
else
bNeedComma = TRUE;
AppendFieldValue(&oStatement, poFeature, i);
}
oStatement.Append( ");" );
if( poFeature->GetFID() != OGRNullFID && pszFIDColumn != NULL )
oStatement.Appendf("SET IDENTITY_INSERT [%s].[%s] OFF;", pszSchemaName, pszTableName );
/* -------------------------------------------------------------------- */
/* Execute the insert. */
/* -------------------------------------------------------------------- */
if( !oStatement.ExecuteSQL() )
{
CPLError( CE_Failure, CPLE_AppDefined,
"INSERT command for new feature failed. %s",
poDS->GetSession()->GetLastError() );
return OGRERR_FAILURE;
}
return OGRERR_NONE;
}
void OGRMSSQLSpatialTableLayer::AppendFieldValue(CPLODBCStatement *poStatement,
OGRFeature* poFeature, int i)
{
int nOGRFieldType = poFeatureDefn->GetFieldDefn(i)->GetType();
// We need special formatting for integer list values.
if( nOGRFieldType == OFTIntegerList )
{
//TODO
poStatement->Append( "null" );
return;
}
// We need special formatting for real list values.
else if( nOGRFieldType == OFTRealList )
{
//TODO
poStatement->Append( "null" );
return;
}
// We need special formatting for string list values.
else if( nOGRFieldType == OFTStringList )
{
//TODO
poStatement->Append( "null" );
return;
}
// Binary formatting
if( nOGRFieldType == OFTBinary )
{
int nLen = 0;
GByte* pabyData = poFeature->GetFieldAsBinary( i, &nLen );
char* pszBytes = GByteArrayToHexString( pabyData, nLen);
poStatement->Append( pszBytes );
CPLFree(pszBytes);
return;
}
// Flag indicating NULL or not-a-date date value
// e.g. 0000-00-00 - there is no year 0
OGRBoolean bIsDateNull = FALSE;
const char *pszStrValue = poFeature->GetFieldAsString(i);
// Check if date is NULL: 0000-00-00
if( nOGRFieldType == OFTDate )
{
if( EQUALN( pszStrValue, "0000", 4 ) )
{
pszStrValue = "null";
bIsDateNull = TRUE;
}
}
else if ( nOGRFieldType == OFTReal )
{
char* pszComma = strchr((char*)pszStrValue, ',');
if (pszComma)
*pszComma = '.';
}
if( nOGRFieldType != OFTInteger && nOGRFieldType != OFTReal
&& !bIsDateNull )
{
OGRMSSQLAppendEscaped(poStatement, pszStrValue);
}
else
{
poStatement->Append( pszStrValue );
}
}
OGRErr OGRMSSQLSpatialTableLayer::SetFeature( OGRFeature *poFeature )
{
OGRErr eErr = OGRERR_FAILURE;
GetLayerDefn();
if( NULL == poFeature )
{
CPLError( CE_Failure, CPLE_AppDefined,
"NULL pointer to OGRFeature passed to SetFeature()." );
return eErr;
}
if( poFeature->GetFID() == OGRNullFID )
{
CPLError( CE_Failure, CPLE_AppDefined,
"FID required on features given to SetFeature()." );
return eErr;
}
if( !pszFIDColumn )
{
CPLError( CE_Failure, CPLE_AppDefined,
"Unable to update features in tables without\n"
"a recognised FID column.");
return eErr;
}
ClearStatement();
/* -------------------------------------------------------------------- */
/* Form the UPDATE command. */
/* -------------------------------------------------------------------- */
CPLODBCStatement oStmt( poDS->GetSession() );
oStmt.Appendf( "UPDATE [%s].[%s] SET ", pszSchemaName, pszTableName);
OGRMSSQLGeometryValidator oValidator(poFeature->GetGeometryRef());
OGRGeometry *poGeom = oValidator.GetValidGeometryRef();
if (poFeature->GetGeometryRef() != poGeom)
{
CPLError( CE_Warning, CPLE_NotSupported,
"Geometry with FID = %ld has been modified.", poFeature->GetFID() );
}
int bNeedComma = FALSE;
if(pszGeomColumn != NULL)
{
char *pszWKT = NULL;
if (poGeom != NULL)
poGeom->exportToWkt( &pszWKT );
oStmt.Appendf( "[%s] = ", pszGeomColumn );
if( pszWKT != NULL && (nGeomColumnType == MSSQLCOLTYPE_GEOMETRY
|| nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY))
{
if (nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY)
{
oStmt.Append( "geography::STGeomFromText(" );
OGRMSSQLAppendEscaped(&oStmt, pszWKT);
oStmt.Appendf(",%d)", nSRSId );
}
else
{
oStmt.Append( "geometry::STGeomFromText(" );
OGRMSSQLAppendEscaped(&oStmt, pszWKT);
oStmt.Appendf(",%d).MakeValid()", nSRSId );
}
}
else
oStmt.Append( "null" );
bNeedComma = TRUE;
CPLFree(pszWKT);
}
int nFieldCount = poFeatureDefn->GetFieldCount();
int i;
for( i = 0; i < nFieldCount; i++ )
{
if (bNeedComma)
oStmt.Appendf( ", [%s] = ", poFeatureDefn->GetFieldDefn(i)->GetNameRef() );
else
{
oStmt.Appendf( "[%s] = ", poFeatureDefn->GetFieldDefn(i)->GetNameRef() );
bNeedComma = TRUE;
}
if( !poFeature->IsFieldSet( i ) )
oStmt.Append( "null" );
else
AppendFieldValue(&oStmt, poFeature, i);
}
/* Add the WHERE clause */
oStmt.Appendf( " WHERE [%s] = %ld" , pszFIDColumn, poFeature->GetFID());
/* -------------------------------------------------------------------- */
/* Execute the update. */
/* -------------------------------------------------------------------- */
if( !oStmt.ExecuteSQL() )
{
CPLError( CE_Failure, CPLE_AppDefined,
"Error updating feature with FID:%ld, %s", poFeature->GetFID(),
poDS->GetSession()->GetLastError() );
return OGRERR_FAILURE;
}
return OGRERR_NONE;
}
int OGRMSSQLSpatialDataSource::FetchSRSId( OGRSpatialReference * poSRS)
{
char *pszWKT = NULL;
int nSRSId = 0;
const char* pszAuthorityName;
if( poSRS == NULL )
return 0;
OGRSpatialReference oSRS(*poSRS);
poSRS = NULL;
pszAuthorityName = oSRS.GetAuthorityName(NULL);
if( pszAuthorityName == NULL || strlen(pszAuthorityName) == 0 )
{
/* -------------------------------------------------------------------- */
/* Try to identify an EPSG code */
/* -------------------------------------------------------------------- */
oSRS.AutoIdentifyEPSG();
pszAuthorityName = oSRS.GetAuthorityName(NULL);
if (pszAuthorityName != NULL && EQUAL(pszAuthorityName, "EPSG"))
{
const char* pszAuthorityCode = oSRS.GetAuthorityCode(NULL);
if ( pszAuthorityCode != NULL && strlen(pszAuthorityCode) > 0 )
{
/* Import 'clean' SRS */
oSRS.importFromEPSG( atoi(pszAuthorityCode) );
pszAuthorityName = oSRS.GetAuthorityName(NULL);
}
}
}
/* -------------------------------------------------------------------- */
/* Check whether the EPSG authority code is already mapped to a */
/* SRS ID. */
/* -------------------------------------------------------------------- */
int nAuthorityCode = 0;
if( pszAuthorityName != NULL && EQUAL( pszAuthorityName, "EPSG" ) )
{
/* For the root authority name 'EPSG', the authority code
* should always be integral
*/
nAuthorityCode = atoi( oSRS.GetAuthorityCode(NULL) );
CPLODBCStatement oStmt( &oSession );
oStmt.Appendf("SELECT srid FROM spatial_ref_sys WHERE "
"auth_name = '%s' AND auth_srid = %d",
pszAuthorityName,
nAuthorityCode );
if( oStmt.ExecuteSQL() && oStmt.Fetch() && oStmt.GetColData( 0 ) )
{
nSRSId = atoi(oStmt.GetColData( 0 ));
return nSRSId;
}
}
/* -------------------------------------------------------------------- */
/* Translate SRS to WKT. */
/* -------------------------------------------------------------------- */
if( oSRS.exportToWkt( &pszWKT ) != OGRERR_NONE )
{
CPLFree(pszWKT);
return 0;
}
/* -------------------------------------------------------------------- */
/* Try to find in the existing table. */
/* -------------------------------------------------------------------- */
CPLODBCStatement oStmt( &oSession );
oStmt.Append( "SELECT srid FROM spatial_ref_sys WHERE srtext = ");
OGRMSSQLAppendEscaped(&oStmt, pszWKT);
/* -------------------------------------------------------------------- */
/* We got it! Return it. */
/* -------------------------------------------------------------------- */
if( oStmt.ExecuteSQL() )
{
if ( oStmt.Fetch() && oStmt.GetColData( 0 ) )
{
nSRSId = atoi(oStmt.GetColData( 0 ));
CPLFree(pszWKT);
return nSRSId;
}
}
else
{
/* probably the table is missing at all */
if( InitializeMetadataTables() != OGRERR_NONE )
{
CPLFree(pszWKT);
return 0;
}
}
/* -------------------------------------------------------------------- */
/* Try adding the SRS to the SRS table. */
/* -------------------------------------------------------------------- */
char *pszProj4 = NULL;
if( oSRS.exportToProj4( &pszProj4 ) != OGRERR_NONE )
{
CPLFree( pszProj4 );
CPLFree(pszWKT);
return 0;
}
/* -------------------------------------------------------------------- */
/* Check whether the auth_code can be used as srid. */
/* -------------------------------------------------------------------- */
nSRSId = nAuthorityCode;
oStmt.Clear();
oSession.BeginTransaction();
if (nAuthorityCode > 0)
{
oStmt.Appendf("SELECT srid FROM spatial_ref_sys where srid = %d", nAuthorityCode);
if ( oStmt.ExecuteSQL() && oStmt.Fetch())
{
nSRSId = 0;
}
}
/* -------------------------------------------------------------------- */
/* Get the current maximum srid in the srs table. */
/* -------------------------------------------------------------------- */
if (nSRSId == 0)
{
oStmt.Clear();
oStmt.Append("SELECT COALESCE(MAX(srid) + 1, 32768) FROM spatial_ref_sys where srid between 32768 and 65536");
if ( oStmt.ExecuteSQL() && oStmt.Fetch() && oStmt.GetColData( 0 ) )
{
nSRSId = atoi(oStmt.GetColData( 0 ));
}
}
if (nSRSId == 0)
{
/* unable to allocate srid */
oSession.RollbackTransaction();
CPLFree( pszProj4 );
CPLFree(pszWKT);
return 0;
}
oStmt.Clear();
if( nAuthorityCode > 0 )
{
oStmt.Appendf(
"INSERT INTO spatial_ref_sys (srid, auth_srid, auth_name, srtext, proj4text) "
"VALUES (%d, %d, ", nSRSId, nAuthorityCode );
OGRMSSQLAppendEscaped(&oStmt, pszAuthorityName);
oStmt.Append(", ");
OGRMSSQLAppendEscaped(&oStmt, pszWKT);
oStmt.Append(", ");
OGRMSSQLAppendEscaped(&oStmt, pszProj4);
oStmt.Append(")");
}
else
{
oStmt.Appendf(
"INSERT INTO spatial_ref_sys (srid,srtext,proj4text) VALUES (%d, ", nSRSId);
OGRMSSQLAppendEscaped(&oStmt, pszWKT);
oStmt.Append(", ");
OGRMSSQLAppendEscaped(&oStmt, pszProj4);
oStmt.Append(")");
}
/* Free everything that was allocated. */
CPLFree( pszProj4 );
CPLFree( pszWKT);
if ( oStmt.ExecuteSQL() )
oSession.CommitTransaction();
else
oSession.RollbackTransaction();
return nSRSId;
}
