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; }