1 /******************************************************************************
2 *
3 * Project: MSSQL Spatial driver
4 * Purpose: Implements OGRMSSQLSpatialDataSource class..
5 * Author: Tamas Szekeres, szekerest at gmail.com
6 *
7 ******************************************************************************
8 * Copyright (c) 2010, Tamas Szekeres
9 * Copyright (c) 2010-2013, Even Rouault <even dot rouault at spatialys.com>
10 *
11 * Permission is hereby granted, free of charge, to any person obtaining a
12 * copy of this software and associated documentation files (the "Software"),
13 * to deal in the Software without restriction, including without limitation
14 * the rights to use, copy, modify, merge, publish, distribute, sublicense,
15 * and/or sell copies of the Software, and to permit persons to whom the
16 * Software is furnished to do so, subject to the following conditions:
17 *
18 * The above copyright notice and this permission notice shall be included
19 * in all copies or substantial portions of the Software.
20 *
21 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
22 * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
23 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
24 * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
25 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
26 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
27 * DEALINGS IN THE SOFTWARE.
28 ****************************************************************************/
29
30 #include "ogr_mssqlspatial.h"
31
32 CPL_CVSID("$Id: ogrmssqlspatialdatasource.cpp 327bfdc0f5dd563c3b1c4cbf26d34967c5c9c790 2020-02-28 13:51:40 +0100 Even Rouault $")
33
34 /************************************************************************/
35 /* OGRMSSQLSpatialDataSource() */
36 /************************************************************************/
37
OGRMSSQLSpatialDataSource()38 OGRMSSQLSpatialDataSource::OGRMSSQLSpatialDataSource() :
39 bDSUpdate(false)
40 {
41 pszName = nullptr;
42 pszCatalog = nullptr;
43 papoLayers = nullptr;
44 nLayers = 0;
45
46 nKnownSRID = 0;
47 panSRID = nullptr;
48 papoSRS = nullptr;
49
50 poLayerInCopyMode = nullptr;
51
52 nGeometryFormat = MSSQLGEOMETRY_NATIVE;
53 pszConnection = nullptr;
54
55 sMSSQLVersion.nMajor = 0;
56 sMSSQLVersion.nMinor = 0;
57 sMSSQLVersion.nBuild = 0;
58 sMSSQLVersion.nRevision = 0;
59
60 bUseGeometryColumns = CPLTestBool(CPLGetConfigOption("MSSQLSPATIAL_USE_GEOMETRY_COLUMNS", "YES"));
61 bAlwaysOutputFid = CPLTestBool(CPLGetConfigOption("MSSQLSPATIAL_ALWAYS_OUTPUT_FID", "NO"));
62 bListAllTables = CPLTestBool(CPLGetConfigOption("MSSQLSPATIAL_LIST_ALL_TABLES", "NO"));
63
64 const char* nBCPSizeParam = CPLGetConfigOption("MSSQLSPATIAL_BCP_SIZE", nullptr);
65 if( nBCPSizeParam != nullptr )
66 nBCPSize = atoi(nBCPSizeParam);
67 else
68 nBCPSize = 1000;
69 #ifdef MSSQL_BCP_SUPPORTED
70 bUseCopy = CPLTestBool(CPLGetConfigOption("MSSQLSPATIAL_USE_BCP", "TRUE"));
71 #else
72 bUseCopy = FALSE;
73 #endif
74 CPLDebug( "MSSQLSpatial", "Use COPY/BCP: %d", bUseCopy );
75 }
76
77 /************************************************************************/
78 /* ~OGRMSSQLSpatialDataSource() */
79 /************************************************************************/
80
~OGRMSSQLSpatialDataSource()81 OGRMSSQLSpatialDataSource::~OGRMSSQLSpatialDataSource()
82
83 {
84 for( int i = 0; i < nLayers; i++ )
85 delete papoLayers[i];
86
87 CPLFree( papoLayers );
88
89 CPLFree( pszName );
90 CPLFree( pszCatalog );
91
92 for( int i = 0; i < nKnownSRID; i++ )
93 {
94 if( papoSRS[i] != nullptr )
95 papoSRS[i]->Release();
96 }
97 CPLFree( panSRID );
98 CPLFree( papoSRS );
99 CPLFree( pszConnection );
100 }
101 /************************************************************************/
102 /* OGRMSSQLDecodeVersionString() */
103 /************************************************************************/
104
OGRMSSQLDecodeVersionString(MSSQLVer * psVersion,const char * pszVer)105 void OGRMSSQLSpatialDataSource::OGRMSSQLDecodeVersionString(MSSQLVer* psVersion, const char* pszVer)
106 {
107 while (*pszVer == ' ') pszVer++;
108
109 const char* ptr = pszVer;
110 // get Version string
111 while (*ptr && *ptr != ' ') ptr++;
112 GUInt32 iLen = static_cast<int>(ptr - pszVer);
113 char szVer[20] = {};
114 if (iLen > sizeof(szVer) - 1) iLen = sizeof(szVer) - 1;
115 strncpy(szVer, pszVer, iLen);
116 szVer[iLen] = '\0';
117
118 ptr = pszVer = szVer;
119
120 // get Major number
121 while (*ptr && *ptr != '.') ptr++;
122 iLen = static_cast<int>(ptr - pszVer);
123 char szNum[20] = {};
124 if (iLen > sizeof(szNum) - 1) iLen = sizeof(szNum) - 1;
125 strncpy(szNum, pszVer, iLen);
126 szNum[iLen] = '\0';
127 psVersion->nMajor = atoi(szNum);
128
129 if (*ptr == 0)
130 return;
131 pszVer = ++ptr;
132
133 // get Minor number
134 while (*ptr && *ptr != '.') ptr++;
135 iLen = static_cast<int>(ptr - pszVer);
136 if (iLen > sizeof(szNum) - 1) iLen = sizeof(szNum) - 1;
137 strncpy(szNum, pszVer, iLen);
138 szNum[iLen] = '\0';
139 psVersion->nMinor = atoi(szNum);
140
141 if (*ptr == 0)
142 return;
143 pszVer = ++ptr;
144
145 // get Build number
146 while (*ptr && *ptr != '.') ptr++;
147 iLen = static_cast<int>(ptr - pszVer);
148 if (iLen > sizeof(szNum) - 1) iLen = sizeof(szNum) - 1;
149 strncpy(szNum, pszVer, iLen);
150 szNum[iLen] = '\0';
151 psVersion->nBuild = atoi(szNum);
152
153 if (*ptr == 0)
154 return;
155 pszVer = ++ptr;
156
157 // get Revision number
158 while (*ptr && *ptr != '.') ptr++;
159 iLen = static_cast<int>(ptr - pszVer);
160 if (iLen > sizeof(szNum) - 1) iLen = sizeof(szNum) - 1;
161 strncpy(szNum, pszVer, iLen);
162 szNum[iLen] = '\0';
163 psVersion->nRevision = atoi(szNum);
164 }
165
166 /************************************************************************/
167 /* TestCapability() */
168 /************************************************************************/
169
TestCapability(const char * pszCap)170 int OGRMSSQLSpatialDataSource::TestCapability( const char * pszCap )
171
172 {
173 #if (ODBCVER >= 0x0300)
174 if ( EQUAL(pszCap,ODsCTransactions) )
175 return TRUE;
176 #endif
177 if( EQUAL(pszCap,ODsCCreateLayer) || EQUAL(pszCap,ODsCDeleteLayer) )
178 return TRUE;
179 if( EQUAL(pszCap,ODsCRandomLayerWrite) )
180 return TRUE;
181 if (EQUAL(pszCap, OLCFastGetExtent))
182 return TRUE;
183 else if (EQUAL(pszCap, ODsCCurveGeometries))
184 return TRUE;
185 else if (EQUAL(pszCap, ODsCMeasuredGeometries))
186 return TRUE;
187 else
188 return FALSE;
189 }
190
191 /************************************************************************/
192 /* GetLayer() */
193 /************************************************************************/
194
GetLayer(int iLayer)195 OGRLayer *OGRMSSQLSpatialDataSource::GetLayer( int iLayer )
196
197 {
198 if( iLayer < 0 || iLayer >= nLayers )
199 return nullptr;
200 else
201 return papoLayers[iLayer];
202 }
203
204 /************************************************************************/
205 /* GetLayerByName() */
206 /************************************************************************/
207
GetLayerByName(const char * pszLayerName)208 OGRLayer *OGRMSSQLSpatialDataSource::GetLayerByName( const char* pszLayerName )
209
210 {
211 if (!pszLayerName)
212 return nullptr;
213
214 char *pszTableName = nullptr;
215 char *pszSchemaName = nullptr;
216
217 const char* pszDotPos = strstr(pszLayerName,".");
218 if ( pszDotPos != nullptr )
219 {
220 int length = static_cast<int>(pszDotPos - pszLayerName);
221 pszSchemaName = (char*)CPLMalloc(length+1);
222 strncpy(pszSchemaName, pszLayerName, length);
223 pszSchemaName[length] = '\0';
224 pszTableName = CPLStrdup( pszDotPos + 1 ); //skip "."
225 }
226 else
227 {
228 pszSchemaName = CPLStrdup("dbo");
229 pszTableName = CPLStrdup( pszLayerName );
230 }
231
232 for( int iLayer = 0; iLayer < nLayers; iLayer++ )
233 {
234 if( EQUAL(pszTableName,papoLayers[iLayer]->GetTableName()) &&
235 EQUAL(pszSchemaName,papoLayers[iLayer]->GetSchemaName()) )
236 {
237 CPLFree( pszSchemaName );
238 CPLFree( pszTableName );
239 return papoLayers[iLayer];
240 }
241 }
242
243 CPLFree( pszSchemaName );
244 CPLFree( pszTableName );
245
246 return nullptr;
247 }
248
249 /************************************************************************/
250 /* DeleteLayer() */
251 /************************************************************************/
252
DeleteLayer(int iLayer)253 OGRErr OGRMSSQLSpatialDataSource::DeleteLayer( int iLayer )
254
255 {
256 if( iLayer < 0 || iLayer >= nLayers )
257 return OGRERR_FAILURE;
258
259 EndCopy();
260
261 /* -------------------------------------------------------------------- */
262 /* Blow away our OGR structures related to the layer. This is */
263 /* pretty dangerous if anything has a reference to this layer! */
264 /* -------------------------------------------------------------------- */
265 const char* pszTableName = papoLayers[iLayer]->GetTableName();
266 const char* pszSchemaName = papoLayers[iLayer]->GetSchemaName();
267
268 CPLODBCStatement oStmt( &oSession );
269 if (bUseGeometryColumns)
270 oStmt.Appendf( "DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s'\n",
271 pszSchemaName, pszTableName );
272 oStmt.Appendf("DROP TABLE [%s].[%s]", pszSchemaName, pszTableName );
273
274 CPLDebug( "MSSQLSpatial", "DeleteLayer(%s)", pszTableName );
275
276 papoLayers[iLayer]->SetSpatialIndexFlag(FALSE);
277
278 delete papoLayers[iLayer];
279 memmove( papoLayers + iLayer, papoLayers + iLayer + 1,
280 sizeof(void *) * (nLayers - iLayer - 1) );
281 nLayers--;
282
283 if ( strlen(pszTableName) == 0 )
284 return OGRERR_NONE;
285
286 /* -------------------------------------------------------------------- */
287 /* Remove from the database. */
288 /* -------------------------------------------------------------------- */
289
290 int bInTransaction = oSession.IsInTransaction();
291 if (!bInTransaction)
292 oSession.BeginTransaction();
293
294 if( !oStmt.ExecuteSQL() )
295 {
296 CPLError( CE_Failure, CPLE_AppDefined,
297 "Error deleting layer: %s", GetSession()->GetLastError() );
298
299 if (!bInTransaction)
300 oSession.RollbackTransaction();
301
302 return OGRERR_FAILURE;
303 }
304
305 if (!bInTransaction)
306 oSession.CommitTransaction();
307
308 return OGRERR_NONE;
309 }
310
311 /************************************************************************/
312 /* CreateLayer() */
313 /************************************************************************/
314
ICreateLayer(const char * pszLayerName,OGRSpatialReference * poSRS,OGRwkbGeometryType eType,char ** papszOptions)315 OGRLayer * OGRMSSQLSpatialDataSource::ICreateLayer( const char * pszLayerName,
316 OGRSpatialReference *poSRS,
317 OGRwkbGeometryType eType,
318 char ** papszOptions )
319
320 {
321 char *pszTableName = nullptr;
322 char *pszSchemaName = nullptr;
323 const char *pszGeomType = nullptr;
324 const char *pszGeomColumn = nullptr;
325 int nCoordDimension = 3;
326 char *pszFIDColumnName = nullptr;
327
328 EndCopy();
329
330 /* determine the dimension */
331 if( eType == wkbFlatten(eType) )
332 nCoordDimension = 2;
333
334 if( CSLFetchNameValue( papszOptions, "DIM") != nullptr )
335 nCoordDimension = atoi(CSLFetchNameValue( papszOptions, "DIM"));
336
337 int bExtractSchemaFromLayerName = CPLTestBool(CSLFetchNameValueDef(
338 papszOptions, "EXTRACT_SCHEMA_FROM_LAYER_NAME", "YES"));
339
340 /* MSSQL Schema handling:
341 Extract schema name from input layer name or passed with -lco SCHEMA.
342 Set layer name to "schema.table" or to "table" if schema is not
343 specified
344 */
345 const char* pszDotPos = strstr(pszLayerName,".");
346 if ( pszDotPos != nullptr && bExtractSchemaFromLayerName )
347 {
348 int length = static_cast<int>(pszDotPos - pszLayerName);
349 pszSchemaName = (char*)CPLMalloc(length+1);
350 CPLAssert(pszSchemaName != nullptr); /* to make Coverity happy and not believe a REVERSE_INULL is possible */
351 strncpy(pszSchemaName, pszLayerName, length);
352 pszSchemaName[length] = '\0';
353
354 if( CPLFetchBool(papszOptions, "LAUNDER", true) )
355 pszTableName = LaunderName( pszDotPos + 1 ); //skip "."
356 else
357 pszTableName = CPLStrdup( pszDotPos + 1 ); //skip "."
358 }
359 else
360 {
361 if( CPLFetchBool(papszOptions, "LAUNDER", TRUE) )
362 pszTableName = LaunderName( pszLayerName ); //skip "."
363 else
364 pszTableName = CPLStrdup( pszLayerName ); //skip "."
365 }
366
367 if( CSLFetchNameValue( papszOptions, "SCHEMA" ) != nullptr )
368 {
369 CPLFree(pszSchemaName);
370 pszSchemaName = CPLStrdup(CSLFetchNameValue( papszOptions, "SCHEMA" ));
371 }
372
373 if (pszSchemaName == nullptr)
374 pszSchemaName = CPLStrdup("dbo");
375
376 /* -------------------------------------------------------------------- */
377 /* Do we already have this layer? If so, should we blow it */
378 /* away? */
379 /* -------------------------------------------------------------------- */
380 int iLayer;
381
382 for( iLayer = 0; iLayer < nLayers; iLayer++ )
383 {
384 if( EQUAL(pszTableName,papoLayers[iLayer]->GetTableName()) &&
385 EQUAL(pszSchemaName,papoLayers[iLayer]->GetSchemaName()) )
386 {
387 if( CSLFetchNameValue( papszOptions, "OVERWRITE" ) != nullptr
388 && !EQUAL(CSLFetchNameValue(papszOptions,"OVERWRITE"),"NO") )
389 {
390 CPLFree(pszSchemaName);
391 pszSchemaName = CPLStrdup(papoLayers[iLayer]->GetSchemaName());
392
393 DeleteLayer( iLayer );
394 }
395 else
396 {
397 CPLError( CE_Failure, CPLE_AppDefined,
398 "Layer %s already exists, CreateLayer failed.\n"
399 "Use the layer creation option OVERWRITE=YES to "
400 "replace it.",
401 pszLayerName );
402
403 CPLFree( pszSchemaName );
404 CPLFree( pszTableName );
405 return nullptr;
406 }
407 }
408 }
409
410 /* -------------------------------------------------------------------- */
411 /* Handle the GEOM_TYPE option. */
412 /* -------------------------------------------------------------------- */
413 if ( eType != wkbNone )
414 {
415 pszGeomType = CSLFetchNameValue( papszOptions, "GEOM_TYPE" );
416
417 if( !pszGeomType )
418 pszGeomType = "geometry";
419
420 if( !EQUAL(pszGeomType, "geometry")
421 && !EQUAL(pszGeomType, "geography"))
422 {
423 CPLError( CE_Failure, CPLE_AppDefined,
424 "FORMAT=%s not recognised or supported.",
425 pszGeomType );
426
427 CPLFree( pszSchemaName );
428 CPLFree( pszTableName );
429 return nullptr;
430 }
431
432 /* determine the geometry column name */
433 pszGeomColumn = CSLFetchNameValue( papszOptions, "GEOMETRY_NAME");
434 if (!pszGeomColumn)
435 pszGeomColumn = CSLFetchNameValue( papszOptions, "GEOM_NAME");
436 if (!pszGeomColumn)
437 pszGeomColumn = "ogr_geometry";
438 }
439 const bool bGeomNullable =
440 CPLFetchBool(papszOptions, "GEOMETRY_NULLABLE", true);
441
442 /* -------------------------------------------------------------------- */
443 /* Initialize the metadata tables */
444 /* -------------------------------------------------------------------- */
445
446 if (InitializeMetadataTables() != OGRERR_NONE)
447 {
448 CPLFree( pszSchemaName );
449 CPLFree( pszTableName );
450 return nullptr;
451 }
452
453 /* -------------------------------------------------------------------- */
454 /* Try to get the SRS Id of this spatial reference system, */
455 /* adding to the srs table if needed. */
456 /* -------------------------------------------------------------------- */
457 int nSRSId = 0;
458
459 if( CSLFetchNameValue( papszOptions, "SRID") != nullptr )
460 nSRSId = atoi(CSLFetchNameValue( papszOptions, "SRID"));
461
462 if( nSRSId == 0 && poSRS != nullptr )
463 nSRSId = FetchSRSId( poSRS );
464
465 /* -------------------------------------------------------------------- */
466 /* Create a new table and create a new entry in the geometry, */
467 /* geometry_columns metadata table. */
468 /* -------------------------------------------------------------------- */
469
470 CPLODBCStatement oStmt( &oSession );
471
472 if( eType != wkbNone && bUseGeometryColumns)
473 {
474 const char *pszGeometryType = OGRToOGCGeomType(eType);
475
476 oStmt.Appendf( "DELETE FROM geometry_columns WHERE f_table_schema = '%s' "
477 "AND f_table_name = '%s'\n", pszSchemaName, pszTableName );
478
479 oStmt.Appendf("INSERT INTO [geometry_columns] ([f_table_catalog], [f_table_schema] ,[f_table_name], "
480 "[f_geometry_column],[coord_dimension],[srid],[geometry_type]) VALUES ('%s', '%s', '%s', '%s', %d, %d, '%s')\n",
481 pszCatalog, pszSchemaName, pszTableName, pszGeomColumn, nCoordDimension, nSRSId, pszGeometryType );
482 }
483
484 if (!EQUAL(pszSchemaName,"dbo"))
485 {
486 // creating the schema if not exists
487 oStmt.Appendf("IF NOT EXISTS (SELECT name from sys.schemas WHERE name = '%s') EXEC sp_executesql N'CREATE SCHEMA [%s]'\n", pszSchemaName, pszSchemaName);
488 }
489
490 /* determine the FID column name */
491 const char* pszFIDColumnNameIn = CSLFetchNameValueDef(papszOptions, "FID", "ogr_fid");
492 if( CPLFetchBool(papszOptions, "LAUNDER", TRUE) )
493 pszFIDColumnName = LaunderName( pszFIDColumnNameIn );
494 else
495 pszFIDColumnName = CPLStrdup( pszFIDColumnNameIn );
496
497 const bool bFID64 = CPLFetchBool(papszOptions, "FID64", FALSE);
498 const char* pszFIDType = bFID64 ? "bigint": "int";
499
500 if( eType == wkbNone )
501 {
502 oStmt.Appendf("CREATE TABLE [%s].[%s] ([%s] [%s] IDENTITY(1,1) NOT NULL, "
503 "CONSTRAINT [PK_%s] PRIMARY KEY CLUSTERED ([%s] ASC))",
504 pszSchemaName, pszTableName, pszFIDColumnName, pszFIDType, pszTableName, pszFIDColumnName);
505 }
506 else
507 {
508 oStmt.Appendf("CREATE TABLE [%s].[%s] ([%s] [%s] IDENTITY(1,1) NOT NULL, "
509 "[%s] [%s] %s, CONSTRAINT [PK_%s] PRIMARY KEY CLUSTERED ([%s] ASC))",
510 pszSchemaName, pszTableName, pszFIDColumnName, pszFIDType, pszGeomColumn, pszGeomType,
511 bGeomNullable ? "NULL":"NOT NULL", pszTableName, pszFIDColumnName);
512 }
513
514 CPLFree( pszFIDColumnName );
515
516 int bInTransaction = oSession.IsInTransaction();
517 if (!bInTransaction)
518 oSession.BeginTransaction();
519
520 if( !oStmt.ExecuteSQL() )
521 {
522 CPLError( CE_Failure, CPLE_AppDefined,
523 "Error creating layer: %s When using the overwrite option and the layer doesn't contain geometry column, you might require to use the MSSQLSPATIAL_LIST_ALL_TABLES config option to get the previous layer deleted before creating the new one.", GetSession()->GetLastError() );
524
525 if (!bInTransaction)
526 oSession.RollbackTransaction();
527
528 return nullptr;
529 }
530
531 if (!bInTransaction)
532 oSession.CommitTransaction();
533
534 /* -------------------------------------------------------------------- */
535 /* Create the layer object. */
536 /* -------------------------------------------------------------------- */
537 OGRMSSQLSpatialTableLayer *poLayer;
538
539 poLayer = new OGRMSSQLSpatialTableLayer( this );
540
541 if (bInTransaction)
542 poLayer->SetLayerStatus(MSSQLLAYERSTATUS_INITIAL);
543 else
544 poLayer->SetLayerStatus(MSSQLLAYERSTATUS_CREATED);
545
546 poLayer->SetLaunderFlag( CPLFetchBool(papszOptions, "LAUNDER", true) );
547 poLayer->SetPrecisionFlag( CPLFetchBool(papszOptions, "PRECISION", true));
548
549 if( bUseCopy )
550 poLayer->SetUseCopy(nBCPSize);
551
552 const char *pszSI = CSLFetchNameValue( papszOptions, "SPATIAL_INDEX" );
553 int bCreateSpatialIndex = ( pszSI == nullptr || CPLTestBool(pszSI) );
554 if (pszGeomColumn == nullptr)
555 bCreateSpatialIndex = FALSE;
556
557 poLayer->SetSpatialIndexFlag( bCreateSpatialIndex );
558
559 const char *pszUploadGeometryFormat = CSLFetchNameValue( papszOptions, "UPLOAD_GEOM_FORMAT" );
560 if (pszUploadGeometryFormat)
561 {
562 if (STARTS_WITH_CI(pszUploadGeometryFormat,"wkb"))
563 poLayer->SetUploadGeometryFormat(MSSQLGEOMETRY_WKB);
564 else if (STARTS_WITH_CI(pszUploadGeometryFormat, "wkt"))
565 poLayer->SetUploadGeometryFormat(MSSQLGEOMETRY_WKT);
566 }
567
568 char *pszWKT = nullptr;
569 if( poSRS && poSRS->exportToWkt( &pszWKT ) != OGRERR_NONE )
570 {
571 CPLFree(pszWKT);
572 pszWKT = nullptr;
573 }
574
575 if( bFID64 )
576 poLayer->SetMetadataItem(OLMD_FID64, "YES");
577
578 if (poLayer->Initialize(pszSchemaName, pszTableName, pszGeomColumn, nCoordDimension, nSRSId, pszWKT, eType) == CE_Failure)
579 {
580 CPLFree( pszSchemaName );
581 CPLFree( pszTableName );
582 CPLFree( pszWKT );
583 return nullptr;
584 }
585
586 CPLFree( pszSchemaName );
587 CPLFree( pszTableName );
588 CPLFree( pszWKT );
589
590 /* -------------------------------------------------------------------- */
591 /* Add layer to data source layer list. */
592 /* -------------------------------------------------------------------- */
593 papoLayers = (OGRMSSQLSpatialTableLayer **)
594 CPLRealloc( papoLayers, sizeof(OGRMSSQLSpatialTableLayer *) * (nLayers+1) );
595
596 papoLayers[nLayers++] = poLayer;
597
598 return poLayer;
599 }
600
601 /************************************************************************/
602 /* OpenTable() */
603 /************************************************************************/
604
OpenTable(const char * pszSchemaName,const char * pszTableName,const char * pszGeomCol,int nCoordDimension,int nSRID,const char * pszSRText,OGRwkbGeometryType eType,bool bUpdate)605 int OGRMSSQLSpatialDataSource::OpenTable( const char *pszSchemaName, const char *pszTableName,
606 const char *pszGeomCol, int nCoordDimension,
607 int nSRID, const char *pszSRText, OGRwkbGeometryType eType,
608 bool bUpdate )
609 {
610 /* -------------------------------------------------------------------- */
611 /* Create the layer object. */
612 /* -------------------------------------------------------------------- */
613 OGRMSSQLSpatialTableLayer *poLayer = new OGRMSSQLSpatialTableLayer( this );
614
615 if( poLayer->Initialize( pszSchemaName, pszTableName, pszGeomCol, nCoordDimension, nSRID, pszSRText, eType ) )
616 {
617 delete poLayer;
618 return FALSE;
619 }
620 poLayer->SetUpdate(bUpdate);
621
622 if (bUseCopy)
623 poLayer->SetUseCopy(nBCPSize);
624
625 /* -------------------------------------------------------------------- */
626 /* Add layer to data source layer list. */
627 /* -------------------------------------------------------------------- */
628 papoLayers = (OGRMSSQLSpatialTableLayer **)
629 CPLRealloc( papoLayers, sizeof(OGRMSSQLSpatialTableLayer *) * (nLayers+1) );
630 papoLayers[nLayers++] = poLayer;
631
632 return TRUE;
633 }
634
635 /************************************************************************/
636 /* GetLayerCount() */
637 /************************************************************************/
638
GetLayerCount()639 int OGRMSSQLSpatialDataSource::GetLayerCount()
640 {
641 return nLayers;
642 }
643
644 /************************************************************************/
645 /* ParseValue() */
646 /************************************************************************/
647
ParseValue(char ** pszValue,char * pszSource,const char * pszKey,int nStart,int nNext,int nTerm,int bRemove)648 int OGRMSSQLSpatialDataSource::ParseValue(char** pszValue, char* pszSource, const char* pszKey, int nStart, int nNext, int nTerm, int bRemove)
649 {
650 int nLen = static_cast<int>(strlen(pszKey));
651 if ((*pszValue) == nullptr && nStart + nLen < nNext &&
652 EQUALN(pszSource + nStart, pszKey, nLen))
653 {
654 *pszValue = (char*)CPLMalloc( sizeof(char) * (nNext - nStart - nLen + 1) );
655 strncpy(*pszValue, pszSource + nStart + nLen, nNext - nStart - nLen);
656 (*pszValue)[nNext - nStart - nLen] = 0;
657
658 if (bRemove)
659 {
660 // remove the value from the source string
661 if (pszSource[nNext] == ';')
662 memmove( pszSource + nStart, pszSource + nNext + 1, nTerm - nNext);
663 else
664 memmove( pszSource + nStart, pszSource + nNext, nTerm - nNext + 1);
665 }
666 return TRUE;
667 }
668 return FALSE;
669 }
670
671 /************************************************************************/
672 /* Open() */
673 /************************************************************************/
674
Open(const char * pszNewName,bool bUpdate,int bTestOpen)675 int OGRMSSQLSpatialDataSource::Open( const char * pszNewName, bool bUpdate,
676 int bTestOpen )
677
678 {
679 CPLAssert( nLayers == 0 );
680
681 if( !STARTS_WITH_CI(pszNewName, "MSSQL:") )
682 {
683 if( !bTestOpen )
684 CPLError( CE_Failure, CPLE_AppDefined,
685 "%s does not conform to MSSSQLSpatial naming convention,"
686 " MSSQL:*\n", pszNewName );
687 return FALSE;
688 }
689
690 /* Determine if the connection string contains specific values */
691 char* pszTableSpec = nullptr;
692 char* pszGeometryFormat = nullptr;
693 char* pszConnectionName = CPLStrdup(pszNewName + 6);
694 char* pszDriver = nullptr;
695 int nCurrent, nNext, nTerm;
696 nCurrent = nNext = nTerm = static_cast<int>(strlen(pszConnectionName));
697
698 while (nCurrent > 0)
699 {
700 --nCurrent;
701 if (pszConnectionName[nCurrent] == ';')
702 {
703 nNext = nCurrent;
704 continue;
705 }
706
707 if (ParseValue(&pszCatalog, pszConnectionName, "database=",
708 nCurrent, nNext, nTerm, FALSE))
709 continue;
710
711 if (ParseValue(&pszTableSpec, pszConnectionName, "tables=",
712 nCurrent, nNext, nTerm, TRUE))
713 continue;
714
715 if (ParseValue(&pszDriver, pszConnectionName, "driver=",
716 nCurrent, nNext, nTerm, FALSE))
717 continue;
718
719 if (ParseValue(&pszGeometryFormat, pszConnectionName,
720 "geometryformat=", nCurrent, nNext, nTerm, TRUE))
721 {
722 if (STARTS_WITH_CI(pszGeometryFormat, "wkbzm"))
723 nGeometryFormat = MSSQLGEOMETRY_WKBZM;
724 else if (STARTS_WITH_CI(pszGeometryFormat, "wkb"))
725 nGeometryFormat = MSSQLGEOMETRY_WKB;
726 else if (STARTS_WITH_CI(pszGeometryFormat, "wkt"))
727 nGeometryFormat = MSSQLGEOMETRY_WKT;
728 else if (STARTS_WITH_CI(pszGeometryFormat, "native"))
729 nGeometryFormat = MSSQLGEOMETRY_NATIVE;
730 else
731 {
732 CPLError( CE_Failure, CPLE_AppDefined,
733 "Invalid geometry type specified: %s,"
734 " MSSQL:*\n", pszGeometryFormat );
735
736 CPLFree(pszTableSpec);
737 CPLFree(pszGeometryFormat);
738 CPLFree(pszConnectionName);
739 CPLFree(pszDriver);
740 return FALSE;
741 }
742
743 CPLFree(pszGeometryFormat);
744 pszGeometryFormat = nullptr;
745 continue;
746 }
747 }
748
749 /* Determine if the connection string contains the catalog portion */
750 if( pszCatalog == nullptr )
751 {
752 CPLError( CE_Failure, CPLE_AppDefined,
753 "'%s' does not contain the 'database' portion\n", pszNewName );
754
755 CPLFree(pszTableSpec);
756 CPLFree(pszGeometryFormat);
757 CPLFree(pszConnectionName);
758 CPLFree(pszDriver);
759 return FALSE;
760 }
761
762 pszName = CPLStrdup(pszNewName);
763
764 char **papszTableNames=nullptr;
765 char **papszSchemaNames=nullptr;
766 char **papszGeomColumnNames=nullptr;
767 char **papszCoordDimensions=nullptr;
768 char **papszSRIds=nullptr;
769 char **papszSRTexts=nullptr;
770
771 /* Determine if the connection string contains the TABLES portion */
772 if( pszTableSpec != nullptr )
773 {
774 char **papszTableList;
775 int i;
776
777 papszTableList = CSLTokenizeString2( pszTableSpec, ",", 0 );
778
779 for( i = 0; i < CSLCount(papszTableList); i++ )
780 {
781 char **papszQualifiedParts;
782
783 // Get schema and table name
784 papszQualifiedParts = CSLTokenizeString2( papszTableList[i],
785 ".", 0 );
786
787 /* Find the geometry column name if specified */
788 if( CSLCount( papszQualifiedParts ) >= 1 )
789 {
790 char* pszGeomColumnName = nullptr;
791 char* pos = strchr(papszQualifiedParts[CSLCount( papszQualifiedParts ) - 1], '(');
792 if (pos != nullptr)
793 {
794 *pos = '\0';
795 pszGeomColumnName = pos+1;
796 int len = static_cast<int>(strlen(pszGeomColumnName));
797 if (len > 0)
798 pszGeomColumnName[len - 1] = '\0';
799 }
800 papszGeomColumnNames = CSLAddString( papszGeomColumnNames,
801 pszGeomColumnName ? pszGeomColumnName : "");
802 }
803
804 if( CSLCount( papszQualifiedParts ) == 2 )
805 {
806 papszSchemaNames = CSLAddString( papszSchemaNames,
807 papszQualifiedParts[0] );
808 papszTableNames = CSLAddString( papszTableNames,
809 papszQualifiedParts[1] );
810 }
811 else if( CSLCount( papszQualifiedParts ) == 1 )
812 {
813 papszSchemaNames = CSLAddString( papszSchemaNames, "dbo");
814 papszTableNames = CSLAddString( papszTableNames,
815 papszQualifiedParts[0] );
816 }
817
818 CSLDestroy(papszQualifiedParts);
819 }
820
821 CSLDestroy(papszTableList);
822 }
823
824 CPLFree(pszTableSpec);
825
826 if ( pszDriver == nullptr )
827 {
828 char* pszConnectionName2 = pszConnectionName;
829 #if SQLNCLI_VERSION == 11
830 pszDriver = CPLStrdup("{SQL Server Native Client 11.0}");
831 #elif SQLNCLI_VERSION == 10
832 pszDriver = CPLStrdup("{SQL Server Native Client 10.0}");
833 #elif MSODBCSQL_VERSION == 13
834 pszDriver = CPLStrdup("{ODBC Driver 13 for SQL Server}");
835 #elif MSODBCSQL_VERSION == 17
836 pszDriver = CPLStrdup("{ODBC Driver 17 for SQL Server}");
837 #else
838 pszDriver = CPLStrdup("{SQL Server}");
839 #endif
840 pszConnectionName = CPLStrdup(CPLSPrintf("DRIVER=%s;%s", pszDriver, pszConnectionName2));
841 CPLFree(pszConnectionName2);
842 }
843
844 CPLFree(pszDriver);
845
846 /* Initialize the SQL Server connection. */
847 if( !oSession.EstablishSession( pszConnectionName, "", "" ) )
848 {
849 /* Get a list of the available drivers */
850 HENV hEnv;
851 if ( SQL_SUCCEEDED(SQLAllocEnv( &hEnv ) ) )
852 {
853 CPLString osDriverList;
854 SQLUSMALLINT direction = SQL_FETCH_FIRST;
855 SQLSMALLINT driver_ret;
856 SQLSMALLINT attr_ret;
857 SQLCHAR attr[256];
858 SQLCHAR driver[256];
859 while(SQL_SUCCEEDED(SQLDrivers(hEnv, direction,
860 driver, sizeof(driver), &driver_ret, attr, sizeof(attr), &attr_ret)))
861 {
862 direction = SQL_FETCH_NEXT;
863 osDriverList += CPLSPrintf("%s\n", driver);
864 }
865
866 CPLError( CE_Failure, CPLE_AppDefined,
867 "Unable to initialize connection to the server for %s,\n"
868 "%s\n"
869 "Try specifying the driver in the connection string from the list of available drivers:\n"
870 "%s", pszNewName, oSession.GetLastError(), osDriverList.c_str() );
871 }
872 else
873 {
874 CPLError( CE_Failure, CPLE_AppDefined,
875 "Unable to initialize connection to the server for %s,\n"
876 "%s\n", pszNewName, oSession.GetLastError() );
877 }
878
879 if( hEnv != nullptr )
880 SQLFreeEnv( hEnv );
881
882 CSLDestroy( papszTableNames );
883 CSLDestroy( papszSchemaNames );
884 CSLDestroy( papszGeomColumnNames );
885 CSLDestroy( papszCoordDimensions );
886 CSLDestroy( papszSRIds );
887 CSLDestroy( papszSRTexts );
888 CPLFree(pszGeometryFormat);
889 CPLFree(pszConnectionName);
890 return FALSE;
891 }
892
893 /* -------------------------------------------------------------------- */
894 /* Find out SQLServer version */
895 /* -------------------------------------------------------------------- */
896 if (true) {
897 sMSSQLVersion.nMajor = -1;
898 sMSSQLVersion.nMinor = -1;
899 sMSSQLVersion.nBuild = -1;
900 sMSSQLVersion.nRevision = -1;
901
902 CPLODBCStatement oStmt(&oSession);
903
904 /* Use join to make sure the existence of the referred column/table */
905 oStmt.Append("SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion;");
906
907 if (oStmt.ExecuteSQL())
908 {
909 while (oStmt.Fetch())
910 {
911 OGRMSSQLDecodeVersionString(&sMSSQLVersion, oStmt.GetColData(0));
912 }
913 }
914 }
915
916 char** papszTypes = nullptr;
917
918 /* read metadata for the specified tables */
919 if (papszTableNames != nullptr && bUseGeometryColumns)
920 {
921 for( int iTable = 0;
922 papszTableNames[iTable] != nullptr;
923 iTable++ )
924 {
925 CPLODBCStatement oStmt( &oSession );
926
927 /* Use join to make sure the existence of the referred column/table */
928 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]);
929
930 if( oStmt.ExecuteSQL() )
931 {
932 while( oStmt.Fetch() )
933 {
934 if (papszGeomColumnNames == nullptr)
935 papszGeomColumnNames = CSLAddString( papszGeomColumnNames, oStmt.GetColData(0) );
936 else if (*papszGeomColumnNames[iTable] == 0)
937 {
938 CPLFree(papszGeomColumnNames[iTable]);
939 papszGeomColumnNames[iTable] = CPLStrdup( oStmt.GetColData(0) );
940 }
941
942 papszCoordDimensions =
943 CSLAddString( papszCoordDimensions, oStmt.GetColData(1, "2") );
944 papszSRIds =
945 CSLAddString( papszSRIds, oStmt.GetColData(2, "0") );
946 papszSRTexts =
947 CSLAddString( papszSRTexts, oStmt.GetColData(3, "") );
948 papszTypes =
949 CSLAddString( papszTypes, oStmt.GetColData(4, "GEOMETRY") );
950 }
951 }
952 else
953 {
954 /* probably the table is missing at all */
955 InitializeMetadataTables();
956 }
957 }
958 }
959
960 /* if requesting all user database table then this takes priority */
961 if (papszTableNames == nullptr && bListAllTables)
962 {
963 CPLODBCStatement oStmt( &oSession );
964
965 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')" );
966
967 if( oStmt.ExecuteSQL() )
968 {
969 while( oStmt.Fetch() )
970 {
971 papszSchemaNames =
972 CSLAddString( papszSchemaNames, oStmt.GetColData(0) );
973 papszTableNames =
974 CSLAddString( papszTableNames, oStmt.GetColData(1) );
975 papszGeomColumnNames =
976 CSLAddString( papszGeomColumnNames, oStmt.GetColData(2) );
977 }
978 }
979 }
980
981 /* Determine the available tables if not specified. */
982 if (papszTableNames == nullptr && bUseGeometryColumns)
983 {
984 CPLODBCStatement oStmt( &oSession );
985
986 /* Use join to make sure the existence of the referred column/table */
987 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");
988
989 if( oStmt.ExecuteSQL() )
990 {
991 while( oStmt.Fetch() )
992 {
993 papszSchemaNames =
994 CSLAddString( papszSchemaNames, oStmt.GetColData(0, "dbo") );
995 papszTableNames =
996 CSLAddString( papszTableNames, oStmt.GetColData(1) );
997 papszGeomColumnNames =
998 CSLAddString( papszGeomColumnNames, oStmt.GetColData(2) );
999 papszCoordDimensions =
1000 CSLAddString( papszCoordDimensions, oStmt.GetColData(3, "2") );
1001 papszSRIds =
1002 CSLAddString( papszSRIds, oStmt.GetColData(4, "0") );
1003 papszSRTexts =
1004 CSLAddString( papszSRTexts, oStmt.GetColData(5, "") );
1005 papszTypes =
1006 CSLAddString( papszTypes, oStmt.GetColData(6, "GEOMETRY") );
1007 }
1008 }
1009 else
1010 {
1011 /* probably the table is missing at all */
1012 InitializeMetadataTables();
1013 }
1014 }
1015
1016 /* Query catalog for tables having geometry columns */
1017 if (papszTableNames == nullptr)
1018 {
1019 CPLODBCStatement oStmt( &oSession );
1020
1021 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')");
1022
1023 if( oStmt.ExecuteSQL() )
1024 {
1025 while( oStmt.Fetch() )
1026 {
1027 papszSchemaNames =
1028 CSLAddString( papszSchemaNames, oStmt.GetColData(0) );
1029 papszTableNames =
1030 CSLAddString( papszTableNames, oStmt.GetColData(1) );
1031 papszGeomColumnNames =
1032 CSLAddString( papszGeomColumnNames, oStmt.GetColData(2) );
1033 }
1034 }
1035 }
1036
1037 int nSRId, nCoordDimension;
1038 OGRwkbGeometryType eType;
1039
1040 for( int iTable = 0;
1041 papszTableNames != nullptr && papszTableNames[iTable] != nullptr;
1042 iTable++ )
1043 {
1044 if (papszSRIds != nullptr)
1045 nSRId = atoi(papszSRIds[iTable]);
1046 else
1047 nSRId = 0;
1048
1049 if (papszCoordDimensions != nullptr)
1050 nCoordDimension = atoi(papszCoordDimensions[iTable]);
1051 else
1052 nCoordDimension = 2;
1053
1054 if (papszTypes != nullptr)
1055 eType = OGRFromOGCGeomType(papszTypes[iTable]);
1056 else
1057 eType = wkbUnknown;
1058
1059 CPLAssert(papszGeomColumnNames && papszGeomColumnNames[iTable]);
1060 if( strlen(papszGeomColumnNames[iTable]) > 0 )
1061 OpenTable( papszSchemaNames[iTable], papszTableNames[iTable], papszGeomColumnNames[iTable],
1062 nCoordDimension, nSRId, papszSRTexts? papszSRTexts[iTable] : nullptr, eType, bUpdate );
1063 else
1064 OpenTable( papszSchemaNames[iTable], papszTableNames[iTable], nullptr,
1065 nCoordDimension, nSRId, papszSRTexts? papszSRTexts[iTable] : nullptr, wkbNone, bUpdate );
1066 }
1067
1068 CSLDestroy( papszTableNames );
1069 CSLDestroy( papszSchemaNames );
1070 CSLDestroy( papszGeomColumnNames );
1071 CSLDestroy( papszCoordDimensions );
1072 CSLDestroy( papszSRIds );
1073 CSLDestroy( papszSRTexts );
1074 CSLDestroy( papszTypes );
1075
1076 CPLFree(pszGeometryFormat);
1077
1078 CPLFree(pszConnection);
1079 pszConnection = pszConnectionName;
1080
1081 bDSUpdate = bUpdate;
1082
1083 return TRUE;
1084 }
1085
1086 /************************************************************************/
1087 /* ExecuteSQL() */
1088 /************************************************************************/
1089
ExecuteSQL(const char * pszSQLCommand,OGRGeometry * poSpatialFilter,const char * pszDialect)1090 OGRLayer * OGRMSSQLSpatialDataSource::ExecuteSQL( const char *pszSQLCommand,
1091 OGRGeometry *poSpatialFilter,
1092 const char *pszDialect )
1093
1094 {
1095 /* -------------------------------------------------------------------- */
1096 /* Use generic implementation for recognized dialects */
1097 /* -------------------------------------------------------------------- */
1098 if( IsGenericSQLDialect(pszDialect) )
1099 return OGRDataSource::ExecuteSQL( pszSQLCommand,
1100 poSpatialFilter,
1101 pszDialect );
1102
1103 /* -------------------------------------------------------------------- */
1104 /* Special case DELLAYER: command. */
1105 /* -------------------------------------------------------------------- */
1106 if( STARTS_WITH_CI(pszSQLCommand, "DELLAYER:") )
1107 {
1108 const char *pszLayerName = pszSQLCommand + 9;
1109
1110 while( *pszLayerName == ' ' )
1111 pszLayerName++;
1112
1113 OGRLayer* poLayer = GetLayerByName(pszLayerName);
1114
1115 for( int iLayer = 0; iLayer < nLayers; iLayer++ )
1116 {
1117 if( papoLayers[iLayer] == poLayer )
1118 {
1119 DeleteLayer( iLayer );
1120 break;
1121 }
1122 }
1123 return nullptr;
1124 }
1125
1126 CPLDebug( "MSSQLSpatial", "ExecuteSQL(%s) called.", pszSQLCommand );
1127
1128 if( STARTS_WITH_CI(pszSQLCommand, "DROP SPATIAL INDEX ON ") )
1129 {
1130 /* Handle command to drop a spatial index. */
1131 OGRMSSQLSpatialTableLayer *poLayer = new OGRMSSQLSpatialTableLayer( this );
1132
1133 if (poLayer)
1134 {
1135 if( poLayer->Initialize( nullptr, pszSQLCommand + 22, nullptr, 0, 0, nullptr, wkbUnknown ) != CE_None )
1136 {
1137 CPLError( CE_Failure, CPLE_AppDefined,
1138 "Failed to initialize layer '%s'", pszSQLCommand + 22 );
1139 }
1140 poLayer->DropSpatialIndex();
1141 delete poLayer;
1142 }
1143 return nullptr;
1144 }
1145 else if( STARTS_WITH_CI(pszSQLCommand, "CREATE SPATIAL INDEX ON ") )
1146 {
1147 /* Handle command to create a spatial index. */
1148 OGRMSSQLSpatialTableLayer *poLayer = new OGRMSSQLSpatialTableLayer( this );
1149
1150 if (poLayer)
1151 {
1152 if( poLayer->Initialize( nullptr, pszSQLCommand + 24, nullptr, 0, 0, nullptr, wkbUnknown ) != CE_None )
1153 {
1154 CPLError( CE_Failure, CPLE_AppDefined,
1155 "Failed to initialize layer '%s'", pszSQLCommand + 24 );
1156 }
1157 poLayer->CreateSpatialIndex();
1158 delete poLayer;
1159 }
1160 return nullptr;
1161 }
1162
1163 /* Execute the command natively */
1164 CPLODBCStatement *poStmt = new CPLODBCStatement( &oSession );
1165 poStmt->Append( pszSQLCommand );
1166
1167 if( !poStmt->ExecuteSQL() )
1168 {
1169 CPLError( CE_Failure, CPLE_AppDefined,
1170 "%s", oSession.GetLastError() );
1171 delete poStmt;
1172 return nullptr;
1173 }
1174
1175 /* -------------------------------------------------------------------- */
1176 /* Are there result columns for this statement? */
1177 /* -------------------------------------------------------------------- */
1178 if( poStmt->GetColCount() == 0 )
1179 {
1180 delete poStmt;
1181 CPLErrorReset();
1182 return nullptr;
1183 }
1184
1185 /* -------------------------------------------------------------------- */
1186 /* Create a results layer. It will take ownership of the */
1187 /* statement. */
1188 /* -------------------------------------------------------------------- */
1189
1190 OGRMSSQLSpatialSelectLayer* poLayer =
1191 new OGRMSSQLSpatialSelectLayer( this, poStmt );
1192
1193 if( poSpatialFilter != nullptr )
1194 poLayer->SetSpatialFilter( poSpatialFilter );
1195
1196 return poLayer;
1197 }
1198
1199 /************************************************************************/
1200 /* ReleaseResultSet() */
1201 /************************************************************************/
1202
ReleaseResultSet(OGRLayer * poLayer)1203 void OGRMSSQLSpatialDataSource::ReleaseResultSet( OGRLayer * poLayer )
1204
1205 {
1206 delete poLayer;
1207 }
1208
1209 /************************************************************************/
1210 /* LaunderName() */
1211 /************************************************************************/
1212
LaunderName(const char * pszSrcName)1213 char *OGRMSSQLSpatialDataSource::LaunderName( const char *pszSrcName )
1214
1215 {
1216 char *pszSafeName = CPLStrdup( pszSrcName );
1217 int i;
1218
1219 for( i = 0; pszSafeName[i] != '\0'; i++ )
1220 {
1221 pszSafeName[i] = (char) tolower( pszSafeName[i] );
1222 if( pszSafeName[i] == '-' || pszSafeName[i] == '#' )
1223 pszSafeName[i] = '_';
1224 }
1225
1226 return pszSafeName;
1227 }
1228
1229 /************************************************************************/
1230 /* InitializeMetadataTables() */
1231 /* */
1232 /* Create the metadata tables (SPATIAL_REF_SYS and */
1233 /* GEOMETRY_COLUMNS). */
1234 /************************************************************************/
1235
InitializeMetadataTables()1236 OGRErr OGRMSSQLSpatialDataSource::InitializeMetadataTables()
1237
1238 {
1239 if (bUseGeometryColumns)
1240 {
1241 CPLODBCStatement oStmt( &oSession );
1242
1243 oStmt.Append( "IF NOT EXISTS (SELECT * FROM sys.objects WHERE "
1244 "object_id = OBJECT_ID(N'[dbo].[geometry_columns]') AND type in (N'U')) "
1245 "CREATE TABLE geometry_columns (f_table_catalog varchar(128) not null, "
1246 "f_table_schema varchar(128) not null, f_table_name varchar(256) not null, "
1247 "f_geometry_column varchar(256) not null, coord_dimension integer not null, "
1248 "srid integer not null, geometry_type varchar(30) not null, "
1249 "CONSTRAINT geometry_columns_pk PRIMARY KEY (f_table_catalog, "
1250 "f_table_schema, f_table_name, f_geometry_column));\n" );
1251
1252 oStmt.Append( "IF NOT EXISTS (SELECT * FROM sys.objects "
1253 "WHERE object_id = OBJECT_ID(N'[dbo].[spatial_ref_sys]') AND type in (N'U')) "
1254 "CREATE TABLE spatial_ref_sys (srid integer not null "
1255 "PRIMARY KEY, auth_name varchar(256), auth_srid integer, srtext varchar(2048), proj4text varchar(2048))" );
1256
1257 int bInTransaction = oSession.IsInTransaction();
1258 if (!bInTransaction)
1259 oSession.BeginTransaction();
1260
1261 if( !oStmt.ExecuteSQL() )
1262 {
1263 CPLError( CE_Failure, CPLE_AppDefined,
1264 "Error initializing the metadata tables : %s", GetSession()->GetLastError() );
1265
1266 if (!bInTransaction)
1267 oSession.RollbackTransaction();
1268
1269 return OGRERR_FAILURE;
1270 }
1271
1272 if (!bInTransaction)
1273 oSession.CommitTransaction();
1274 }
1275
1276 return OGRERR_NONE;
1277 }
1278
1279 /************************************************************************/
1280 /* FetchSRS() */
1281 /* */
1282 /* Return a SRS corresponding to a particular id. Note that */
1283 /* reference counting should be honoured on the returned */
1284 /* OGRSpatialReference, as handles may be cached. */
1285 /************************************************************************/
1286
FetchSRS(int nId)1287 OGRSpatialReference *OGRMSSQLSpatialDataSource::FetchSRS( int nId )
1288
1289 {
1290 if( nId <= 0 )
1291 return nullptr;
1292
1293 /* -------------------------------------------------------------------- */
1294 /* First, we look through our SRID cache, is it there? */
1295 /* -------------------------------------------------------------------- */
1296 int i;
1297
1298 for( i = 0; i < nKnownSRID; i++ )
1299 {
1300 if( panSRID[i] == nId )
1301 return papoSRS[i];
1302 }
1303
1304 EndCopy();
1305
1306 OGRSpatialReference *poSRS = nullptr;
1307
1308 /* -------------------------------------------------------------------- */
1309 /* Try looking up in spatial_ref_sys table */
1310 /* -------------------------------------------------------------------- */
1311 if (bUseGeometryColumns)
1312 {
1313 CPLODBCStatement oStmt( GetSession() );
1314 oStmt.Appendf( "SELECT srtext FROM spatial_ref_sys WHERE srid = %d", nId );
1315
1316 if( oStmt.ExecuteSQL() && oStmt.Fetch() )
1317 {
1318 if ( oStmt.GetColData( 0 ) )
1319 {
1320 poSRS = new OGRSpatialReference();
1321 poSRS->SetAxisMappingStrategy(OAMS_TRADITIONAL_GIS_ORDER);
1322 const char* pszWKT = oStmt.GetColData( 0 );
1323 if( poSRS->importFromWkt( pszWKT ) != OGRERR_NONE )
1324 {
1325 delete poSRS;
1326 poSRS = nullptr;
1327 }
1328 else
1329 {
1330 const char* pszAuthorityName = poSRS->GetAuthorityName(nullptr);
1331 const char* pszAuthorityCode = poSRS->GetAuthorityCode(nullptr);
1332 if( pszAuthorityName && pszAuthorityCode &&
1333 EQUAL(pszAuthorityName, "EPSG") )
1334 {
1335 const int nCode = atoi(pszAuthorityCode);
1336 poSRS->Clear();
1337 poSRS->importFromEPSG(nCode);
1338 }
1339 }
1340 }
1341 }
1342 }
1343
1344 /* -------------------------------------------------------------------- */
1345 /* Try looking up the EPSG list */
1346 /* -------------------------------------------------------------------- */
1347 if (!poSRS)
1348 {
1349 poSRS = new OGRSpatialReference();
1350 poSRS->SetAxisMappingStrategy(OAMS_TRADITIONAL_GIS_ORDER);
1351 if( poSRS->importFromEPSG( nId ) != OGRERR_NONE )
1352 {
1353 delete poSRS;
1354 poSRS = nullptr;
1355 }
1356 }
1357
1358 /* -------------------------------------------------------------------- */
1359 /* Add to the cache. */
1360 /* -------------------------------------------------------------------- */
1361 if (poSRS)
1362 {
1363 panSRID = (int *) CPLRealloc(panSRID,sizeof(int) * (nKnownSRID+1) );
1364 papoSRS = (OGRSpatialReference **)
1365 CPLRealloc(papoSRS, sizeof(void*) * (nKnownSRID + 1) );
1366 panSRID[nKnownSRID] = nId;
1367 papoSRS[nKnownSRID] = poSRS;
1368 nKnownSRID++;
1369 }
1370
1371 return poSRS;
1372 }
1373
1374 /************************************************************************/
1375 /* FetchSRSId() */
1376 /* */
1377 /* Fetch the id corresponding to an SRS, and if not found, add */
1378 /* it to the table. */
1379 /************************************************************************/
1380
FetchSRSId(OGRSpatialReference * poSRS)1381 int OGRMSSQLSpatialDataSource::FetchSRSId( OGRSpatialReference * poSRS)
1382
1383 {
1384 char *pszWKT = nullptr;
1385 int nSRSId = 0;
1386 const char* pszAuthorityName;
1387
1388 if( poSRS == nullptr )
1389 return 0;
1390
1391 OGRSpatialReference oSRS(*poSRS);
1392 // cppcheck-suppress uselessAssignmentPtrArg
1393 poSRS = nullptr;
1394
1395 pszAuthorityName = oSRS.GetAuthorityName(nullptr);
1396
1397 if( pszAuthorityName == nullptr || strlen(pszAuthorityName) == 0 )
1398 {
1399 /* -------------------------------------------------------------------- */
1400 /* Try to identify an EPSG code */
1401 /* -------------------------------------------------------------------- */
1402 oSRS.AutoIdentifyEPSG();
1403
1404 pszAuthorityName = oSRS.GetAuthorityName(nullptr);
1405 if (pszAuthorityName != nullptr && EQUAL(pszAuthorityName, "EPSG"))
1406 {
1407 const char* pszAuthorityCode = oSRS.GetAuthorityCode(nullptr);
1408 if ( pszAuthorityCode != nullptr && strlen(pszAuthorityCode) > 0 )
1409 {
1410 /* Import 'clean' SRS */
1411 oSRS.importFromEPSG( atoi(pszAuthorityCode) );
1412
1413 pszAuthorityName = oSRS.GetAuthorityName(nullptr);
1414 }
1415 }
1416 }
1417 /* -------------------------------------------------------------------- */
1418 /* Check whether the EPSG authority code is already mapped to a */
1419 /* SRS ID. */
1420 /* -------------------------------------------------------------------- */
1421 int nAuthorityCode = 0;
1422 if( pszAuthorityName != nullptr && EQUAL( pszAuthorityName, "EPSG" ) )
1423 {
1424 /* For the root authority name 'EPSG', the authority code
1425 * should always be integral
1426 */
1427 nAuthorityCode = atoi( oSRS.GetAuthorityCode(nullptr) );
1428
1429 CPLODBCStatement oStmt( &oSession );
1430 oStmt.Appendf("SELECT srid FROM spatial_ref_sys WHERE "
1431 "auth_name = '%s' AND auth_srid = %d",
1432 pszAuthorityName,
1433 nAuthorityCode );
1434
1435 if( oStmt.ExecuteSQL() && oStmt.Fetch() && oStmt.GetColData( 0 ) )
1436 {
1437 nSRSId = atoi(oStmt.GetColData( 0 ));
1438 return nSRSId;
1439 }
1440 }
1441
1442 /* -------------------------------------------------------------------- */
1443 /* Translate SRS to WKT. */
1444 /* -------------------------------------------------------------------- */
1445 if( oSRS.exportToWkt( &pszWKT ) != OGRERR_NONE )
1446 {
1447 CPLFree(pszWKT);
1448 return 0;
1449 }
1450
1451 /* -------------------------------------------------------------------- */
1452 /* Try to find in the existing table. */
1453 /* -------------------------------------------------------------------- */
1454 CPLODBCStatement oStmt( &oSession );
1455
1456 oStmt.Append( "SELECT srid FROM spatial_ref_sys WHERE srtext = ");
1457 OGRMSSQLAppendEscaped(&oStmt, pszWKT);
1458
1459 /* -------------------------------------------------------------------- */
1460 /* We got it! Return it. */
1461 /* -------------------------------------------------------------------- */
1462 if( oStmt.ExecuteSQL() )
1463 {
1464 if ( oStmt.Fetch() && oStmt.GetColData( 0 ) )
1465 {
1466 nSRSId = atoi(oStmt.GetColData( 0 ));
1467 CPLFree(pszWKT);
1468 return nSRSId;
1469 }
1470 }
1471 else
1472 {
1473 /* probably the table is missing at all */
1474 if( InitializeMetadataTables() != OGRERR_NONE )
1475 {
1476 CPLFree(pszWKT);
1477 return 0;
1478 }
1479 }
1480
1481 /* -------------------------------------------------------------------- */
1482 /* Try adding the SRS to the SRS table. */
1483 /* -------------------------------------------------------------------- */
1484 char *pszProj4 = nullptr;
1485 if( oSRS.exportToProj4( &pszProj4 ) != OGRERR_NONE )
1486 {
1487 CPLFree( pszProj4 );
1488 CPLFree(pszWKT);
1489 return 0;
1490 }
1491
1492 /* -------------------------------------------------------------------- */
1493 /* Check whether the auth_code can be used as srid. */
1494 /* -------------------------------------------------------------------- */
1495 nSRSId = nAuthorityCode;
1496
1497 oStmt.Clear();
1498
1499 int bInTransaction = oSession.IsInTransaction();
1500 if (!bInTransaction)
1501 oSession.BeginTransaction();
1502
1503 if (nAuthorityCode > 0)
1504 {
1505 oStmt.Appendf("SELECT srid FROM spatial_ref_sys where srid = %d", nAuthorityCode);
1506 if ( oStmt.ExecuteSQL() && oStmt.Fetch())
1507 {
1508 nSRSId = 0;
1509 }
1510 }
1511
1512 /* -------------------------------------------------------------------- */
1513 /* Get the current maximum srid in the srs table. */
1514 /* -------------------------------------------------------------------- */
1515
1516 if (nSRSId == 0)
1517 {
1518 oStmt.Clear();
1519 oStmt.Append("SELECT COALESCE(MAX(srid) + 1, 32768) FROM spatial_ref_sys where srid between 32768 and 65536");
1520
1521 if ( oStmt.ExecuteSQL() && oStmt.Fetch() && oStmt.GetColData( 0 ) )
1522 {
1523 nSRSId = atoi(oStmt.GetColData( 0 ));
1524 }
1525 }
1526
1527 if (nSRSId == 0)
1528 {
1529 /* unable to allocate srid */
1530 if (!bInTransaction)
1531 oSession.RollbackTransaction();
1532 CPLFree( pszProj4 );
1533 CPLFree(pszWKT);
1534 return 0;
1535 }
1536
1537 oStmt.Clear();
1538 if( nAuthorityCode > 0 )
1539 {
1540 oStmt.Appendf(
1541 "INSERT INTO spatial_ref_sys (srid, auth_srid, auth_name, srtext, proj4text) "
1542 "VALUES (%d, %d, ", nSRSId, nAuthorityCode );
1543 OGRMSSQLAppendEscaped(&oStmt, pszAuthorityName);
1544 oStmt.Append(", ");
1545 OGRMSSQLAppendEscaped(&oStmt, pszWKT);
1546 oStmt.Append(", ");
1547 OGRMSSQLAppendEscaped(&oStmt, pszProj4);
1548 oStmt.Append(")");
1549 }
1550 else
1551 {
1552 oStmt.Appendf(
1553 "INSERT INTO spatial_ref_sys (srid,srtext,proj4text) VALUES (%d, ", nSRSId);
1554 OGRMSSQLAppendEscaped(&oStmt, pszWKT);
1555 oStmt.Append(", ");
1556 OGRMSSQLAppendEscaped(&oStmt, pszProj4);
1557 oStmt.Append(")");
1558 }
1559
1560 /* Free everything that was allocated. */
1561 CPLFree( pszProj4 );
1562 CPLFree( pszWKT);
1563
1564 if ( oStmt.ExecuteSQL() )
1565 {
1566 if (!bInTransaction)
1567 oSession.CommitTransaction();
1568 }
1569 else
1570 {
1571 if (!bInTransaction)
1572 oSession.RollbackTransaction();
1573 }
1574
1575 return nSRSId;
1576 }
1577
1578 /************************************************************************/
1579 /* StartTransaction() */
1580 /* */
1581 /* Should only be called by user code. Not driver internals. */
1582 /************************************************************************/
1583
StartTransaction(CPL_UNUSED int bForce)1584 OGRErr OGRMSSQLSpatialDataSource::StartTransaction(CPL_UNUSED int bForce)
1585 {
1586 if (!oSession.BeginTransaction())
1587 {
1588 CPLError( CE_Failure, CPLE_AppDefined,
1589 "Failed to start transaction: %s", oSession.GetLastError() );
1590 return OGRERR_FAILURE;
1591 }
1592
1593 return OGRERR_NONE;
1594 }
1595
1596 /************************************************************************/
1597 /* CommitTransaction() */
1598 /* */
1599 /* Should only be called by user code. Not driver internals. */
1600 /************************************************************************/
1601
CommitTransaction()1602 OGRErr OGRMSSQLSpatialDataSource::CommitTransaction()
1603 {
1604 if (!oSession.CommitTransaction())
1605 {
1606 CPLError( CE_Failure, CPLE_AppDefined,
1607 "Failed to commit transaction: %s", oSession.GetLastError() );
1608
1609 for( int iLayer = 0; iLayer < nLayers; iLayer++ )
1610 {
1611 if( papoLayers[iLayer]->GetLayerStatus() == MSSQLLAYERSTATUS_INITIAL )
1612 papoLayers[iLayer]->SetLayerStatus(MSSQLLAYERSTATUS_DISABLED);
1613 }
1614 return OGRERR_FAILURE;
1615 }
1616
1617 /* set the status for the newly created layers */
1618 for( int iLayer = 0; iLayer < nLayers; iLayer++ )
1619 {
1620 if( papoLayers[iLayer]->GetLayerStatus() == MSSQLLAYERSTATUS_INITIAL )
1621 papoLayers[iLayer]->SetLayerStatus(MSSQLLAYERSTATUS_CREATED);
1622 }
1623
1624 return OGRERR_NONE;
1625 }
1626
1627 /************************************************************************/
1628 /* RollbackTransaction() */
1629 /* */
1630 /* Should only be called by user code. Not driver internals. */
1631 /************************************************************************/
1632
RollbackTransaction()1633 OGRErr OGRMSSQLSpatialDataSource::RollbackTransaction()
1634 {
1635 /* set the status for the newly created layers */
1636 for( int iLayer = 0; iLayer < nLayers; iLayer++ )
1637 {
1638 if( papoLayers[iLayer]->GetLayerStatus() == MSSQLLAYERSTATUS_INITIAL )
1639 papoLayers[iLayer]->SetLayerStatus(MSSQLLAYERSTATUS_DISABLED);
1640 }
1641
1642 if (!oSession.RollbackTransaction())
1643 {
1644 CPLError( CE_Failure, CPLE_AppDefined,
1645 "Failed to roll back transaction: %s", oSession.GetLastError() );
1646 return OGRERR_FAILURE;
1647 }
1648
1649 return OGRERR_NONE;
1650 }
1651
1652 /************************************************************************/
1653 /* StartCopy() */
1654 /************************************************************************/
1655
StartCopy(OGRMSSQLSpatialTableLayer * poMSSQLSpatialLayer)1656 void OGRMSSQLSpatialDataSource::StartCopy(OGRMSSQLSpatialTableLayer *poMSSQLSpatialLayer)
1657 {
1658 if (poLayerInCopyMode == poMSSQLSpatialLayer)
1659 return;
1660 EndCopy();
1661 poLayerInCopyMode = poMSSQLSpatialLayer;
1662 poLayerInCopyMode->StartCopy();
1663 }
1664
1665 /************************************************************************/
1666 /* EndCopy() */
1667 /************************************************************************/
1668
EndCopy()1669 OGRErr OGRMSSQLSpatialDataSource::EndCopy()
1670 {
1671 if (poLayerInCopyMode != nullptr)
1672 {
1673 OGRErr result = poLayerInCopyMode->EndCopy();
1674 poLayerInCopyMode = nullptr;
1675
1676 return result;
1677 }
1678 else
1679 return OGRERR_NONE;
1680 }
1681