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;
}
OGRLayer *
OGRMySQLDataSource::CreateLayer( const char * pszLayerNameIn,
OGRSpatialReference *poSRS,
OGRwkbGeometryType eType,
char ** papszOptions )
{
MYSQL_RES *hResult=NULL;
CPLString osCommand;
const char *pszGeometryType;
const char *pszGeomColumnName;
const char *pszExpectedFIDName;
char *pszLayerName;
int nDimension = 3; // MySQL only supports 2d currently
/* -------------------------------------------------------------------- */
/* Make sure there isn't an active transaction already. */
/* -------------------------------------------------------------------- */
InterruptLongResult();
if( CSLFetchBoolean(papszOptions,"LAUNDER",TRUE) )
pszLayerName = LaunderName( pszLayerNameIn );
else
pszLayerName = CPLStrdup( pszLayerNameIn );
if( wkbFlatten(eType) == eType )
nDimension = 2;
CPLDebug("MYSQL","Creating layer %s.", pszLayerName);
/* -------------------------------------------------------------------- */
/* Do we already have this layer? If so, should we blow it */
/* away? */
/* -------------------------------------------------------------------- */
int iLayer;
for( iLayer = 0; iLayer < nLayers; iLayer++ )
{
if( EQUAL(pszLayerName,papoLayers[iLayer]->GetLayerDefn()->GetName()) )
{
if( CSLFetchNameValue( papszOptions, "OVERWRITE" ) != NULL
&& !EQUAL(CSLFetchNameValue(papszOptions,"OVERWRITE"),"NO") )
{
DeleteLayer( iLayer );
}
else
{
CPLError( CE_Failure, CPLE_AppDefined,
"Layer %s already exists, CreateLayer failed.\n"
"Use the layer creation option OVERWRITE=YES to "
"replace it.",
pszLayerName );
CPLFree( pszLayerName );
return NULL;
}
}
}
pszGeomColumnName = CSLFetchNameValue( papszOptions, "GEOMETRY_NAME" );
if (!pszGeomColumnName)
pszGeomColumnName="SHAPE";
pszExpectedFIDName = CSLFetchNameValue( papszOptions, "MYSQL_FID" );
if (!pszExpectedFIDName)
pszExpectedFIDName="OGR_FID";
CPLDebug("MYSQL","Geometry Column Name %s.", pszGeomColumnName);
CPLDebug("MYSQL","FID Column Name %s.", pszExpectedFIDName);
if( wkbFlatten(eType) == wkbNone )
{
osCommand.Printf(
"CREATE TABLE `%s` ( "
" %s INT UNIQUE NOT NULL AUTO_INCREMENT )",
pszLayerName, pszExpectedFIDName );
}
else
{
osCommand.Printf(
"CREATE TABLE `%s` ( "
" %s INT UNIQUE NOT NULL AUTO_INCREMENT, "
" %s GEOMETRY NOT NULL )",
pszLayerName, pszExpectedFIDName, pszGeomColumnName );
}
if( CSLFetchNameValue( papszOptions, "ENGINE" ) != NULL )
{
osCommand += " ENGINE = ";
osCommand += CSLFetchNameValue( papszOptions, "ENGINE" );
}
if( !mysql_query(GetConn(), osCommand ) )
{
if( mysql_field_count( GetConn() ) == 0 )
CPLDebug("MYSQL","Created table %s.", pszLayerName);
else
{
ReportError( osCommand );
return NULL;
}
}
else
{
ReportError( osCommand );
return NULL;
}
// make sure to attempt to free results of successful queries
hResult = mysql_store_result( GetConn() );
if( hResult != NULL )
mysql_free_result( hResult );
hResult = NULL;
// Calling this does no harm
InitializeMetadataTables();
/* -------------------------------------------------------------------- */
/* Try to get the SRS Id of this spatial reference system, */
/* adding tot the srs table if needed. */
/* -------------------------------------------------------------------- */
int nSRSId = -1;
if( poSRS != NULL )
nSRSId = FetchSRSId( poSRS );
/* -------------------------------------------------------------------- */
/* Sometimes there is an old crufty entry in the geometry_columns */
/* table if things were not properly cleaned up before. We make */
/* an effort to clean out such cruft. */
/* */
/* -------------------------------------------------------------------- */
osCommand.Printf(
"DELETE FROM geometry_columns WHERE f_table_name = '%s'",
pszLayerName );
if( mysql_query(GetConn(), osCommand ) )
{
ReportError( osCommand );
return NULL;
}
// make sure to attempt to free results of successful queries
hResult = mysql_store_result( GetConn() );
if( hResult != NULL )
mysql_free_result( hResult );
hResult = NULL;
/* -------------------------------------------------------------------- */
/* Attempt to add this table to the geometry_columns table, if */
/* it is a spatial layer. */
/* -------------------------------------------------------------------- */
if( eType != wkbNone )
{
int nCoordDimension;
if( eType == wkbFlatten(eType) )
nCoordDimension = 2;
else
nCoordDimension = 3;
pszGeometryType = OGRToOGCGeomType(eType);
if( nSRSId == -1 )
osCommand.Printf(
"INSERT INTO geometry_columns "
" (F_TABLE_NAME, "
" F_GEOMETRY_COLUMN, "
" COORD_DIMENSION, "
" TYPE) values "
" ('%s', '%s', %d, '%s')",
pszLayerName,
pszGeomColumnName,
nCoordDimension,
pszGeometryType );
else
osCommand.Printf(
"INSERT INTO geometry_columns "
" (F_TABLE_NAME, "
" F_GEOMETRY_COLUMN, "
" COORD_DIMENSION, "
" SRID, "
" TYPE) values "
" ('%s', '%s', %d, %d, '%s')",
pszLayerName,
pszGeomColumnName,
nCoordDimension,
nSRSId,
pszGeometryType );
if( mysql_query(GetConn(), osCommand ) )
{
ReportError( osCommand );
return NULL;
}
// make sure to attempt to free results of successful queries
hResult = mysql_store_result( GetConn() );
if( hResult != NULL )
mysql_free_result( hResult );
hResult = NULL;
}
/* -------------------------------------------------------------------- */
/* Create the spatial index. */
/* */
/* We're doing this before we add geometry and record to the table */
/* so this may not be exactly the best way to do it. */
/* -------------------------------------------------------------------- */
const char *pszSI = CSLFetchNameValue( papszOptions, "SPATIAL_INDEX" );
if( eType != wkbNone && (pszSI == NULL || CSLTestBoolean(pszSI)) )
{
osCommand.Printf(
"ALTER TABLE `%s` ADD SPATIAL INDEX(`%s`) ",
pszLayerName,
pszGeomColumnName);
if( mysql_query(GetConn(), osCommand ) )
{
ReportError( osCommand );
return NULL;
}
// make sure to attempt to free results of successful queries
hResult = mysql_store_result( GetConn() );
if( hResult != NULL )
mysql_free_result( hResult );
hResult = NULL;
}
/* -------------------------------------------------------------------- */
/* Create the layer object. */
/* -------------------------------------------------------------------- */
OGRMySQLTableLayer *poLayer;
OGRErr eErr;
poLayer = new OGRMySQLTableLayer( this, pszLayerName, TRUE, nSRSId );
eErr = poLayer->Initialize(pszLayerName);
if (eErr == OGRERR_FAILURE)
return NULL;
poLayer->SetLaunderFlag( CSLFetchBoolean(papszOptions,"LAUNDER",TRUE) );
poLayer->SetPrecisionFlag( CSLFetchBoolean(papszOptions,"PRECISION",TRUE));
/* -------------------------------------------------------------------- */
/* Add layer to data source layer list. */
/* -------------------------------------------------------------------- */
papoLayers = (OGRMySQLLayer **)
CPLRealloc( papoLayers, sizeof(OGRMySQLLayer *) * (nLayers+1) );
papoLayers[nLayers++] = poLayer;
CPLFree( pszLayerName );
return poLayer;
}
OGRLayer * OGRMSSQLSpatialDataSource::CreateLayer( const char * pszLayerName,
OGRSpatialReference *poSRS,
OGRwkbGeometryType eType,
char ** papszOptions )
{
char *pszTableName = NULL;
char *pszSchemaName = NULL;
const char *pszGeomType = NULL;
const char *pszGeomColumn = NULL;
int nCoordDimension = 3;
/* determine the dimension */
if( eType == wkbFlatten(eType) )
nCoordDimension = 2;
if( CSLFetchNameValue( papszOptions, "DIM") != NULL )
nCoordDimension = atoi(CSLFetchNameValue( papszOptions, "DIM"));
/* MSSQL Schema handling:
Extract schema name from input layer name or passed with -lco SCHEMA.
Set layer name to "schema.table" or to "table" if schema is not
specified
*/
const char* pszDotPos = strstr(pszLayerName,".");
if ( pszDotPos != NULL )
{
int length = pszDotPos - pszLayerName;
pszSchemaName = (char*)CPLMalloc(length+1);
strncpy(pszSchemaName, pszLayerName, length);
pszSchemaName[length] = '\0';
if( CSLFetchBoolean(papszOptions,"LAUNDER", TRUE) )
pszTableName = LaunderName( pszDotPos + 1 ); //skip "."
else
pszTableName = CPLStrdup( pszDotPos + 1 ); //skip "."
}
else
{
pszSchemaName = NULL;
if( CSLFetchBoolean(papszOptions,"LAUNDER", TRUE) )
pszTableName = LaunderName( pszLayerName ); //skip "."
else
pszTableName = CPLStrdup( pszLayerName ); //skip "."
}
if( CSLFetchNameValue( papszOptions, "SCHEMA" ) != NULL )
{
CPLFree(pszSchemaName);
pszSchemaName = CPLStrdup(CSLFetchNameValue( papszOptions, "SCHEMA" ));
}
if (pszSchemaName == NULL)
pszSchemaName = CPLStrdup("dbo");
/* -------------------------------------------------------------------- */
/* Do we already have this layer? If so, should we blow it */
/* away? */
/* -------------------------------------------------------------------- */
int iLayer;
for( iLayer = 0; iLayer < nLayers; iLayer++ )
{
if( EQUAL(pszLayerName,papoLayers[iLayer]->GetTableName()) )
{
if( CSLFetchNameValue( papszOptions, "OVERWRITE" ) != NULL
&& !EQUAL(CSLFetchNameValue(papszOptions,"OVERWRITE"),"NO") )
{
if (!pszSchemaName)
pszSchemaName = CPLStrdup(papoLayers[iLayer]->GetSchemaName());
DeleteLayer( iLayer );
}
else
{
CPLError( CE_Failure, CPLE_AppDefined,
"Layer %s already exists, CreateLayer failed.\n"
"Use the layer creation option OVERWRITE=YES to "
"replace it.",
pszLayerName );
CPLFree( pszSchemaName );
CPLFree( pszTableName );
return NULL;
}
}
}
/* -------------------------------------------------------------------- */
/* Handle the GEOM_TYPE option. */
/* -------------------------------------------------------------------- */
pszGeomType = CSLFetchNameValue( papszOptions, "GEOM_TYPE" );
if( !pszGeomType )
pszGeomType = "geometry";
if( !EQUAL(pszGeomType, "geometry")
&& !EQUAL(pszGeomType, "geography"))
{
CPLError( CE_Failure, CPLE_AppDefined,
"FORMAT=%s not recognised or supported.",
pszGeomType );
CPLFree( pszSchemaName );
CPLFree( pszTableName );
return NULL;
}
/* determine the geometry column name */
pszGeomColumn = CSLFetchNameValue( papszOptions, "GEOM_NAME");
if (!pszGeomColumn)
pszGeomColumn = "ogr_geometry";
/* -------------------------------------------------------------------- */
/* Initialize the metadata tables */
/* -------------------------------------------------------------------- */
if (InitializeMetadataTables() != OGRERR_NONE)
{
CPLFree( pszSchemaName );
CPLFree( pszTableName );
return NULL;
}
/* -------------------------------------------------------------------- */
/* Try to get the SRS Id of this spatial reference system, */
/* adding to the srs table if needed. */
/* -------------------------------------------------------------------- */
int nSRSId = 0;
if( CSLFetchNameValue( papszOptions, "SRID") != NULL )
nSRSId = atoi(CSLFetchNameValue( papszOptions, "SRID"));
if( nSRSId == 0 && poSRS != NULL )
nSRSId = FetchSRSId( poSRS );
/* -------------------------------------------------------------------- */
/* Create a new table and create a new entry in the geometry, */
/* geometry_columns metadata table. */
/* -------------------------------------------------------------------- */
if( eType != wkbNone )
{
const char *pszGeometryType = OGRToOGCGeomType(eType);
CPLODBCStatement oStmt( &oSession );
oStmt.Appendf( "DELETE FROM geometry_columns WHERE f_table_schema = '%s' "
"AND f_table_name = '%s'\n", pszSchemaName, pszTableName );
oStmt.Appendf("INSERT INTO [geometry_columns] ([f_table_catalog], [f_table_schema] ,[f_table_name], "
"[f_geometry_column],[coord_dimension],[srid],[geometry_type]) VALUES ('%s', '%s', '%s', '%s', %d, %d, '%s')\n",
pszCatalog, pszSchemaName, pszTableName, pszGeomColumn, nCoordDimension, nSRSId, pszGeometryType );
oStmt.Appendf("CREATE TABLE [%s].[%s] ([ogr_fid] [int] IDENTITY(1,1) NOT NULL, "
"[%s] [%s] NULL, CONSTRAINT [PK_%s] PRIMARY KEY CLUSTERED ([ogr_fid] ASC))",
pszSchemaName, pszTableName, pszGeomColumn, pszGeomType, pszTableName);
oSession.BeginTransaction();
if( !oStmt.ExecuteSQL() )
{
CPLError( CE_Failure, CPLE_AppDefined,
"Error creating layer: %s", GetSession()->GetLastError() );
return NULL;
}
oSession.CommitTransaction();
}
CPLFree( pszSchemaName );
CPLFree( pszTableName );
/* -------------------------------------------------------------------- */
/* Create the layer object. */
/* -------------------------------------------------------------------- */
OGRMSSQLSpatialTableLayer *poLayer;
poLayer = new OGRMSSQLSpatialTableLayer( this );
poLayer->SetLaunderFlag( CSLFetchBoolean(papszOptions,"LAUNDER",TRUE) );
poLayer->SetPrecisionFlag( CSLFetchBoolean(papszOptions,"PRECISION",TRUE));
if (poLayer->Initialize("dbo", pszLayerName, pszGeomColumn, nCoordDimension, nSRSId, eType) == OGRERR_FAILURE)
{
return NULL;
}
/* -------------------------------------------------------------------- */
/* Add layer to data source layer list. */
/* -------------------------------------------------------------------- */
papoLayers = (OGRMSSQLSpatialTableLayer **)
CPLRealloc( papoLayers, sizeof(OGRMSSQLSpatialTableLayer *) * (nLayers+1) );
papoLayers[nLayers++] = poLayer;
return poLayer;
}
int OGRMSSQLSpatialDataSource::Open( const char * pszNewName, int bUpdate,
int bTestOpen )
{
CPLAssert( nLayers == 0 );
if( !EQUALN(pszNewName,"MSSQL:",6) )
{
if( !bTestOpen )
CPLError( CE_Failure, CPLE_AppDefined,
"%s does not conform to MSSSQLSpatial naming convention,"
" MSSQL:*\n", pszNewName );
return FALSE;
}
/* Determine if the connection string contains specific values */
char* pszTableSpec = NULL;
char* pszGeometryFormat = NULL;
char* pszConnectionName = CPLStrdup(pszNewName + 6);
char* pszDriver = NULL;
int nCurrent, nNext, nTerm;
nCurrent = nNext = nTerm = strlen(pszConnectionName);
while (nCurrent > 0)
{
--nCurrent;
if (pszConnectionName[nCurrent] == ';')
{
nNext = nCurrent;
continue;
}
if (ParseValue(&pszCatalog, pszConnectionName, "database=",
nCurrent, nNext, nTerm, FALSE))
continue;
if (ParseValue(&pszTableSpec, pszConnectionName, "tables=",
nCurrent, nNext, nTerm, TRUE))
continue;
if (ParseValue(&pszDriver, pszConnectionName, "driver=",
nCurrent, nNext, nTerm, FALSE))
continue;
if (ParseValue(&pszGeometryFormat, pszConnectionName,
"geometryformat=", nCurrent, nNext, nTerm, TRUE))
{
if (EQUALN(pszGeometryFormat,"wkbzm",5))
nGeometryFormat = MSSQLGEOMETRY_WKBZM;
else if (EQUALN(pszGeometryFormat, "wkb",3))
nGeometryFormat = MSSQLGEOMETRY_WKB;
else if (EQUALN(pszGeometryFormat,"wkt",3))
nGeometryFormat = MSSQLGEOMETRY_WKT;
else if (EQUALN(pszGeometryFormat,"native",6))
nGeometryFormat = MSSQLGEOMETRY_NATIVE;
else
{
CPLError( CE_Failure, CPLE_AppDefined,
"Invalid geometry type specified: %s,"
" MSSQL:*\n", pszGeometryFormat );
CPLFree(pszTableSpec);
CPLFree(pszGeometryFormat);
CPLFree(pszConnectionName);
CPLFree(pszDriver);
return FALSE;
}
CPLFree(pszGeometryFormat);
pszGeometryFormat = NULL;
continue;
}
}
/* Determine if the connection string contains the catalog portion */
if( pszCatalog == NULL )
{
CPLError( CE_Failure, CPLE_AppDefined,
"'%s' does not contain the 'database' portion\n", pszNewName );
CPLFree(pszTableSpec);
CPLFree(pszGeometryFormat);
CPLFree(pszConnectionName);
CPLFree(pszDriver);
return FALSE;
}
pszName = CPLStrdup(pszNewName);
char **papszTableNames=NULL;
char **papszSchemaNames=NULL;
char **papszGeomColumnNames=NULL;
char **papszCoordDimensions=NULL;
char **papszSRIds=NULL;
char **papszSRTexts=NULL;
/* Determine if the connection string contains the TABLES portion */
if( pszTableSpec != NULL )
{
char **papszTableList;
int i;
papszTableList = CSLTokenizeString2( pszTableSpec, ",", 0 );
for( i = 0; i < CSLCount(papszTableList); i++ )
{
char **papszQualifiedParts;
// Get schema and table name
papszQualifiedParts = CSLTokenizeString2( papszTableList[i],
".", 0 );
/* Find the geometry column name if specified */
if( CSLCount( papszQualifiedParts ) >= 1 )
{
char* pszGeomColumnName = NULL;
char* pos = strchr(papszQualifiedParts[CSLCount( papszQualifiedParts ) - 1], '(');
if (pos != NULL)
{
*pos = '\0';
pszGeomColumnName = pos+1;
int len = strlen(pszGeomColumnName);
if (len > 0)
pszGeomColumnName[len - 1] = '\0';
}
papszGeomColumnNames = CSLAddString( papszGeomColumnNames,
pszGeomColumnName ? pszGeomColumnName : "");
}
if( CSLCount( papszQualifiedParts ) == 2 )
{
papszSchemaNames = CSLAddString( papszSchemaNames,
papszQualifiedParts[0] );
papszTableNames = CSLAddString( papszTableNames,
papszQualifiedParts[1] );
}
else if( CSLCount( papszQualifiedParts ) == 1 )
{
papszSchemaNames = CSLAddString( papszSchemaNames, "dbo");
papszTableNames = CSLAddString( papszTableNames,
papszQualifiedParts[0] );
}
CSLDestroy(papszQualifiedParts);
}
CSLDestroy(papszTableList);
}
CPLFree(pszTableSpec);
/* Initialize the SQL Server connection. */
int nResult;
if ( pszDriver != NULL )
{
/* driver has been specified */
CPLDebug( "OGR_MSSQLSpatial", "EstablishSession(Connection:\"%s\")", pszConnectionName);
nResult = oSession.EstablishSession( pszConnectionName, "", "" );
}
else
{
/* no driver has been specified, defautls to SQL Server */
CPLDebug( "OGR_MSSQLSpatial", "EstablishSession(Connection:\"%s\")", pszConnectionName);
nResult = oSession.EstablishSession( CPLSPrintf("DRIVER=SQL Server;%s", pszConnectionName), "", "" );
}
CPLFree(pszDriver);
if( !nResult )
{
CPLError( CE_Failure, CPLE_AppDefined,
"Unable to initialize connection to the server for %s,\n"
"%s", pszNewName, oSession.GetLastError() );
CSLDestroy( papszTableNames );
CSLDestroy( papszSchemaNames );
CSLDestroy( papszGeomColumnNames );
CSLDestroy( papszCoordDimensions );
CSLDestroy( papszSRIds );
CSLDestroy( papszSRTexts );
CPLFree(pszGeometryFormat);
CPLFree(pszConnectionName);
return FALSE;
}
char** papszTypes = NULL;
/* read metadata for the specified tables */
if (papszTableNames != NULL && bUseGeometryColumns)
{
for( int iTable = 0;
papszTableNames != NULL && papszTableNames[iTable] != NULL;
iTable++ )
{
CPLODBCStatement oStmt( &oSession );
/* Use join to make sure the existence of the referred column/table */
oStmt.Appendf( "SELECT f_geometry_column, coord_dimension, g.srid, srtext, geometry_type FROM dbo.geometry_columns g JOIN INFORMATION_SCHEMA.COLUMNS ON f_table_schema = TABLE_SCHEMA and f_table_name = TABLE_NAME and f_geometry_column = COLUMN_NAME left outer join dbo.spatial_ref_sys s on g.srid = s.srid WHERE f_table_schema = '%s' AND f_table_name = '%s'", papszSchemaNames[iTable], papszTableNames[iTable]);
if( oStmt.ExecuteSQL() )
{
while( oStmt.Fetch() )
{
if (papszGeomColumnNames == NULL)
papszGeomColumnNames = CSLAddString( papszGeomColumnNames, oStmt.GetColData(0) );
else if (*papszGeomColumnNames[iTable] == 0)
{
CPLFree(papszGeomColumnNames[iTable]);
papszGeomColumnNames[iTable] = CPLStrdup( oStmt.GetColData(0) );
}
papszCoordDimensions =
CSLAddString( papszCoordDimensions, oStmt.GetColData(1, "2") );
papszSRIds =
CSLAddString( papszSRIds, oStmt.GetColData(2, "0") );
papszSRTexts =
CSLAddString( papszSRTexts, oStmt.GetColData(3, "") );
papszTypes =
CSLAddString( papszTypes, oStmt.GetColData(4, "GEOMETRY") );
}
}
else
{
/* probably the table is missing at all */
InitializeMetadataTables();
}
}
}
/* if requesting all user database table then this takes priority */
if (papszTableNames == NULL && bListAllTables)
{
CPLODBCStatement oStmt( &oSession );
oStmt.Append( "select sys.schemas.name, sys.schemas.name + '.' + sys.objects.name, sys.columns.name from sys.columns join sys.types on sys.columns.system_type_id = sys.types.system_type_id and sys.columns.user_type_id = sys.types.user_type_id join sys.objects on sys.objects.object_id = sys.columns.object_id join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id where (sys.types.name = 'geometry' or sys.types.name = 'geography') and (sys.objects.type = 'U' or sys.objects.type = 'V') union all select sys.schemas.name, sys.schemas.name + '.' + sys.objects.name, '' from sys.objects join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id where not exists (select * from sys.columns sc1 join sys.types on sc1.system_type_id = sys.types.system_type_id where (sys.types.name = 'geometry' or sys.types.name = 'geography') and sys.objects.object_id = sc1.object_id) and (sys.objects.type = 'U' or sys.objects.type = 'V')" );
if( oStmt.ExecuteSQL() )
{
while( oStmt.Fetch() )
{
papszSchemaNames =
CSLAddString( papszSchemaNames, oStmt.GetColData(0) );
papszTableNames =
CSLAddString( papszTableNames, oStmt.GetColData(1) );
papszGeomColumnNames =
CSLAddString( papszGeomColumnNames, oStmt.GetColData(2) );
}
}
}
/* Determine the available tables if not specified. */
if (papszTableNames == NULL && bUseGeometryColumns)
{
CPLODBCStatement oStmt( &oSession );
/* Use join to make sure the existence of the referred column/table */
oStmt.Append( "SELECT f_table_schema, f_table_name, f_geometry_column, coord_dimension, g.srid, srtext, geometry_type FROM dbo.geometry_columns g JOIN INFORMATION_SCHEMA.COLUMNS ON f_table_schema = TABLE_SCHEMA and f_table_name = TABLE_NAME and f_geometry_column = COLUMN_NAME left outer join dbo.spatial_ref_sys s on g.srid = s.srid");
if( oStmt.ExecuteSQL() )
{
while( oStmt.Fetch() )
{
papszSchemaNames =
CSLAddString( papszSchemaNames, oStmt.GetColData(0, "dbo") );
papszTableNames =
CSLAddString( papszTableNames, oStmt.GetColData(1) );
papszGeomColumnNames =
CSLAddString( papszGeomColumnNames, oStmt.GetColData(2) );
papszCoordDimensions =
CSLAddString( papszCoordDimensions, oStmt.GetColData(3, "2") );
papszSRIds =
CSLAddString( papszSRIds, oStmt.GetColData(4, "0") );
papszSRTexts =
CSLAddString( papszSRTexts, oStmt.GetColData(5, "") );
papszTypes =
CSLAddString( papszTypes, oStmt.GetColData(6, "GEOMETRY") );
}
}
else
{
/* probably the table is missing at all */
InitializeMetadataTables();
}
}
/* Query catalog for tables having geometry columns */
if (papszTableNames == NULL)
{
CPLODBCStatement oStmt( &oSession );
oStmt.Append( "SELECT sys.schemas.name, sys.schemas.name + '.' + sys.objects.name, sys.columns.name from sys.columns join sys.types on sys.columns.system_type_id = sys.types.system_type_id and sys.columns.user_type_id = sys.types.user_type_id join sys.objects on sys.objects.object_id = sys.columns.object_id join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id where (sys.types.name = 'geometry' or sys.types.name = 'geography') and (sys.objects.type = 'U' or sys.objects.type = 'V')");
if( oStmt.ExecuteSQL() )
{
while( oStmt.Fetch() )
{
papszSchemaNames =
CSLAddString( papszSchemaNames, oStmt.GetColData(0) );
papszTableNames =
CSLAddString( papszTableNames, oStmt.GetColData(1) );
papszGeomColumnNames =
CSLAddString( papszGeomColumnNames, oStmt.GetColData(2) );
}
}
}
int nSRId, nCoordDimension;
OGRwkbGeometryType eType;
for( int iTable = 0;
papszTableNames != NULL && papszTableNames[iTable] != NULL;
iTable++ )
{
if (papszSRIds != NULL)
nSRId = atoi(papszSRIds[iTable]);
else
nSRId = -1;
if (papszCoordDimensions != NULL)
nCoordDimension = atoi(papszCoordDimensions[iTable]);
else
nCoordDimension = 2;
if (papszTypes != NULL)
eType = OGRFromOGCGeomType(papszTypes[iTable]);
else
eType = wkbUnknown;
if( strlen(papszGeomColumnNames[iTable]) > 0 )
OpenTable( papszSchemaNames[iTable], papszTableNames[iTable], papszGeomColumnNames[iTable],
nCoordDimension, nSRId, papszSRTexts? papszSRTexts[iTable] : NULL, eType, bUpdate );
else
OpenTable( papszSchemaNames[iTable], papszTableNames[iTable], NULL,
nCoordDimension, nSRId, papszSRTexts? papszSRTexts[iTable] : NULL, wkbNone, bUpdate );
}
CSLDestroy( papszTableNames );
CSLDestroy( papszSchemaNames );
CSLDestroy( papszGeomColumnNames );
CSLDestroy( papszCoordDimensions );
CSLDestroy( papszSRIds );
CSLDestroy( papszSRTexts );
CSLDestroy( papszTypes );
CPLFree(pszGeometryFormat);
CPLFree(pszConnectionName);
bDSUpdate = bUpdate;
return TRUE;
}
