1 /******************************************************************************
2 *
3 * Project: MSSQL Spatial driver
4 * Purpose: Implements OGRMSSQLSpatialTableLayer class, access to an existing table.
5 * Author: Tamas Szekeres, szekerest at gmail.com
6 *
7 ******************************************************************************
8 * Copyright (c) 2010, Tamas Szekeres
9 * Copyright (c) 2010-2012, 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 "cpl_conv.h"
31 #include "ogr_mssqlspatial.h"
32 #include "ogr_p.h"
33
34 #include <memory>
35
36 CPL_CVSID("$Id: ogrmssqlspatialtablelayer.cpp 3798cbe48457b7127606931896549f26507469db 2021-04-09 15:04:16 +0200 Even Rouault $")
37
38 #define UNSUPPORTED_OP_READ_ONLY "%s : unsupported operation on a read-only datasource."
39
40 /************************************************************************/
41 /* OGRMSSQLAppendEscaped( ) */
42 /************************************************************************/
43
OGRMSSQLAppendEscaped(CPLODBCStatement * poStatement,const char * pszStrValue)44 void OGRMSSQLAppendEscaped( CPLODBCStatement* poStatement, const char* pszStrValue)
45 {
46 if (!pszStrValue)
47 {
48 poStatement->Append("null");
49 return;
50 }
51
52 size_t iIn, iOut , nTextLen = strlen(pszStrValue);
53 char *pszEscapedText = (char *) CPLMalloc(nTextLen*2 + 3);
54
55 pszEscapedText[0] = '\'';
56
57 for( iIn = 0, iOut = 1; iIn < nTextLen; iIn++ )
58 {
59 switch( pszStrValue[iIn] )
60 {
61 case '\'':
62 pszEscapedText[iOut++] = '\''; // double quote
63 pszEscapedText[iOut++] = pszStrValue[iIn];
64 break;
65
66 default:
67 pszEscapedText[iOut++] = pszStrValue[iIn];
68 break;
69 }
70 }
71
72 pszEscapedText[iOut++] = '\'';
73
74 pszEscapedText[iOut] = '\0';
75
76 poStatement->Append(pszEscapedText);
77
78 CPLFree( pszEscapedText );
79 }
80
81 /************************************************************************/
82 /* OGRMSSQLSpatialTableLayer() */
83 /************************************************************************/
84
OGRMSSQLSpatialTableLayer(OGRMSSQLSpatialDataSource * poDSIn)85 OGRMSSQLSpatialTableLayer::OGRMSSQLSpatialTableLayer( OGRMSSQLSpatialDataSource *poDSIn )
86 {
87 poDS = poDSIn;
88 bUseGeometryValidation = CPLTestBool(CPLGetConfigOption("MSSQLSPATIAL_USE_GEOMETRY_VALIDATION", "YES"));
89 }
90
91 /************************************************************************/
92 /* ~OGRMSSQLSpatialTableLayer() */
93 /************************************************************************/
94
~OGRMSSQLSpatialTableLayer()95 OGRMSSQLSpatialTableLayer::~OGRMSSQLSpatialTableLayer()
96
97 {
98 #ifdef MSSQL_BCP_SUPPORTED
99 CloseBCP();
100 #endif
101
102 if ( bNeedSpatialIndex && nLayerStatus == MSSQLLAYERSTATUS_CREATED )
103 {
104 /* recreate spatial index */
105 DropSpatialIndex();
106 CreateSpatialIndex();
107 }
108
109 CPLFree( pszTableName );
110 CPLFree( pszLayerName );
111 CPLFree( pszSchemaName );
112
113 CPLFree( pszQuery );
114 ClearStatement();
115 }
116
117 /************************************************************************/
118 /* GetName() */
119 /************************************************************************/
120
GetName()121 const char *OGRMSSQLSpatialTableLayer::GetName()
122
123 {
124 return pszLayerName;
125 }
126
127 /************************************************************************/
128 /* GetLayerDefn() */
129 /************************************************************************/
GetLayerDefn()130 OGRFeatureDefn* OGRMSSQLSpatialTableLayer::GetLayerDefn()
131 {
132 if (poFeatureDefn)
133 return poFeatureDefn;
134
135 CPLODBCSession *poSession = poDS->GetSession();
136 /* -------------------------------------------------------------------- */
137 /* Do we have a simple primary key? */
138 /* -------------------------------------------------------------------- */
139 CPLODBCStatement oGetKey( poSession );
140
141 if( oGetKey.GetPrimaryKeys( pszTableName, poDS->GetCatalog(), pszSchemaName )
142 && oGetKey.Fetch() )
143 {
144 pszFIDColumn = CPLStrdup(oGetKey.GetColData( 3 ));
145
146 if( oGetKey.Fetch() ) // more than one field in key!
147 {
148 oGetKey.Clear();
149 CPLFree( pszFIDColumn );
150 pszFIDColumn = nullptr;
151
152 CPLDebug( "OGR_MSSQLSpatial", "Table %s has multiple primary key fields, "
153 "ignoring them all.", pszTableName );
154 }
155 }
156
157 /* -------------------------------------------------------------------- */
158 /* Get the column definitions for this table. */
159 /* -------------------------------------------------------------------- */
160 CPLODBCStatement oGetCol( poSession );
161 CPLErr eErr;
162
163 if( !oGetCol.GetColumns( pszTableName, poDS->GetCatalog(), pszSchemaName ) )
164 return nullptr;
165
166 eErr = BuildFeatureDefn( pszLayerName, &oGetCol );
167 if( eErr != CE_None )
168 return nullptr;
169
170 if (eGeomType != wkbNone)
171 poFeatureDefn->SetGeomType(eGeomType);
172
173 if ( GetSpatialRef() && poFeatureDefn->GetGeomFieldCount() == 1)
174 poFeatureDefn->GetGeomFieldDefn(0)->SetSpatialRef( poSRS );
175
176 if( poFeatureDefn->GetFieldCount() == 0 &&
177 pszFIDColumn == nullptr && pszGeomColumn == nullptr )
178 {
179 CPLError( CE_Failure, CPLE_AppDefined,
180 "No column definitions found for table '%s', layer not usable.",
181 pszLayerName );
182 return nullptr;
183 }
184
185 /* -------------------------------------------------------------------- */
186 /* If we got a geometry column, does it exist? Is it binary? */
187 /* -------------------------------------------------------------------- */
188 if( pszGeomColumn != nullptr )
189 {
190 int iColumn = oGetCol.GetColId( pszGeomColumn );
191 if( iColumn < 0 )
192 {
193 CPLError( CE_Failure, CPLE_AppDefined,
194 "Column %s requested for geometry, but it does not exist.",
195 pszGeomColumn );
196 CPLFree( pszGeomColumn );
197 pszGeomColumn = nullptr;
198 }
199 else
200 {
201 if ( nGeomColumnType < 0 )
202 {
203 /* last attempt to identify the geometry column type */
204 if ( EQUAL(oGetCol.GetColTypeName( iColumn ), "geometry") )
205 nGeomColumnType = MSSQLCOLTYPE_GEOMETRY;
206 else if ( EQUAL(oGetCol.GetColTypeName( iColumn ), "geography") )
207 nGeomColumnType = MSSQLCOLTYPE_GEOGRAPHY;
208 else if ( EQUAL(oGetCol.GetColTypeName( iColumn ), "varchar") )
209 nGeomColumnType = MSSQLCOLTYPE_TEXT;
210 else if ( EQUAL(oGetCol.GetColTypeName( iColumn ), "nvarchar") )
211 nGeomColumnType = MSSQLCOLTYPE_TEXT;
212 else if ( EQUAL(oGetCol.GetColTypeName( iColumn ), "text") )
213 nGeomColumnType = MSSQLCOLTYPE_TEXT;
214 else if ( EQUAL(oGetCol.GetColTypeName( iColumn ), "ntext") )
215 nGeomColumnType = MSSQLCOLTYPE_TEXT;
216 else if ( EQUAL(oGetCol.GetColTypeName( iColumn ), "image") )
217 nGeomColumnType = MSSQLCOLTYPE_BINARY;
218 else
219 {
220 CPLError( CE_Failure, CPLE_AppDefined,
221 "Column type %s is not supported for geometry column.",
222 oGetCol.GetColTypeName( iColumn ) );
223 CPLFree( pszGeomColumn );
224 pszGeomColumn = nullptr;
225 }
226 }
227 }
228 }
229
230 return poFeatureDefn;
231 }
232
233 /************************************************************************/
234 /* Initialize() */
235 /************************************************************************/
236
Initialize(const char * pszSchema,const char * pszLayerNameIn,const char * pszGeomCol,CPL_UNUSED int nCoordDimension,int nSRId,const char * pszSRText,OGRwkbGeometryType eType)237 CPLErr OGRMSSQLSpatialTableLayer::Initialize( const char *pszSchema,
238 const char *pszLayerNameIn,
239 const char *pszGeomCol,
240 CPL_UNUSED int nCoordDimension,
241 int nSRId,
242 const char *pszSRText,
243 OGRwkbGeometryType eType )
244 {
245 CPLFree( pszFIDColumn );
246 pszFIDColumn = nullptr;
247
248 /* -------------------------------------------------------------------- */
249 /* Parse out schema name if present in layer. We assume a */
250 /* schema is provided if there is a dot in the name, and that */
251 /* it is in the form <schema>.<tablename> */
252 /* -------------------------------------------------------------------- */
253 const char *pszDot = strstr(pszLayerNameIn,".");
254 if( pszDot != nullptr )
255 {
256 pszTableName = CPLStrdup(pszDot + 1);
257 if (pszSchema == nullptr)
258 {
259 pszSchemaName = CPLStrdup(pszLayerNameIn);
260 pszSchemaName[pszDot - pszLayerNameIn] = '\0';
261 }
262 else
263 pszSchemaName = CPLStrdup(pszSchema);
264
265 this->pszLayerName = CPLStrdup(pszLayerNameIn);
266 }
267 else
268 {
269 pszTableName = CPLStrdup(pszLayerNameIn);
270 if ( pszSchema == nullptr || EQUAL(pszSchema, "dbo") )
271 {
272 pszSchemaName = CPLStrdup("dbo");
273 this->pszLayerName = CPLStrdup(pszLayerNameIn);
274 }
275 else
276 {
277 pszSchemaName = CPLStrdup(pszSchema);
278 this->pszLayerName = CPLStrdup(CPLSPrintf("%s.%s", pszSchemaName, pszTableName));
279 }
280 }
281 SetDescription( this->pszLayerName );
282
283 /* -------------------------------------------------------------------- */
284 /* Have we been provided a geometry column? */
285 /* -------------------------------------------------------------------- */
286 CPLFree( pszGeomColumn );
287 if( pszGeomCol == nullptr )
288 GetLayerDefn(); /* fetch geom column if not specified */
289 else
290 pszGeomColumn = CPLStrdup( pszGeomCol );
291
292 if (eType != wkbNone)
293 eGeomType = eType;
294
295 /* -------------------------------------------------------------------- */
296 /* Try to find out the spatial reference */
297 /* -------------------------------------------------------------------- */
298
299 nSRSId = nSRId;
300
301 if (pszSRText)
302 {
303 /* Process srtext directly if specified */
304 poSRS = new OGRSpatialReference();
305 poSRS->SetAxisMappingStrategy(OAMS_TRADITIONAL_GIS_ORDER);
306 if( poSRS->importFromWkt( pszSRText ) != OGRERR_NONE )
307 {
308 delete poSRS;
309 poSRS = nullptr;
310 }
311 else
312 {
313 const char* pszAuthorityName = poSRS->GetAuthorityName(nullptr);
314 const char* pszAuthorityCode = poSRS->GetAuthorityCode(nullptr);
315 if( pszAuthorityName && pszAuthorityCode &&
316 EQUAL(pszAuthorityName, "EPSG") )
317 {
318 const int nCode = atoi(pszAuthorityCode);
319 poSRS->Clear();
320 poSRS->importFromEPSG(nCode);
321 }
322 }
323 }
324
325 if (!poSRS)
326 {
327 if (nSRSId <= 0)
328 nSRSId = FetchSRSId();
329
330 GetSpatialRef();
331 }
332
333 if (nSRSId < 0)
334 nSRSId = 0;
335
336 return CE_None;
337 }
338
339 /************************************************************************/
340 /* FetchSRSId() */
341 /************************************************************************/
342
FetchSRSId()343 int OGRMSSQLSpatialTableLayer::FetchSRSId()
344 {
345 if ( poDS->UseGeometryColumns() )
346 {
347 CPLODBCStatement oStatement( poDS->GetSession() );
348 oStatement.Appendf( "select srid from geometry_columns "
349 "where f_table_schema = '%s' and f_table_name = '%s'",
350 pszSchemaName, pszTableName );
351
352 if( oStatement.ExecuteSQL() && oStatement.Fetch() )
353 {
354 if ( oStatement.GetColData( 0 ) )
355 nSRSId = atoi( oStatement.GetColData( 0 ) );
356 if( nSRSId < 0 )
357 nSRSId = 0;
358 }
359 }
360
361 return nSRSId;
362 }
363
364 /************************************************************************/
365 /* CreateSpatialIndex() */
366 /* */
367 /* Create a spatial index on the geometry column of the layer */
368 /************************************************************************/
369
CreateSpatialIndex()370 OGRErr OGRMSSQLSpatialTableLayer::CreateSpatialIndex()
371 {
372 OGRMSSQLSpatialTableLayer::GetLayerDefn();
373
374 if (pszGeomColumn == nullptr)
375 {
376 CPLError(CE_Warning, CPLE_AppDefined,
377 "No geometry column found.");
378 return OGRERR_FAILURE;
379 }
380
381 CPLODBCStatement oStatement( poDS->GetSession() );
382
383 if (nGeomColumnType == MSSQLCOLTYPE_GEOMETRY)
384 {
385 OGREnvelope oExt;
386 if (GetExtent(&oExt, TRUE) != OGRERR_NONE)
387 {
388 CPLError( CE_Warning, CPLE_AppDefined,
389 "Failed to get extent for spatial index." );
390 return OGRERR_FAILURE;
391 }
392
393 if (oExt.MinX == oExt.MaxX || oExt.MinY == oExt.MaxY)
394 return OGRERR_NONE; /* skip creating index */
395
396 oStatement.Appendf("CREATE SPATIAL INDEX [ogr_%s_%s_%s_sidx] ON [%s].[%s] ( [%s] ) "
397 "USING GEOMETRY_GRID WITH (BOUNDING_BOX =(%.15g, %.15g, %.15g, %.15g))",
398 pszSchemaName, pszTableName, pszGeomColumn,
399 pszSchemaName, pszTableName, pszGeomColumn,
400 oExt.MinX, oExt.MinY, oExt.MaxX, oExt.MaxY );
401 }
402 else if (nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY)
403 {
404 oStatement.Appendf("CREATE SPATIAL INDEX [ogr_%s_%s_%s_sidx] ON [%s].[%s] ( [%s] ) "
405 "USING GEOGRAPHY_GRID",
406 pszSchemaName, pszTableName, pszGeomColumn,
407 pszSchemaName, pszTableName, pszGeomColumn );
408 }
409 else
410 {
411 CPLError( CE_Failure, CPLE_AppDefined,
412 "Spatial index is not supported on the geometry column '%s'", pszGeomColumn);
413 return OGRERR_FAILURE;
414 }
415
416 if( !oStatement.ExecuteSQL() )
417 {
418 CPLError( CE_Failure, CPLE_AppDefined,
419 "Failed to create the spatial index, %s.",
420 poDS->GetSession()->GetLastError());
421 return OGRERR_FAILURE;
422 }
423
424 return OGRERR_NONE;
425 }
426
427 /************************************************************************/
428 /* DropSpatialIndex() */
429 /* */
430 /* Drop the spatial index on the geometry column of the layer */
431 /************************************************************************/
432
DropSpatialIndex()433 void OGRMSSQLSpatialTableLayer::DropSpatialIndex()
434 {
435 OGRMSSQLSpatialTableLayer::GetLayerDefn();
436
437 CPLODBCStatement oStatement( poDS->GetSession() );
438
439 oStatement.Appendf("IF EXISTS (SELECT * FROM sys.indexes "
440 "WHERE object_id = OBJECT_ID(N'[%s].[%s]') AND name = N'ogr_%s_%s_%s_sidx') "
441 "DROP INDEX [ogr_%s_%s_%s_sidx] ON [%s].[%s]",
442 pszSchemaName, pszTableName,
443 pszSchemaName, pszTableName, pszGeomColumn,
444 pszSchemaName, pszTableName, pszGeomColumn,
445 pszSchemaName, pszTableName );
446
447 if( !oStatement.ExecuteSQL() )
448 {
449 CPLError( CE_Failure, CPLE_AppDefined,
450 "Failed to drop the spatial index, %s.",
451 poDS->GetSession()->GetLastError());
452 return;
453 }
454 }
455
456 /************************************************************************/
457 /* BuildFields() */
458 /* */
459 /* Build list of fields to fetch, performing any required */
460 /* transformations (such as on geometry). */
461 /************************************************************************/
462
BuildFields()463 CPLString OGRMSSQLSpatialTableLayer::BuildFields()
464
465 {
466 int nColumn = 0;
467 CPLString osFieldList;
468
469 GetLayerDefn();
470
471 if( pszFIDColumn && poFeatureDefn->GetFieldIndex( pszFIDColumn ) == -1 )
472 {
473 /* Always get the FID column */
474 osFieldList += "[";
475 osFieldList += pszFIDColumn;
476 osFieldList += "]";
477 ++nColumn;
478 }
479
480 if( pszGeomColumn && !poFeatureDefn->IsGeometryIgnored())
481 {
482 if( nColumn > 0 )
483 osFieldList += ", ";
484
485 osFieldList += "[";
486 osFieldList += pszGeomColumn;
487 if (nGeomColumnType == MSSQLCOLTYPE_GEOMETRY ||
488 nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY)
489 {
490 if ( poDS->GetGeometryFormat() == MSSQLGEOMETRY_WKB )
491 {
492 osFieldList += "].STAsBinary() as [";
493 osFieldList += pszGeomColumn;
494 }
495 else if ( poDS->GetGeometryFormat() == MSSQLGEOMETRY_WKT )
496 {
497 osFieldList += "].AsTextZM() as [";
498 osFieldList += pszGeomColumn;
499 }
500 else if ( poDS->GetGeometryFormat() == MSSQLGEOMETRY_WKBZM )
501 {
502 /* SQL Server 2012 */
503 osFieldList += "].AsBinaryZM() as [";
504 osFieldList += pszGeomColumn;
505 }
506 }
507 osFieldList += "]";
508
509 ++nColumn;
510 }
511
512 if (poFeatureDefn->GetFieldCount() > 0)
513 {
514 /* need to reconstruct the field ordinals list */
515 CPLFree(panFieldOrdinals);
516 panFieldOrdinals = (int *) CPLMalloc( sizeof(int) * poFeatureDefn->GetFieldCount() );
517
518 for( int i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
519 {
520 if ( poFeatureDefn->GetFieldDefn(i)->IsIgnored() )
521 continue;
522
523 const char *pszName = poFeatureDefn->GetFieldDefn(i)->GetNameRef();
524
525 if( nColumn > 0 )
526 osFieldList += ", ";
527
528 osFieldList += "[";
529 osFieldList += pszName;
530 osFieldList += "]";
531
532 panFieldOrdinals[i] = nColumn;
533
534 ++nColumn;
535 }
536 }
537
538 return osFieldList;
539 }
540
541 /************************************************************************/
542 /* GetStatement() */
543 /************************************************************************/
544
GetStatement()545 CPLODBCStatement *OGRMSSQLSpatialTableLayer::GetStatement()
546
547 {
548 if( poStmt == nullptr )
549 {
550 poStmt = BuildStatement(BuildFields());
551 }
552
553 return poStmt;
554 }
555
556 /************************************************************************/
557 /* BuildStatement() */
558 /************************************************************************/
559
BuildStatement(const char * pszColumns)560 CPLODBCStatement* OGRMSSQLSpatialTableLayer::BuildStatement(const char* pszColumns)
561
562 {
563 CPLODBCStatement* poStatement = new CPLODBCStatement( poDS->GetSession() );
564 poStatement->Append( "select " );
565 poStatement->Append( pszColumns );
566 poStatement->Append( " from [" );
567 poStatement->Append( pszSchemaName );
568 poStatement->Append( "].[" );
569 poStatement->Append( pszTableName );
570 poStatement->Append( "]" );
571
572 /* Append attribute query if we have it */
573 if( pszQuery != nullptr )
574 poStatement->Appendf( " where (%s)", pszQuery );
575
576 /* If we have a spatial filter, query on it */
577 if ( m_poFilterGeom != nullptr )
578 {
579 if (nGeomColumnType == MSSQLCOLTYPE_GEOMETRY
580 || nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY)
581 {
582 if( !CPLIsInf(m_sFilterEnvelope.MinX) &&
583 !CPLIsInf(m_sFilterEnvelope.MinY) &&
584 !CPLIsInf(m_sFilterEnvelope.MaxX) &&
585 !CPLIsInf(m_sFilterEnvelope.MaxY) )
586 {
587 if( pszQuery == nullptr )
588 poStatement->Append( " where" );
589 else
590 poStatement->Append( " and" );
591
592 poStatement->Appendf(" [%s].STIntersects(", pszGeomColumn );
593
594 if (nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY)
595 poStatement->Append( "geography::" );
596 else
597 poStatement->Append( "geometry::" );
598
599 if ( m_sFilterEnvelope.MinX == m_sFilterEnvelope.MaxX ||
600 m_sFilterEnvelope.MinY == m_sFilterEnvelope.MaxY)
601 poStatement->Appendf("STGeomFromText('POINT(%.15g %.15g)',%d)) = 1",
602 m_sFilterEnvelope.MinX, m_sFilterEnvelope.MinY, nSRSId);
603 else
604 poStatement->Appendf( "STGeomFromText('POLYGON((%.15g %.15g,%.15g %.15g,%.15g %.15g,%.15g %.15g,%.15g %.15g))',%d)) = 1",
605 m_sFilterEnvelope.MinX, m_sFilterEnvelope.MinY,
606 m_sFilterEnvelope.MaxX, m_sFilterEnvelope.MinY,
607 m_sFilterEnvelope.MaxX, m_sFilterEnvelope.MaxY,
608 m_sFilterEnvelope.MinX, m_sFilterEnvelope.MaxY,
609 m_sFilterEnvelope.MinX, m_sFilterEnvelope.MinY,
610 nSRSId );
611 }
612 }
613 else
614 {
615 CPLError( CE_Failure, CPLE_AppDefined,
616 "Spatial filter is supported only on geometry and geography column types." );
617
618 delete poStatement;
619 return nullptr;
620 }
621 }
622
623 CPLDebug( "OGR_MSSQLSpatial", "ExecuteSQL(%s)", poStatement->GetCommand() );
624 if( poStatement->ExecuteSQL() )
625 return poStatement;
626 else
627 {
628 delete poStatement;
629 return nullptr;
630 }
631 }
632
633 /************************************************************************/
634 /* GetFeature() */
635 /************************************************************************/
636
GetFeature(GIntBig nFeatureId)637 OGRFeature *OGRMSSQLSpatialTableLayer::GetFeature( GIntBig nFeatureId )
638
639 {
640 if( pszFIDColumn == nullptr )
641 return OGRMSSQLSpatialLayer::GetFeature( nFeatureId );
642
643 poDS->EndCopy();
644
645 ClearStatement();
646
647 iNextShapeId = nFeatureId;
648
649 m_bResetNeeded = true;
650 poStmt = new CPLODBCStatement( poDS->GetSession() );
651 CPLString osFields = BuildFields();
652 poStmt->Appendf( "select %s from %s where %s = " CPL_FRMT_GIB, osFields.c_str(),
653 poFeatureDefn->GetName(), pszFIDColumn, nFeatureId );
654
655 if( !poStmt->ExecuteSQL() )
656 {
657 delete poStmt;
658 poStmt = nullptr;
659 return nullptr;
660 }
661
662 return GetNextRawFeature();
663 }
664
665
666 /************************************************************************/
667 /* GetExtent() */
668 /* */
669 /* For Geometry or Geography types we can use an optimized */
670 /* statement in other cases we use standard OGRLayer::GetExtent() */
671 /************************************************************************/
672
GetExtent(int iGeomField,OGREnvelope * psExtent,int bForce)673 OGRErr OGRMSSQLSpatialTableLayer::GetExtent(int iGeomField, OGREnvelope *psExtent, int bForce)
674 {
675 // Make sure we have a geometry field:
676 if (iGeomField < 0 || iGeomField >= poFeatureDefn->GetGeomFieldCount() ||
677 poFeatureDefn->GetGeomFieldDefn(iGeomField)->GetType() == wkbNone)
678 {
679 if (iGeomField != 0)
680 {
681 CPLError(CE_Failure, CPLE_AppDefined,
682 "Invalid geometry field index : %d", iGeomField);
683 }
684 return OGRERR_FAILURE;
685 }
686
687 // If we have a geometry or geography type:
688 if (nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY || nGeomColumnType == MSSQLCOLTYPE_GEOMETRY)
689 {
690 // Prepare statement
691 auto poStatement = std::unique_ptr<CPLODBCStatement>(new CPLODBCStatement(poDS->GetSession()));
692
693 if (poDS->sMSSQLVersion.nMajor >= 11) {
694 // SQLServer 2012 or later:
695 // geography is converted to geometry to obtain the rectangular envelope
696 if (nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY)
697 poStatement->Appendf("WITH extent(extentcol) AS (SELECT geometry::EnvelopeAggregate(geometry::STGeomFromWKB(%s.STAsBinary(), %s.STSrid).MakeValid()) as extentcol FROM [%s].[%s])", pszGeomColumn, pszGeomColumn, pszSchemaName, pszTableName);
698 else
699 poStatement->Appendf("WITH extent(extentcol) AS (SELECT geometry::EnvelopeAggregate(%s.MakeValid()) AS extentcol FROM [%s].[%s])", pszGeomColumn, pszSchemaName, pszTableName);
700
701 poStatement->Appendf("SELECT extentcol.STPointN(1).STX, extentcol.STPointN(1).STY,");
702 poStatement->Appendf("extentcol.STPointN(3).STX, extentcol.STPointN(3).STY FROM extent;");
703 }
704 else
705 {
706 // Before 2012 use two CTE's:
707 // geography is converted to geometry to obtain the envelope
708 if (nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY)
709 poStatement->Appendf("WITH ENVELOPE as (SELECT geometry::STGeomFromWKB(%s.STAsBinary(), %s.STSrid).MakeValid().STEnvelope() as envelope from [%s].[%s]),", pszGeomColumn, pszGeomColumn, pszSchemaName, pszTableName);
710 else
711 poStatement->Appendf("WITH ENVELOPE as (SELECT %s.MakeValid().STEnvelope() as envelope from [%s].[%s]),", pszGeomColumn, pszSchemaName, pszTableName);
712
713 poStatement->Appendf(" CORNERS as (SELECT envelope.STPointN(1) as point from ENVELOPE UNION ALL select envelope.STPointN(3) from ENVELOPE)");
714 poStatement->Appendf("SELECT MIN(point.STX), MIN(point.STY), MAX(point.STX), MAX(point.STY) FROM CORNERS;");
715 }
716
717 // Execute
718 if (!poStatement->ExecuteSQL())
719 {
720 CPLError(CE_Failure, CPLE_AppDefined,
721 "Error getting extents, %s",
722 poDS->GetSession()->GetLastError());
723 }
724 else
725 {
726 // Try to update
727 while (poStatement->Fetch()) {
728
729 const char *minx = poStatement->GetColData(0);
730 const char *miny = poStatement->GetColData(1);
731 const char *maxx = poStatement->GetColData(2);
732 const char *maxy = poStatement->GetColData(3);
733
734 if (!(minx == nullptr || miny == nullptr || maxx == nullptr || maxy == nullptr)) {
735 psExtent->MinX = CPLAtof(minx);
736 psExtent->MinY = CPLAtof(miny);
737 psExtent->MaxX = CPLAtof(maxx);
738 psExtent->MaxY = CPLAtof(maxy);
739 return OGRERR_NONE;
740 }
741 else
742 {
743 CPLError(CE_Failure, CPLE_AppDefined,
744 "MSSQL extents query returned a NULL value");
745 }
746 }
747 }
748
749 }
750
751 // Fall back to generic implementation (loading all features)
752 if (iGeomField == 0)
753 return OGRLayer::GetExtent(psExtent, bForce);
754 else
755 return OGRLayer::GetExtent(iGeomField, psExtent, bForce);
756 }
757
758 /************************************************************************/
759 /* SetAttributeFilter() */
760 /************************************************************************/
761
SetAttributeFilter(const char * pszQueryIn)762 OGRErr OGRMSSQLSpatialTableLayer::SetAttributeFilter( const char *pszQueryIn )
763
764 {
765 CPLFree(m_pszAttrQueryString);
766 m_pszAttrQueryString = (pszQueryIn) ? CPLStrdup(pszQueryIn) : nullptr;
767
768 if( (pszQueryIn == nullptr && this->pszQuery == nullptr)
769 || (pszQueryIn != nullptr && this->pszQuery != nullptr
770 && EQUAL(pszQueryIn,this->pszQuery)) )
771 return OGRERR_NONE;
772
773 CPLFree( this->pszQuery );
774 this->pszQuery = (pszQueryIn) ? CPLStrdup( pszQueryIn ) : nullptr;
775
776 ClearStatement();
777
778 return OGRERR_NONE;
779 }
780
781 /************************************************************************/
782 /* TestCapability() */
783 /************************************************************************/
784
TestCapability(const char * pszCap)785 int OGRMSSQLSpatialTableLayer::TestCapability( const char * pszCap )
786
787 {
788 if ( bUpdateAccess )
789 {
790 if( EQUAL(pszCap,OLCSequentialWrite) || EQUAL(pszCap,OLCCreateField)
791 || EQUAL(pszCap,OLCDeleteFeature) )
792 return TRUE;
793
794 else if( EQUAL(pszCap,OLCRandomWrite) )
795 return pszFIDColumn != nullptr;
796 }
797
798 #if (ODBCVER >= 0x0300)
799 if( EQUAL(pszCap,OLCTransactions) )
800 return TRUE;
801 #else
802 if( EQUAL(pszCap,OLCTransactions) )
803 return FALSE;
804 #endif
805
806 if( EQUAL(pszCap,OLCIgnoreFields) )
807 return TRUE;
808
809 if( EQUAL(pszCap,OLCRandomRead) )
810 return pszFIDColumn != nullptr;
811 else if( EQUAL(pszCap,OLCFastFeatureCount) )
812 return TRUE;
813 else if (EQUAL(pszCap, OLCCurveGeometries))
814 return TRUE;
815 else if (EQUAL(pszCap, OLCMeasuredGeometries))
816 return TRUE;
817 else
818 return OGRMSSQLSpatialLayer::TestCapability( pszCap );
819 }
820
821 /************************************************************************/
822 /* GetFeatureCount() */
823 /************************************************************************/
824
GetFeatureCount(int bForce)825 GIntBig OGRMSSQLSpatialTableLayer::GetFeatureCount( int bForce )
826
827 {
828 poDS->EndCopy();
829
830 GetLayerDefn();
831
832 if( TestCapability(OLCFastFeatureCount) == FALSE )
833 return OGRMSSQLSpatialLayer::GetFeatureCount( bForce );
834
835 CPLODBCStatement* poStatement = BuildStatement( "count(*)" );
836
837 if (poStatement == nullptr || !poStatement->Fetch())
838 {
839 delete poStatement;
840 return OGRMSSQLSpatialLayer::GetFeatureCount( bForce );
841 }
842
843 GIntBig nRet = CPLAtoGIntBig(poStatement->GetColData( 0 ));
844 delete poStatement;
845 return nRet;
846 }
847
848 /************************************************************************/
849 /* StartCopy() */
850 /************************************************************************/
851
StartCopy()852 OGRErr OGRMSSQLSpatialTableLayer::StartCopy()
853
854 {
855 return OGRERR_NONE;
856 }
857
858 /************************************************************************/
859 /* EndCopy() */
860 /************************************************************************/
861
EndCopy()862 OGRErr OGRMSSQLSpatialTableLayer::EndCopy()
863
864 {
865 #ifdef MSSQL_BCP_SUPPORTED
866 CloseBCP();
867 #endif
868 return OGRERR_NONE;
869 }
870
871 /************************************************************************/
872 /* CreateField() */
873 /************************************************************************/
874
CreateField(OGRFieldDefn * poFieldIn,int bApproxOK)875 OGRErr OGRMSSQLSpatialTableLayer::CreateField( OGRFieldDefn *poFieldIn,
876 int bApproxOK )
877
878 {
879 char szFieldType[256];
880 OGRFieldDefn oField( poFieldIn );
881
882 poDS->EndCopy();
883
884 GetLayerDefn();
885
886 /* -------------------------------------------------------------------- */
887 /* Do we want to "launder" the column names into MSSQL */
888 /* friendly format? */
889 /* -------------------------------------------------------------------- */
890 if( bLaunderColumnNames )
891 {
892 char *pszSafeName = poDS->LaunderName( oField.GetNameRef() );
893
894 oField.SetName( pszSafeName );
895 CPLFree( pszSafeName );
896 }
897
898 /* -------------------------------------------------------------------- */
899 /* Identify the MSSQL type. */
900 /* -------------------------------------------------------------------- */
901
902 if( oField.GetType() == OFTInteger )
903 {
904 if( oField.GetWidth() > 0 && bPreservePrecision )
905 snprintf( szFieldType, sizeof(szFieldType), "numeric(%d,0)", oField.GetWidth() );
906 else
907 strcpy( szFieldType, "int" );
908 }
909 else if( oField.GetType() == OFTInteger64 )
910 {
911 if( oField.GetWidth() > 0 && bPreservePrecision )
912 snprintf( szFieldType, sizeof(szFieldType), "numeric(%d,0)", oField.GetWidth() );
913 else
914 strcpy( szFieldType, "bigint" );
915 }
916 else if( oField.GetType() == OFTReal )
917 {
918 if( oField.GetWidth() > 0 && oField.GetPrecision() > 0
919 && bPreservePrecision )
920 snprintf( szFieldType, sizeof(szFieldType), "numeric(%d,%d)",
921 oField.GetWidth(), oField.GetPrecision() );
922 else
923 strcpy( szFieldType, "float" );
924 }
925 else if( oField.GetType() == OFTString )
926 {
927 if( oField.GetSubType() == OGRFieldSubType::OFSTUUID)
928 strcpy( szFieldType, "uniqueidentifier" );
929 else if( oField.GetWidth() == 0 || oField.GetWidth() > 4000 || !bPreservePrecision )
930 strcpy( szFieldType, "nvarchar(MAX)" );
931 else
932 snprintf( szFieldType, sizeof(szFieldType), "nvarchar(%d)", oField.GetWidth() );
933 }
934 else if( oField.GetType() == OFTDate )
935 {
936 strcpy( szFieldType, "date" );
937 }
938 else if( oField.GetType() == OFTTime )
939 {
940 strcpy( szFieldType, "time(7)" );
941 }
942 else if( oField.GetType() == OFTDateTime )
943 {
944 strcpy( szFieldType, "datetime" );
945 }
946 else if( oField.GetType() == OFTBinary )
947 {
948 strcpy( szFieldType, "image" );
949 }
950 else if( bApproxOK )
951 {
952 CPLError( CE_Warning, CPLE_NotSupported,
953 "Can't create field %s with type %s on MSSQL layers. Creating as varchar.",
954 oField.GetNameRef(),
955 OGRFieldDefn::GetFieldTypeName(oField.GetType()) );
956 strcpy( szFieldType, "varchar" );
957 }
958 else
959 {
960 CPLError( CE_Failure, CPLE_NotSupported,
961 "Can't create field %s with type %s on MSSQL layers.",
962 oField.GetNameRef(),
963 OGRFieldDefn::GetFieldTypeName(oField.GetType()) );
964
965 return OGRERR_FAILURE;
966 }
967
968 /* -------------------------------------------------------------------- */
969 /* Create the new field. */
970 /* -------------------------------------------------------------------- */
971
972 CPLODBCStatement oStmt( poDS->GetSession() );
973
974 oStmt.Appendf( "ALTER TABLE [%s].[%s] ADD [%s] %s",
975 pszSchemaName, pszTableName, oField.GetNameRef(), szFieldType);
976
977 if ( !oField.IsNullable() )
978 {
979 oStmt.Append(" NOT NULL");
980 }
981 if ( oField.GetDefault() != nullptr && !oField.IsDefaultDriverSpecific() )
982 {
983 /* process default value specifications */
984 if ( EQUAL(oField.GetDefault(), "CURRENT_TIME") )
985 oStmt.Append(" DEFAULT(CONVERT([time],getdate()))");
986 else if ( EQUAL(oField.GetDefault(), "CURRENT_DATE") )
987 oStmt.Append( " DEFAULT(CONVERT([date],getdate()))" );
988 else
989 oStmt.Appendf(" DEFAULT(%s)", oField.GetDefault());
990 }
991
992 if( !oStmt.ExecuteSQL() )
993 {
994 CPLError( CE_Failure, CPLE_AppDefined,
995 "Error creating field %s, %s", oField.GetNameRef(),
996 poDS->GetSession()->GetLastError() );
997
998 return OGRERR_FAILURE;
999 }
1000
1001 /* -------------------------------------------------------------------- */
1002 /* Add the field to the OGRFeatureDefn. */
1003 /* -------------------------------------------------------------------- */
1004
1005 poFeatureDefn->AddFieldDefn( &oField );
1006
1007 return OGRERR_NONE;
1008 }
1009
1010 /************************************************************************/
1011 /* ISetFeature() */
1012 /* */
1013 /* SetFeature() is implemented by an UPDATE SQL command */
1014 /************************************************************************/
1015
ISetFeature(OGRFeature * poFeature)1016 OGRErr OGRMSSQLSpatialTableLayer::ISetFeature( OGRFeature *poFeature )
1017
1018 {
1019 if( !bUpdateAccess )
1020 {
1021 CPLError( CE_Failure, CPLE_NotSupported,
1022 UNSUPPORTED_OP_READ_ONLY,
1023 "SetFeature");
1024 return OGRERR_FAILURE;
1025 }
1026
1027 OGRErr eErr = OGRERR_FAILURE;
1028
1029 poDS->EndCopy();
1030
1031 GetLayerDefn();
1032
1033 if( nullptr == poFeature )
1034 {
1035 CPLError( CE_Failure, CPLE_AppDefined,
1036 "NULL pointer to OGRFeature passed to SetFeature()." );
1037 return eErr;
1038 }
1039
1040 if( poFeature->GetFID() == OGRNullFID )
1041 {
1042 CPLError( CE_Failure, CPLE_AppDefined,
1043 "FID required on features given to SetFeature()." );
1044 return eErr;
1045 }
1046
1047 if( !pszFIDColumn )
1048 {
1049 CPLError( CE_Failure, CPLE_AppDefined,
1050 "Unable to update features in tables without\n"
1051 "a recognised FID column.");
1052 return eErr;
1053 }
1054
1055 ClearStatement();
1056
1057 /* -------------------------------------------------------------------- */
1058 /* Form the UPDATE command. */
1059 /* -------------------------------------------------------------------- */
1060 CPLODBCStatement oStmt( poDS->GetSession() );
1061
1062 oStmt.Appendf( "UPDATE [%s].[%s] SET ", pszSchemaName, pszTableName);
1063
1064 OGRGeometry *poGeom = poFeature->GetGeometryRef();
1065 if (bUseGeometryValidation && poGeom != nullptr)
1066 {
1067 OGRMSSQLGeometryValidator oValidator(poGeom, nGeomColumnType);
1068 if (!oValidator.IsValid())
1069 {
1070 oValidator.MakeValid(poGeom);
1071 CPLError(CE_Warning, CPLE_NotSupported,
1072 "Geometry with FID = " CPL_FRMT_GIB " has been modified to valid geometry.", poFeature->GetFID());
1073 }
1074 }
1075
1076 int nFieldCount = poFeatureDefn->GetFieldCount();
1077 int bind_num = 0;
1078 void** bind_buffer = (void**)CPLMalloc(sizeof(void*) * nFieldCount);
1079
1080 int bNeedComma = FALSE;
1081 SQLLEN nWKBLenBindParameter;
1082 if(poGeom != nullptr && pszGeomColumn != nullptr)
1083 {
1084 oStmt.Appendf( "[%s] = ", pszGeomColumn );
1085
1086 if (nUploadGeometryFormat == MSSQLGEOMETRY_NATIVE)
1087 {
1088 OGRMSSQLGeometryWriter poWriter(poGeom, nGeomColumnType, nSRSId);
1089 int nDataLen = poWriter.GetDataLen();
1090 GByte *pabyData = (GByte *) CPLMalloc(nDataLen + 1);
1091 if (poWriter.WriteSqlGeometry(pabyData, nDataLen) == OGRERR_NONE)
1092 {
1093 char* pszBytes = GByteArrayToHexString( pabyData, nDataLen);
1094 SQLLEN nts = SQL_NTS;
1095 int nRetCode = SQLBindParameter(oStmt.GetStatement(), (SQLUSMALLINT)(bind_num + 1),
1096 SQL_PARAM_INPUT, SQL_C_CHAR, SQL_LONGVARCHAR,
1097 nDataLen, 0, (SQLPOINTER)pszBytes, 0, &nts);
1098 if ( nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO )
1099 {
1100 oStmt.Append( "?" );
1101 bind_buffer[bind_num] = pszBytes;
1102 ++bind_num;
1103 }
1104 else
1105 {
1106 oStmt.Append( "null" );
1107 CPLFree(pszBytes);
1108 }
1109 }
1110 else
1111 {
1112 oStmt.Append( "null" );
1113 }
1114 CPLFree(pabyData);
1115 }
1116 else if (nUploadGeometryFormat == MSSQLGEOMETRY_WKB)
1117 {
1118 const size_t nWKBLen = poGeom->WkbSize();
1119 GByte *pabyWKB = (GByte *) VSI_MALLOC_VERBOSE(nWKBLen + 1); // do we need the +1 ?
1120 if( pabyWKB == nullptr )
1121 {
1122 oStmt.Append( "null" );
1123 }
1124 else if( poGeom->exportToWkb( wkbNDR, pabyWKB, wkbVariantIso ) == OGRERR_NONE && (nGeomColumnType == MSSQLCOLTYPE_GEOMETRY
1125 || nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY))
1126 {
1127 nWKBLenBindParameter = nWKBLen;
1128 int nRetCode = SQLBindParameter(oStmt.GetStatement(), (SQLUSMALLINT)(bind_num + 1),
1129 SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY,
1130 nWKBLen, 0, (SQLPOINTER)pabyWKB, nWKBLen, &nWKBLenBindParameter);
1131 if ( nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO )
1132 {
1133 if (nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY)
1134 {
1135 oStmt.Append( "geography::STGeomFromWKB(?" );
1136 oStmt.Appendf(",%d)", nSRSId );
1137 }
1138 else
1139 {
1140 oStmt.Append( "geometry::STGeomFromWKB(?" );
1141 oStmt.Appendf(",%d).MakeValid()", nSRSId );
1142 }
1143 bind_buffer[bind_num] = pabyWKB;
1144 ++bind_num;
1145 }
1146 else
1147 {
1148 oStmt.Append( "null" );
1149 CPLFree(pabyWKB);
1150 }
1151 }
1152 else
1153 {
1154 oStmt.Append( "null" );
1155 CPLFree(pabyWKB);
1156 }
1157 }
1158 else if (nUploadGeometryFormat == MSSQLGEOMETRY_WKT)
1159 {
1160 char *pszWKT = nullptr;
1161 if( poGeom->exportToWkt( &pszWKT ) == OGRERR_NONE && (nGeomColumnType == MSSQLCOLTYPE_GEOMETRY
1162 || nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY))
1163 {
1164 size_t nLen = 0;
1165 while(pszWKT[nLen] != '\0')
1166 nLen ++;
1167
1168 int nRetCode = SQLBindParameter(oStmt.GetStatement(), (SQLUSMALLINT)(bind_num + 1),
1169 SQL_PARAM_INPUT, SQL_C_CHAR, SQL_LONGVARCHAR,
1170 nLen, 0, (SQLPOINTER)pszWKT, 0, nullptr);
1171 if ( nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO )
1172 {
1173 if (nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY)
1174 {
1175 oStmt.Append( "geography::STGeomFromText(?" );
1176 oStmt.Appendf(",%d)", nSRSId );
1177 }
1178 else
1179 {
1180 oStmt.Append( "geometry::STGeomFromText(?" );
1181 oStmt.Appendf(",%d).MakeValid()", nSRSId );
1182 }
1183 bind_buffer[bind_num] = pszWKT;
1184 ++bind_num;
1185 }
1186 else
1187 {
1188 oStmt.Append( "null" );
1189 CPLFree(pszWKT);
1190 }
1191 }
1192 else
1193 {
1194 oStmt.Append( "null" );
1195 CPLFree(pszWKT);
1196 }
1197 }
1198 else
1199 oStmt.Append( "null" );
1200
1201 bNeedComma = TRUE;
1202 }
1203
1204 int i;
1205 for( i = 0; i < nFieldCount; i++ )
1206 {
1207 if (bNeedComma)
1208 oStmt.Appendf( ", [%s] = ", poFeatureDefn->GetFieldDefn(i)->GetNameRef() );
1209 else
1210 {
1211 oStmt.Appendf( "[%s] = ", poFeatureDefn->GetFieldDefn(i)->GetNameRef() );
1212 bNeedComma = TRUE;
1213 }
1214
1215 if( !poFeature->IsFieldSetAndNotNull( i ) )
1216 oStmt.Append( "null" );
1217 else
1218 AppendFieldValue(&oStmt, poFeature, i, &bind_num, bind_buffer);
1219 }
1220
1221 /* Add the WHERE clause */
1222 oStmt.Appendf( " WHERE [%s] = " CPL_FRMT_GIB, pszFIDColumn, poFeature->GetFID());
1223
1224 /* -------------------------------------------------------------------- */
1225 /* Execute the update. */
1226 /* -------------------------------------------------------------------- */
1227
1228 if( !oStmt.ExecuteSQL() )
1229 {
1230 CPLError( CE_Failure, CPLE_AppDefined,
1231 "Error updating feature with FID:" CPL_FRMT_GIB ", %s", poFeature->GetFID(),
1232 poDS->GetSession()->GetLastError() );
1233
1234 for( i = 0; i < bind_num; i++ )
1235 CPLFree(bind_buffer[i]);
1236 CPLFree(bind_buffer);
1237
1238 return OGRERR_FAILURE;
1239 }
1240
1241 for( i = 0; i < bind_num; i++ )
1242 CPLFree(bind_buffer[i]);
1243 CPLFree(bind_buffer);
1244
1245 if (oStmt.GetRowCountAffected() < 1)
1246 return OGRERR_NON_EXISTING_FEATURE;
1247
1248 return OGRERR_NONE;
1249 }
1250
1251 /************************************************************************/
1252 /* DeleteFeature() */
1253 /************************************************************************/
1254
DeleteFeature(GIntBig nFID)1255 OGRErr OGRMSSQLSpatialTableLayer::DeleteFeature( GIntBig nFID )
1256
1257 {
1258 if( !bUpdateAccess )
1259 {
1260 CPLError( CE_Failure, CPLE_NotSupported,
1261 UNSUPPORTED_OP_READ_ONLY,
1262 "DeleteFeature");
1263 return OGRERR_FAILURE;
1264 }
1265
1266 poDS->EndCopy();
1267
1268 GetLayerDefn();
1269
1270 if( pszFIDColumn == nullptr )
1271 {
1272 CPLError( CE_Failure, CPLE_AppDefined,
1273 "DeleteFeature() without any FID column." );
1274 return OGRERR_FAILURE;
1275 }
1276
1277 if( nFID == OGRNullFID )
1278 {
1279 CPLError( CE_Failure, CPLE_AppDefined,
1280 "DeleteFeature() with unset FID fails." );
1281 return OGRERR_FAILURE;
1282 }
1283
1284 ClearStatement();
1285
1286 /* -------------------------------------------------------------------- */
1287 /* Drop the record with this FID. */
1288 /* -------------------------------------------------------------------- */
1289 CPLODBCStatement oStatement( poDS->GetSession() );
1290
1291 oStatement.Appendf("DELETE FROM [%s].[%s] WHERE [%s] = " CPL_FRMT_GIB,
1292 pszSchemaName, pszTableName, pszFIDColumn, nFID);
1293
1294 if( !oStatement.ExecuteSQL() )
1295 {
1296 CPLError( CE_Failure, CPLE_AppDefined,
1297 "Attempt to delete feature with FID " CPL_FRMT_GIB " failed. %s",
1298 nFID, poDS->GetSession()->GetLastError() );
1299
1300 return OGRERR_FAILURE;
1301 }
1302
1303 if (oStatement.GetRowCountAffected() < 1)
1304 return OGRERR_NON_EXISTING_FEATURE;
1305
1306 return OGRERR_NONE;
1307 }
1308
1309 /************************************************************************/
1310 /* Failed() */
1311 /************************************************************************/
1312
Failed(int nRetCode)1313 int OGRMSSQLSpatialTableLayer::Failed( int nRetCode )
1314
1315 {
1316 if( nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO )
1317 return FALSE;
1318
1319 char SQLState[6] = "";
1320 char Msg[256] = "";
1321 SQLINTEGER iNativeError = 0;
1322 SQLSMALLINT iMsgLen = 0;
1323
1324 int iRc = SQLGetDiagRec(SQL_HANDLE_ENV, hEnvBCP, 1,
1325 (SQLCHAR*)SQLState, &iNativeError, (SQLCHAR*)Msg, 256, &iMsgLen);
1326 if (iRc != SQL_NO_DATA) {
1327 CPLError( CE_Failure, CPLE_AppDefined,
1328 "SQL Error SQLState=%s, NativeError=%d, Msg=%s\n", SQLState,
1329 static_cast<int>(iNativeError), Msg );
1330 }
1331
1332 return TRUE;
1333 }
1334
1335 /************************************************************************/
1336 /* Failed2() */
1337 /************************************************************************/
1338
1339 #ifdef MSSQL_BCP_SUPPORTED
Failed2(int nRetCode)1340 int OGRMSSQLSpatialTableLayer::Failed2( int nRetCode )
1341
1342 {
1343 if (nRetCode == SUCCEED)
1344 return FALSE;
1345
1346 char SQLState[6] = "";
1347 char Msg[256] = "";
1348 SQLINTEGER iNativeError = 0;
1349 SQLSMALLINT iMsgLen = 0;
1350
1351 int iRc = SQLGetDiagRec(SQL_HANDLE_DBC, hDBCBCP, 1,
1352 (SQLCHAR*)SQLState, &iNativeError, (SQLCHAR*)Msg, 256, &iMsgLen);
1353 if (iRc != SQL_NO_DATA) {
1354 CPLError( CE_Failure, CPLE_AppDefined,
1355 "SQL Error SQLState=%s, NativeError=%d, Msg=%s\n", SQLState,
1356 static_cast<int>(iNativeError), Msg );
1357 }
1358
1359 return TRUE;
1360 }
1361
1362 /************************************************************************/
1363 /* InitBCP() */
1364 /************************************************************************/
1365
InitBCP(const char * pszDSN)1366 int OGRMSSQLSpatialTableLayer::InitBCP(const char* pszDSN)
1367
1368 {
1369 /* Create a different connection for BCP upload */
1370 if( Failed( SQLAllocHandle( SQL_HANDLE_ENV, NULL, &hEnvBCP ) ) )
1371 return FALSE;
1372
1373 /* Notify ODBC that this is an ODBC 3.0 app. */
1374 if( Failed( SQLSetEnvAttr( hEnvBCP, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER ) ) )
1375 {
1376 CloseBCP();
1377 return FALSE;
1378 }
1379
1380 if( Failed( SQLAllocHandle( SQL_HANDLE_DBC, hEnvBCP, &hDBCBCP ) ) )
1381 {
1382 CloseBCP();
1383 return FALSE;
1384 }
1385
1386 /* set bulk copy mode */
1387 if( Failed( SQLSetConnectAttr(hDBCBCP, SQL_COPT_SS_BCP, (void *)SQL_BCP_ON, SQL_IS_INTEGER) ) )
1388 {
1389 CloseBCP();
1390 return FALSE;
1391 }
1392
1393 SQLUINTEGER timeout = 30;
1394 Failed(SQLSetConnectAttr(hDBCBCP, SQL_ATTR_LOGIN_TIMEOUT, (void*)timeout, SQL_IS_INTEGER));
1395
1396 SQLCHAR szOutConnString[1024];
1397 SQLSMALLINT nOutConnStringLen = 0;
1398
1399 if ( Failed(
1400 SQLDriverConnect( hDBCBCP, NULL,
1401 (SQLCHAR *) pszDSN, (SQLSMALLINT)strlen(pszDSN),
1402 szOutConnString, sizeof(szOutConnString),
1403 &nOutConnStringLen, SQL_DRIVER_NOPROMPT ) ))
1404 {
1405 CloseBCP();
1406 return FALSE;
1407 }
1408
1409 return TRUE;
1410 }
1411
1412 /************************************************************************/
1413 /* CloseBCP() */
1414 /************************************************************************/
1415
CloseBCP()1416 void OGRMSSQLSpatialTableLayer::CloseBCP()
1417
1418 {
1419 if (papstBindBuffer)
1420 {
1421 int iCol;
1422
1423 int nRecNum = bcp_done( hDBCBCP );
1424 if (nRecNum == -1)
1425 Failed2(nRecNum);
1426
1427 for( iCol = 0; iCol < nRawColumns; iCol++ )
1428 CPLFree(papstBindBuffer[iCol]);
1429 CPLFree(papstBindBuffer);
1430 papstBindBuffer = NULL;
1431
1432 if( bIdentityInsert )
1433 {
1434 bIdentityInsert = FALSE;
1435 }
1436 }
1437
1438 if( hDBCBCP!=NULL )
1439 {
1440 CPLDebug( "ODBC", "SQLDisconnect()" );
1441 SQLDisconnect( hDBCBCP );
1442 SQLFreeHandle( SQL_HANDLE_DBC, hDBCBCP );
1443 hDBCBCP = NULL;
1444 }
1445
1446 if( hEnvBCP!=NULL )
1447 {
1448 SQLFreeHandle( SQL_HANDLE_ENV, hEnvBCP );
1449 hEnvBCP = NULL;
1450 }
1451 }
1452
1453 /************************************************************************/
1454 /* CreateFeatureBCP() */
1455 /************************************************************************/
1456
CreateFeatureBCP(OGRFeature * poFeature)1457 OGRErr OGRMSSQLSpatialTableLayer::CreateFeatureBCP( OGRFeature *poFeature )
1458
1459 {
1460 int iCol;
1461 int iField = 0;
1462
1463 if ( hDBCBCP == NULL )
1464 {
1465 nBCPCount = 0;
1466
1467 /* Tell the datasource we are now planning to copy data */
1468 poDS->StartCopy(this);
1469
1470 CPLODBCSession* poSession = poDS->GetSession();
1471
1472 if (poSession->IsInTransaction())
1473 poSession->CommitTransaction(); /* commit creating the table */
1474
1475 /* Get the column definitions for this table. */
1476 if( poFeatureDefn )
1477 {
1478 /* need to re-create layer defn */
1479 poFeatureDefn->Release();
1480 poFeatureDefn = NULL;
1481 }
1482 GetLayerDefn();
1483 if (!poFeatureDefn)
1484 return OGRERR_FAILURE;
1485
1486 if( poFeature->GetFID() != OGRNullFID && pszFIDColumn != NULL && bIsIdentityFid )
1487 {
1488 bIdentityInsert = TRUE;
1489 }
1490
1491 if ( !InitBCP( poDS->GetConnectionString() ) )
1492 return OGRERR_FAILURE;
1493
1494 /* Initialize the bulk copy */
1495 if (Failed2( bcp_init(hDBCBCP, CPLSPrintf("[%s].[%s]", pszSchemaName, pszTableName), NULL, NULL, DB_IN) ))
1496 {
1497 CloseBCP();
1498 return OGRERR_FAILURE;
1499 }
1500
1501 if (bIdentityInsert)
1502 {
1503 if (Failed2( bcp_control(hDBCBCP, BCPKEEPIDENTITY, (void*) TRUE )))
1504 {
1505 CPLError( CE_Failure, CPLE_AppDefined,
1506 "Failed to set identity insert bulk copy mode, %s.",
1507 poDS->GetSession()->GetLastError());
1508 return OGRERR_FAILURE;
1509 }
1510 }
1511
1512 papstBindBuffer = (BCPData**)CPLMalloc(sizeof(BCPData*) * (nRawColumns));
1513
1514 for( iCol = 0; iCol < nRawColumns; iCol++ )
1515 {
1516 papstBindBuffer[iCol] = NULL;
1517
1518 if (iCol == nGeomColumnIndex)
1519 {
1520 papstBindBuffer[iCol] = (BCPData*)CPLMalloc(sizeof(BCPData));
1521 if (Failed2( bcp_bind(hDBCBCP, NULL /* data is provided later */,
1522 0, 0/*or any value < 8000*/, NULL, 0, SQLUDT, iCol + 1) ))
1523 return OGRERR_FAILURE;
1524 }
1525 else if (iCol == nFIDColumnIndex)
1526 {
1527 if ( !bIdentityInsert )
1528 continue;
1529 /* bind fid column */
1530 papstBindBuffer[iCol] = (BCPData*)CPLMalloc(sizeof(BCPData));
1531 papstBindBuffer[iCol]->VarChar.nSize = SQL_VARLEN_DATA;
1532
1533 if (Failed2( bcp_bind(hDBCBCP, (LPCBYTE)papstBindBuffer[iCol]->VarChar.pData,
1534 0, SQL_VARLEN_DATA,
1535 (LPCBYTE)"", 1, SQLVARCHAR, iCol + 1) ))
1536 return OGRERR_FAILURE;
1537 }
1538 else if (iField < poFeatureDefn->GetFieldCount() && iCol == panFieldOrdinals[iField])
1539 {
1540 OGRFieldDefn* poFDefn = poFeatureDefn->GetFieldDefn(iField);
1541
1542 if ( poFDefn->IsIgnored() )
1543 {
1544 /* set null */
1545 ++iField;
1546 continue;
1547 }
1548
1549 int iSrcField = poFeature->GetFieldIndex(poFDefn->GetNameRef());
1550 if (iSrcField < 0)
1551 {
1552 ++iField;
1553 continue; /* no such field at the source */
1554 }
1555
1556 if( poFDefn->GetType() == OFTInteger )
1557 {
1558 /* int */
1559 papstBindBuffer[iCol] = (BCPData*)CPLMalloc(sizeof(BCPData));
1560 papstBindBuffer[iCol]->Integer.iIndicator = sizeof(papstBindBuffer[iCol]->Integer.Value);
1561
1562 if (Failed2( bcp_bind(hDBCBCP, (LPCBYTE)papstBindBuffer[iCol],
1563 sizeof(papstBindBuffer[iCol]->Integer.iIndicator), sizeof(papstBindBuffer[iCol]->Integer.Value),
1564 NULL, 0, SQLINT4, iCol + 1) ))
1565 return OGRERR_FAILURE;
1566 }
1567 else if( poFDefn->GetType() == OFTInteger64 )
1568 {
1569 /* bigint */
1570 papstBindBuffer[iCol] = (BCPData*)CPLMalloc(sizeof(BCPData));
1571 papstBindBuffer[iCol]->VarChar.nSize = SQL_VARLEN_DATA;
1572
1573 if (Failed2( bcp_bind(hDBCBCP, (LPCBYTE)papstBindBuffer[iCol]->VarChar.pData,
1574 0, SQL_VARLEN_DATA,
1575 (LPCBYTE)"", 1, SQLVARCHAR, iCol + 1) ))
1576 return OGRERR_FAILURE;
1577 }
1578 else if( poFDefn->GetType() == OFTReal )
1579 {
1580 /* float */
1581 /* TODO convert to DBNUMERIC */
1582 papstBindBuffer[iCol] = (BCPData*)CPLMalloc(sizeof(BCPData));
1583 papstBindBuffer[iCol]->VarChar.nSize = SQL_VARLEN_DATA;
1584
1585 if (Failed2( bcp_bind(hDBCBCP, (LPCBYTE)papstBindBuffer[iCol]->VarChar.pData,
1586 0, SQL_VARLEN_DATA,
1587 (LPCBYTE)"", 1, SQLVARCHAR, iCol + 1) ))
1588 return OGRERR_FAILURE;
1589 }
1590 else if( poFDefn->GetType() == OFTString )
1591 {
1592 /* nvarchar */
1593 papstBindBuffer[iCol] = (BCPData*)CPLMalloc(sizeof(BCPData));
1594 papstBindBuffer[iCol]->VarChar.nSize = poFDefn->GetWidth();
1595 if (poFDefn->GetWidth() == 0)
1596 {
1597 if (Failed2( bcp_bind(hDBCBCP, NULL /* data is provided later */,
1598 0, 0/*or any value < 8000*/, NULL, 0, 0, iCol + 1) ))
1599 return OGRERR_FAILURE;
1600 }
1601 else
1602 {
1603 if (Failed2( bcp_bind(hDBCBCP, (LPCBYTE)papstBindBuffer[iCol],
1604 sizeof(papstBindBuffer[iCol]->VarChar.nSize), poFDefn->GetWidth(),
1605 NULL, 0, SQLNVARCHAR, iCol + 1) ))
1606 return OGRERR_FAILURE;
1607 }
1608 }
1609 else if( poFDefn->GetType() == OFTDate )
1610 {
1611 /* date */
1612 papstBindBuffer[iCol] = (BCPData*)CPLMalloc(sizeof(BCPData));
1613 papstBindBuffer[iCol]->VarChar.nSize = SQL_VARLEN_DATA;
1614
1615 if (Failed2( bcp_bind(hDBCBCP, (LPCBYTE)papstBindBuffer[iCol]->VarChar.pData,
1616 0, SQL_VARLEN_DATA,
1617 (LPCBYTE)"", 1, SQLVARCHAR, iCol + 1) ))
1618 return OGRERR_FAILURE;
1619 }
1620 else if( poFDefn->GetType() == OFTTime )
1621 {
1622 /* time(7) */
1623 papstBindBuffer[iCol] = (BCPData*)CPLMalloc(sizeof(BCPData));
1624 papstBindBuffer[iCol]->VarChar.nSize = SQL_VARLEN_DATA;
1625
1626 if (Failed2( bcp_bind(hDBCBCP, (LPCBYTE)papstBindBuffer[iCol]->VarChar.pData,
1627 0, SQL_VARLEN_DATA,
1628 (LPCBYTE)"", 1, SQLVARCHAR, iCol + 1) ))
1629 return OGRERR_FAILURE;
1630 }
1631 else if( poFDefn->GetType() == OFTDateTime )
1632 {
1633 /* datetime */
1634 papstBindBuffer[iCol] = (BCPData*)CPLMalloc(sizeof(BCPData));
1635 papstBindBuffer[iCol]->VarChar.nSize = SQL_VARLEN_DATA;
1636
1637 if (Failed2( bcp_bind(hDBCBCP, (LPCBYTE)papstBindBuffer[iCol]->VarChar.pData,
1638 0, SQL_VARLEN_DATA,
1639 (LPCBYTE)"", 1, SQLVARCHAR, iCol + 1) ))
1640 return OGRERR_FAILURE;
1641 }
1642 else if( poFDefn->GetType() == OFTBinary )
1643 {
1644 /* image */
1645 papstBindBuffer[iCol] = (BCPData*)CPLMalloc(sizeof(BCPData));
1646 if (Failed2( bcp_bind(hDBCBCP, NULL /* data is provided later */,
1647 0, 0/*or any value < 8000*/, NULL, 0, 0, iCol + 1) ))
1648 return OGRERR_FAILURE;
1649 }
1650 else
1651 {
1652 CPLError( CE_Failure, CPLE_NotSupported,
1653 "Filed %s with type %s is not supported for bulk insert.",
1654 poFDefn->GetNameRef(),
1655 OGRFieldDefn::GetFieldTypeName(poFDefn->GetType()) );
1656
1657 return OGRERR_FAILURE;
1658 }
1659
1660 ++iField;
1661 }
1662 }
1663 }
1664
1665 /* do bulk insert here */
1666
1667 /* prepare data to variables */
1668 iField = 0;
1669 for( iCol = 0; iCol < nRawColumns; iCol++ )
1670 {
1671 if (iCol == nGeomColumnIndex)
1672 {
1673 if (poFeature->GetGeometryRef())
1674 {
1675 /* prepare geometry */
1676 OGRGeometry *poGeom = poFeature->GetGeometryRef();
1677 if (bUseGeometryValidation && poGeom != nullptr)
1678 {
1679 OGRMSSQLGeometryValidator oValidator(poGeom, nGeomColumnType);
1680 if (!oValidator.IsValid())
1681 {
1682 oValidator.MakeValid(poGeom);
1683 CPLError(CE_Warning, CPLE_NotSupported,
1684 "Geometry with FID = " CPL_FRMT_GIB " has been modified to valid geometry.", poFeature->GetFID());
1685 }
1686 }
1687
1688 OGRMSSQLGeometryWriter poWriter(poGeom, nGeomColumnType, nSRSId);
1689 papstBindBuffer[iCol]->RawData.nSize = poWriter.GetDataLen();
1690 papstBindBuffer[iCol]->RawData.pData = (GByte *) CPLMalloc(papstBindBuffer[iCol]->RawData.nSize + 1);
1691
1692 if (poWriter.WriteSqlGeometry(papstBindBuffer[iCol]->RawData.pData, (int)papstBindBuffer[iCol]->RawData.nSize) != OGRERR_NONE)
1693 return OGRERR_FAILURE;
1694
1695 /* set data length */
1696 if (Failed2( bcp_collen( hDBCBCP, (DBINT)papstBindBuffer[iCol]->RawData.nSize, iCol + 1) ))
1697 return OGRERR_FAILURE;
1698 }
1699 else
1700 {
1701 /* set NULL */
1702 papstBindBuffer[iCol]->RawData.nSize = SQL_NULL_DATA;
1703 if (Failed2( bcp_collen( hDBCBCP, SQL_NULL_DATA, iCol + 1) ))
1704 return OGRERR_FAILURE;
1705 }
1706 }
1707 else if (iCol == nFIDColumnIndex)
1708 {
1709 if ( !bIdentityInsert )
1710 continue;
1711
1712 GIntBig nFID = poFeature->GetFID();
1713 if ( nFID == OGRNullFID )
1714 {
1715 papstBindBuffer[iCol]->VarChar.nSize = SQL_NULL_DATA;
1716 /* set NULL */
1717 if (Failed2( bcp_collen( hDBCBCP, SQL_NULL_DATA, iCol + 1) ))
1718 return OGRERR_FAILURE;
1719 }
1720 else
1721 {
1722 papstBindBuffer[iCol]->VarChar.nSize = SQL_VARLEN_DATA;
1723 snprintf((char*)papstBindBuffer[iCol]->VarChar.pData, 8000, CPL_FRMT_GIB, nFID);
1724
1725 if (Failed2( bcp_collen( hDBCBCP, SQL_VARLEN_DATA, iCol + 1) ))
1726 return OGRERR_FAILURE;
1727 }
1728 }
1729 else if (iField < poFeatureDefn->GetFieldCount() && iCol == panFieldOrdinals[iField])
1730 {
1731 OGRFieldDefn* poFDefn = poFeatureDefn->GetFieldDefn(iField);
1732
1733 if (papstBindBuffer[iCol] == NULL)
1734 {
1735 ++iField;
1736 continue; /* column requires no data */
1737 }
1738
1739 if( poFDefn->GetType() == OFTInteger )
1740 {
1741 /* int */
1742 if (!poFeature->IsFieldSetAndNotNull( iField ))
1743 papstBindBuffer[iCol]->Integer.iIndicator = SQL_NULL_DATA;
1744 else
1745 {
1746 papstBindBuffer[iCol]->Integer.iIndicator = sizeof(papstBindBuffer[iCol]->Integer.Value);
1747 papstBindBuffer[iCol]->Integer.Value = poFeature->GetFieldAsInteger(iField);
1748 }
1749 }
1750 else if( poFDefn->GetType() == OFTInteger64 )
1751 {
1752 /* bigint */
1753 if (!poFeature->IsFieldSetAndNotNull( iField ))
1754 {
1755 papstBindBuffer[iCol]->VarChar.nSize = SQL_NULL_DATA;
1756 /* set NULL */
1757 if (Failed2( bcp_collen( hDBCBCP, SQL_NULL_DATA, iCol + 1) ))
1758 return OGRERR_FAILURE;
1759 }
1760 else
1761 {
1762 papstBindBuffer[iCol]->VarChar.nSize = SQL_VARLEN_DATA;
1763 snprintf((char*)papstBindBuffer[iCol]->VarChar.pData, 8000, "%s", poFeature->GetFieldAsString(iField));
1764
1765 if (Failed2( bcp_collen( hDBCBCP, SQL_VARLEN_DATA, iCol + 1) ))
1766 return OGRERR_FAILURE;
1767 }
1768 }
1769 else if( poFDefn->GetType() == OFTReal )
1770 {
1771 /* float */
1772 if (!poFeature->IsFieldSetAndNotNull( iField ))
1773 {
1774 papstBindBuffer[iCol]->VarChar.nSize = SQL_NULL_DATA;
1775 /* set NULL */
1776 if (Failed2( bcp_collen( hDBCBCP, SQL_NULL_DATA, iCol + 1) ))
1777 return OGRERR_FAILURE;
1778 }
1779 else
1780 {
1781 papstBindBuffer[iCol]->VarChar.nSize = SQL_VARLEN_DATA;
1782 snprintf((char*)papstBindBuffer[iCol]->VarChar.pData, 8000, "%s", poFeature->GetFieldAsString(iField));
1783
1784 if (Failed2( bcp_collen( hDBCBCP, SQL_VARLEN_DATA, iCol + 1) ))
1785 return OGRERR_FAILURE;
1786 }
1787 }
1788 else if( poFDefn->GetType() == OFTString )
1789 {
1790 /* nvarchar */
1791 if (poFDefn->GetWidth() != 0)
1792 {
1793 if (!poFeature->IsFieldSetAndNotNull( iField ))
1794 {
1795 papstBindBuffer[iCol]->VarChar.nSize = SQL_NULL_DATA;
1796 if (Failed2( bcp_collen( hDBCBCP, SQL_NULL_DATA, iCol + 1) ))
1797 return OGRERR_FAILURE;
1798 }
1799 else
1800 {
1801
1802 papstBindBuffer[iCol]->VarChar.nSize = (SQLLEN)CPLStrlenUTF8(poFeature->GetFieldAsString(iField)) * 2;
1803 wchar_t* buffer = CPLRecodeToWChar( poFeature->GetFieldAsString(iField), CPL_ENC_UTF8, CPL_ENC_UCS2);
1804 memcpy(papstBindBuffer[iCol]->VarChar.pData, buffer, papstBindBuffer[iCol]->VarChar.nSize + 2);
1805 CPLFree(buffer);
1806
1807 if (Failed2( bcp_collen( hDBCBCP, (DBINT)papstBindBuffer[iCol]->VarChar.nSize, iCol + 1) ))
1808 return OGRERR_FAILURE;
1809 }
1810 }
1811 }
1812 else if( poFDefn->GetType() == OFTDate )
1813 {
1814 /* date */
1815 if (!poFeature->IsFieldSetAndNotNull( iField ))
1816 {
1817 papstBindBuffer[iCol]->VarChar.nSize = SQL_NULL_DATA;
1818 /* set NULL */
1819 if (Failed2( bcp_collen( hDBCBCP, SQL_NULL_DATA, iCol + 1) ))
1820 return OGRERR_FAILURE;
1821 }
1822 else
1823 {
1824 int pnYear;
1825 int pnMonth;
1826 int pnDay;
1827 int pnHour;
1828 int pnMinute;
1829 float pfSecond;
1830 int pnTZFlag;
1831
1832 poFeature->GetFieldAsDateTime(iField, &pnYear, &pnMonth, &pnDay,
1833 &pnHour, &pnMinute, &pfSecond, &pnTZFlag);
1834
1835 papstBindBuffer[iCol]->VarChar.nSize = SQL_VARLEN_DATA;
1836 snprintf((char*)papstBindBuffer[iCol]->VarChar.pData, 8000, "%4d-%02d-%02d %02d:%02d:%06.3f", pnYear, pnMonth, pnDay, pnHour, pnMinute, pfSecond);
1837 if (Failed2( bcp_collen( hDBCBCP, SQL_VARLEN_DATA, iCol + 1) ))
1838 return OGRERR_FAILURE;
1839 }
1840 }
1841 else if( poFDefn->GetType() == OFTTime )
1842 {
1843 /* time(7) */
1844 if (!poFeature->IsFieldSetAndNotNull( iField ))
1845 {
1846 papstBindBuffer[iCol]->VarChar.nSize = SQL_NULL_DATA;
1847 /* set NULL */
1848 if (Failed2( bcp_collen( hDBCBCP, SQL_NULL_DATA, iCol + 1) ))
1849 return OGRERR_FAILURE;
1850 }
1851 else
1852 {
1853 int pnYear;
1854 int pnMonth;
1855 int pnDay;
1856 int pnHour;
1857 int pnMinute;
1858 float pfSecond;
1859 int pnTZFlag;
1860
1861 poFeature->GetFieldAsDateTime(iField, &pnYear, &pnMonth, &pnDay,
1862 &pnHour, &pnMinute, &pfSecond, &pnTZFlag);
1863
1864 papstBindBuffer[iCol]->VarChar.nSize = SQL_VARLEN_DATA;
1865 snprintf((char*)papstBindBuffer[iCol]->VarChar.pData, 8000, "%4d-%02d-%02d %02d:%02d:%06.3f", pnYear, pnMonth, pnDay, pnHour, pnMinute, pfSecond);
1866 if (Failed2( bcp_collen( hDBCBCP, SQL_VARLEN_DATA, iCol + 1) ))
1867 return OGRERR_FAILURE;
1868 }
1869 }
1870 else if( poFDefn->GetType() == OFTDateTime )
1871 {
1872 /* datetime */
1873 if (!poFeature->IsFieldSetAndNotNull( iField ))
1874 {
1875 papstBindBuffer[iCol]->VarChar.nSize = SQL_NULL_DATA;
1876 /* set NULL */
1877 if (Failed2( bcp_collen( hDBCBCP, SQL_NULL_DATA, iCol + 1) ))
1878 return OGRERR_FAILURE;
1879 }
1880 else
1881 {
1882 int pnYear;
1883 int pnMonth;
1884 int pnDay;
1885 int pnHour;
1886 int pnMinute;
1887 float pfSecond;
1888 int pnTZFlag;
1889
1890 poFeature->GetFieldAsDateTime(iField, &pnYear, &pnMonth, &pnDay,
1891 &pnHour, &pnMinute, &pfSecond, &pnTZFlag);
1892
1893 papstBindBuffer[iCol]->VarChar.nSize = SQL_VARLEN_DATA;
1894 snprintf((char*)papstBindBuffer[iCol]->VarChar.pData, 8000, "%4d-%02d-%02d %02d:%02d:%06.3f", pnYear, pnMonth, pnDay, pnHour, pnMinute, pfSecond);
1895
1896 if (Failed2( bcp_collen( hDBCBCP, SQL_VARLEN_DATA, iCol + 1) ))
1897 return OGRERR_FAILURE;
1898 }
1899 }
1900 else if( poFDefn->GetType() == OFTBinary )
1901 {
1902 if (!poFeature->IsFieldSetAndNotNull( iField ))
1903 {
1904 papstBindBuffer[iCol]->RawData.nSize = SQL_NULL_DATA;
1905 /* set NULL */
1906 if (Failed2( bcp_collen( hDBCBCP, SQL_NULL_DATA, iCol + 1) ))
1907 return OGRERR_FAILURE;
1908 }
1909 else
1910 {
1911 /* image */
1912 int nLen;
1913 papstBindBuffer[iCol]->RawData.pData = poFeature->GetFieldAsBinary(iField, &nLen);
1914 papstBindBuffer[iCol]->RawData.nSize = nLen;
1915
1916 /* set data length */
1917 if (Failed2( bcp_collen( hDBCBCP, (DBINT)papstBindBuffer[iCol]->RawData.nSize, iCol + 1) ))
1918 return OGRERR_FAILURE;
1919 }
1920 }
1921 else
1922 {
1923 CPLError( CE_Failure, CPLE_NotSupported,
1924 "Filed %s with type %s is not supported for bulk insert.",
1925 poFDefn->GetNameRef(),
1926 OGRFieldDefn::GetFieldTypeName(poFDefn->GetType()) );
1927
1928 return OGRERR_FAILURE;
1929 }
1930
1931 ++iField;
1932 }
1933 }
1934
1935 /* send row */
1936 if (Failed2( bcp_sendrow( hDBCBCP ) ))
1937 return OGRERR_FAILURE;
1938
1939 /* send dynamic data */
1940 iField = 0;
1941 for( iCol = 0; iCol < nRawColumns; iCol++ )
1942 {
1943 if (iCol == nGeomColumnIndex)
1944 {
1945 if (papstBindBuffer[iCol]->RawData.nSize != SQL_NULL_DATA)
1946 {
1947 if (Failed2( bcp_moretext( hDBCBCP,
1948 (DBINT)papstBindBuffer[iCol]->RawData.nSize,
1949 papstBindBuffer[iCol]->RawData.pData ) ))
1950 {
1951
1952 }
1953 CPLFree(papstBindBuffer[iCol]->RawData.pData);
1954 if (Failed2( bcp_moretext( hDBCBCP, 0, NULL) ))
1955 {
1956
1957 }
1958 }
1959 else
1960 {
1961 if (Failed2( bcp_moretext( hDBCBCP, SQL_NULL_DATA, NULL) ))
1962 {
1963
1964 }
1965 }
1966 }
1967 else if (iCol == nFIDColumnIndex)
1968 {
1969 /* TODO */
1970 continue;
1971 }
1972 else if (iField < poFeatureDefn->GetFieldCount() && iCol == panFieldOrdinals[iField])
1973 {
1974 OGRFieldDefn* poFDefn = poFeatureDefn->GetFieldDefn(iField);
1975
1976 if( poFDefn->GetType() == OFTString )
1977 {
1978 if (poFDefn->GetWidth() == 0)
1979 {
1980 if (poFeature->IsFieldSetAndNotNull( iField ))
1981 {
1982 papstBindBuffer[iCol]->VarChar.nSize = (SQLLEN)CPLStrlenUTF8(poFeature->GetFieldAsString(iField)) * 2;
1983 if (papstBindBuffer[iCol]->VarChar.nSize > 0)
1984 {
1985 wchar_t* buffer = CPLRecodeToWChar( poFeature->GetFieldAsString(iField), CPL_ENC_UTF8, CPL_ENC_UCS2);
1986 if (Failed2( bcp_moretext( hDBCBCP,
1987 (DBINT)papstBindBuffer[iCol]->VarChar.nSize,
1988 (LPCBYTE)buffer ) ))
1989 {
1990
1991 }
1992
1993 CPLFree(buffer);
1994 }
1995
1996 if (Failed2( bcp_moretext( hDBCBCP, 0, NULL) ))
1997 {
1998
1999 }
2000 }
2001 else
2002 {
2003 if (Failed2( bcp_moretext( hDBCBCP, SQL_NULL_DATA, NULL) ))
2004 {
2005
2006 }
2007 }
2008 }
2009 }
2010 else if ( poFDefn->GetType() == OFTBinary )
2011 {
2012 if (papstBindBuffer[iCol]->RawData.nSize != SQL_NULL_DATA)
2013 {
2014 if (papstBindBuffer[iCol]->RawData.nSize > 0)
2015 {
2016 if (Failed2( bcp_moretext( hDBCBCP,
2017 (DBINT)papstBindBuffer[iCol]->RawData.nSize,
2018 papstBindBuffer[iCol]->RawData.pData ) ))
2019 {
2020
2021 }
2022 }
2023 if (Failed2( bcp_moretext( hDBCBCP, 0, NULL) ))
2024 {
2025
2026 }
2027 }
2028 else
2029 {
2030 if (Failed2( bcp_moretext( hDBCBCP, SQL_NULL_DATA, NULL) ))
2031 {
2032
2033 }
2034 }
2035 }
2036 ++iField;
2037 }
2038 }
2039
2040 if (++nBCPCount >= nBCPSize)
2041 {
2042 /* commit */
2043 int nRecNum = bcp_batch( hDBCBCP );
2044 if (nRecNum == -1)
2045 Failed2(nRecNum);
2046
2047 nBCPCount = 0;
2048 }
2049
2050 return OGRERR_NONE;
2051 }
2052 #endif /* MSSQL_BCP_SUPPORTED */
2053
2054 /************************************************************************/
2055 /* ICreateFeature() */
2056 /************************************************************************/
2057
ICreateFeature(OGRFeature * poFeature)2058 OGRErr OGRMSSQLSpatialTableLayer::ICreateFeature( OGRFeature *poFeature )
2059
2060 {
2061 if( !bUpdateAccess )
2062 {
2063 CPLError( CE_Failure, CPLE_NotSupported,
2064 UNSUPPORTED_OP_READ_ONLY,
2065 "CreateFeature");
2066 return OGRERR_FAILURE;
2067 }
2068
2069 GetLayerDefn();
2070
2071 if( nullptr == poFeature )
2072 {
2073 CPLError( CE_Failure, CPLE_AppDefined,
2074 "NULL pointer to OGRFeature passed to CreateFeature()." );
2075 return OGRERR_FAILURE;
2076 }
2077
2078 #if (ODBCVER >= 0x0300) && defined(MSSQL_BCP_SUPPORTED)
2079 if (bUseCopy)
2080 {
2081 return CreateFeatureBCP( poFeature );
2082 }
2083 #endif
2084
2085 ClearStatement();
2086
2087 CPLODBCSession* poSession = poDS->GetSession();
2088
2089 /* the fid values are retrieved from the source layer */
2090 CPLODBCStatement oStatement( poSession );
2091
2092 if( poFeature->GetFID() != OGRNullFID && pszFIDColumn != nullptr && bIsIdentityFid )
2093 oStatement.Appendf( "SET IDENTITY_INSERT [%s].[%s] ON;",
2094 pszSchemaName, pszTableName );
2095
2096 /* -------------------------------------------------------------------- */
2097 /* Form the INSERT command. */
2098 /* -------------------------------------------------------------------- */
2099
2100 oStatement.Appendf( "INSERT INTO [%s].[%s] ", pszSchemaName, pszTableName );
2101
2102 OGRGeometry *poGeom = poFeature->GetGeometryRef();
2103 GIntBig nFID = poFeature->GetFID();
2104 if (bUseGeometryValidation && poGeom != nullptr)
2105 {
2106 OGRMSSQLGeometryValidator oValidator(poGeom, nGeomColumnType);
2107 if (!oValidator.IsValid())
2108 {
2109 oValidator.MakeValid(poGeom);
2110 CPLError(CE_Warning, CPLE_NotSupported,
2111 "Geometry with FID = " CPL_FRMT_GIB " has been modified to valid geometry.", poFeature->GetFID());
2112 }
2113 }
2114
2115 int bNeedComma = FALSE;
2116
2117 if (poGeom != nullptr && pszGeomColumn != nullptr)
2118 {
2119 oStatement.Append("([");
2120 oStatement.Append( pszGeomColumn );
2121 oStatement.Append("]");
2122 bNeedComma = TRUE;
2123 }
2124
2125 if( nFID != OGRNullFID && pszFIDColumn != nullptr )
2126 {
2127 if( !CPL_INT64_FITS_ON_INT32(nFID) &&
2128 GetMetadataItem(OLMD_FID64) == nullptr )
2129 {
2130 /* MSSQL server doesn't support modifying pk columns without recreating the field */
2131 CPLError( CE_Failure, CPLE_AppDefined,
2132 "Failed to create feature with large integer fid. "
2133 "The FID64 layer creation option should be used." );
2134
2135 return OGRERR_FAILURE;
2136 }
2137
2138 if (bNeedComma)
2139 oStatement.Appendf( ", [%s]", pszFIDColumn );
2140 else
2141 {
2142 oStatement.Appendf( "([%s]", pszFIDColumn );
2143 bNeedComma = TRUE;
2144 }
2145 }
2146
2147 int nFieldCount = poFeatureDefn->GetFieldCount();
2148
2149 int bind_num = 0;
2150 void** bind_buffer = (void**)CPLMalloc(sizeof(void*) * (nFieldCount + 1));
2151 #ifdef SQL_SS_UDT
2152 SQLLEN* bind_datalen = (SQLLEN*)CPLMalloc(sizeof(SQLLEN) * (nFieldCount + 1));
2153 #endif
2154
2155 int i;
2156 for( i = 0; i < nFieldCount; i++ )
2157 {
2158 if( !poFeature->IsFieldSetAndNotNull( i ) )
2159 continue;
2160
2161 if (bNeedComma)
2162 oStatement.Appendf( ", [%s]", poFeatureDefn->GetFieldDefn(i)->GetNameRef() );
2163 else
2164 {
2165 oStatement.Appendf( "([%s]", poFeatureDefn->GetFieldDefn(i)->GetNameRef() );
2166 bNeedComma = TRUE;
2167 }
2168 }
2169
2170 SQLLEN nWKBLenBindParameter;
2171 if (oStatement.GetCommand()[strlen(oStatement.GetCommand()) - 1] != ']')
2172 {
2173 /* no fields were added */
2174
2175 if (nFID == OGRNullFID && pszFIDColumn != nullptr && (bIsIdentityFid || poDS->AlwaysOutputFid() ))
2176 oStatement.Appendf(" OUTPUT INSERTED.[%s] DEFAULT VALUES;", GetFIDColumn());
2177 else
2178 oStatement.Appendf( "DEFAULT VALUES;" );
2179 }
2180 else
2181 {
2182 /* prepend VALUES section */
2183 if (nFID == OGRNullFID && pszFIDColumn != nullptr && (bIsIdentityFid || poDS->AlwaysOutputFid() ))
2184 oStatement.Appendf(") OUTPUT INSERTED.[%s] VALUES (", GetFIDColumn());
2185 else
2186 oStatement.Appendf( ") VALUES (" );
2187
2188 /* Set the geometry */
2189 bNeedComma = FALSE;
2190 if(poGeom != nullptr && pszGeomColumn != nullptr)
2191 {
2192 int nOutgoingSRSId = 0;
2193
2194 // Use the SRID specified by the provided feature's geometry, if
2195 // its spatial-reference system is known; otherwise, use the SRID
2196 // associated with the table
2197 OGRSpatialReference *poFeatureSRS = poGeom->getSpatialReference();
2198 if (poFeatureSRS)
2199 nOutgoingSRSId = poDS->FetchSRSId(poFeatureSRS);
2200 if (nOutgoingSRSId <= 0)
2201 nOutgoingSRSId = nSRSId;
2202
2203 if (nUploadGeometryFormat == MSSQLGEOMETRY_NATIVE)
2204 {
2205 #ifdef SQL_SS_UDT
2206 OGRMSSQLGeometryWriter poWriter(poGeom, nGeomColumnType, nOutgoingSRSId);
2207 bind_datalen[bind_num] = poWriter.GetDataLen();
2208 GByte *pabyData = (GByte *) CPLMalloc(bind_datalen[bind_num] + 1);
2209 if (poWriter.WriteSqlGeometry(pabyData, (int)bind_datalen[bind_num]) == OGRERR_NONE)
2210 {
2211 SQLHANDLE ipd;
2212 if ((!poSession->Failed( SQLBindParameter(oStatement.GetStatement(), (SQLUSMALLINT)(bind_num + 1),
2213 SQL_PARAM_INPUT, SQL_C_BINARY, SQL_SS_UDT,
2214 SQL_SS_LENGTH_UNLIMITED, 0, (SQLPOINTER)pabyData, bind_datalen[bind_num], (SQLLEN*)&bind_datalen[bind_num])))
2215 && (!poSession->Failed(SQLGetStmtAttr(oStatement.GetStatement(), SQL_ATTR_IMP_PARAM_DESC, &ipd, 0, 0)))
2216 && (!poSession->Failed(SQLSetDescField(ipd, 1, SQL_CA_SS_UDT_TYPE_NAME,
2217 (nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY? "geography" : "geometry"), SQL_NTS))))
2218 {
2219 oStatement.Append( "?" );
2220 bind_buffer[bind_num] = pabyData;
2221 ++bind_num;
2222 }
2223 else
2224 {
2225 oStatement.Append( "null" );
2226 CPLFree(pabyData);
2227 }
2228 }
2229 else
2230 {
2231 oStatement.Append( "null" );
2232 CPLFree(pabyData);
2233 }
2234 #else
2235 CPLError( CE_Failure, CPLE_AppDefined,
2236 "Native geometry upload is not supported" );
2237
2238 // No need to free bind_buffer[i] since bind_num == 0 in that branch
2239 CPLFree(bind_buffer);
2240
2241 return OGRERR_FAILURE;
2242 #endif
2243 //CPLFree(pabyData);
2244 }
2245 else if (nUploadGeometryFormat == MSSQLGEOMETRY_WKB)
2246 {
2247 const size_t nWKBLen = poGeom->WkbSize();
2248 GByte *pabyWKB = (GByte *) VSI_MALLOC_VERBOSE(nWKBLen + 1); // do we need the +1 ?
2249 if( pabyWKB == nullptr )
2250 {
2251 oStatement.Append( "null" );
2252 }
2253 else if( poGeom->exportToWkb( wkbNDR, pabyWKB, wkbVariantIso ) == OGRERR_NONE && (nGeomColumnType == MSSQLCOLTYPE_GEOMETRY
2254 || nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY))
2255 {
2256 nWKBLenBindParameter = nWKBLen;
2257 int nRetCode = SQLBindParameter(oStatement.GetStatement(), (SQLUSMALLINT)(bind_num + 1),
2258 SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY,
2259 nWKBLen, 0, (SQLPOINTER)pabyWKB, nWKBLen, &nWKBLenBindParameter);
2260 if ( nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO )
2261 {
2262 if (nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY)
2263 {
2264 oStatement.Append( "geography::STGeomFromWKB(?" );
2265 oStatement.Appendf(",%d)", nOutgoingSRSId );
2266 }
2267 else
2268 {
2269 oStatement.Append( "geometry::STGeomFromWKB(?" );
2270 oStatement.Appendf(",%d).MakeValid()", nOutgoingSRSId );
2271 }
2272 bind_buffer[bind_num] = pabyWKB;
2273 ++bind_num;
2274 }
2275 else
2276 {
2277 oStatement.Append( "null" );
2278 CPLFree(pabyWKB);
2279 }
2280 }
2281 else
2282 {
2283 oStatement.Append( "null" );
2284 CPLFree(pabyWKB);
2285 }
2286 }
2287 else if (nUploadGeometryFormat == MSSQLGEOMETRY_WKT)
2288 {
2289 char *pszWKT = nullptr;
2290 if( poGeom->exportToWkt( &pszWKT ) == OGRERR_NONE && (nGeomColumnType == MSSQLCOLTYPE_GEOMETRY
2291 || nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY))
2292 {
2293 size_t nLen = 0;
2294 while(pszWKT[nLen] != '\0')
2295 nLen ++;
2296
2297 int nRetCode = SQLBindParameter(oStatement.GetStatement(), (SQLUSMALLINT)(bind_num + 1),
2298 SQL_PARAM_INPUT, SQL_C_CHAR, SQL_LONGVARCHAR,
2299 nLen, 0, (SQLPOINTER)pszWKT, 0, nullptr);
2300 if ( nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO )
2301 {
2302 if (nGeomColumnType == MSSQLCOLTYPE_GEOGRAPHY)
2303 {
2304 oStatement.Append( "geography::STGeomFromText(?" );
2305 oStatement.Appendf(",%d)", nOutgoingSRSId );
2306 }
2307 else
2308 {
2309 oStatement.Append( "geometry::STGeomFromText(?" );
2310 oStatement.Appendf(",%d).MakeValid()", nOutgoingSRSId );
2311 }
2312 bind_buffer[bind_num] = pszWKT;
2313 ++bind_num;
2314 }
2315 else
2316 {
2317 oStatement.Append( "null" );
2318 CPLFree(pszWKT);
2319 }
2320 }
2321 else
2322 {
2323 oStatement.Append( "null" );
2324 CPLFree(pszWKT);
2325 }
2326 }
2327 else
2328 oStatement.Append( "null" );
2329
2330 bNeedComma = TRUE;
2331 }
2332
2333 /* Set the FID */
2334 if( nFID != OGRNullFID && pszFIDColumn != nullptr )
2335 {
2336 if (bNeedComma)
2337 oStatement.Appendf( ", " CPL_FRMT_GIB, nFID );
2338 else
2339 {
2340 oStatement.Appendf( CPL_FRMT_GIB, nFID );
2341 bNeedComma = TRUE;
2342 }
2343 }
2344
2345 for( i = 0; i < nFieldCount; i++ )
2346 {
2347 if( !poFeature->IsFieldSetAndNotNull( i ) )
2348 continue;
2349
2350 if (bNeedComma)
2351 oStatement.Append( ", " );
2352 else
2353 bNeedComma = TRUE;
2354
2355 AppendFieldValue(&oStatement, poFeature, i, &bind_num, bind_buffer);
2356 }
2357
2358 oStatement.Append( ");" );
2359 }
2360
2361 if( nFID != OGRNullFID && pszFIDColumn != nullptr && bIsIdentityFid )
2362 oStatement.Appendf("SET IDENTITY_INSERT [%s].[%s] OFF;", pszSchemaName, pszTableName );
2363
2364 /* -------------------------------------------------------------------- */
2365 /* Execute the insert. */
2366 /* -------------------------------------------------------------------- */
2367
2368 if( !oStatement.ExecuteSQL() )
2369 {
2370 CPLError( CE_Failure, CPLE_AppDefined,
2371 "INSERT command for new feature failed. %s",
2372 poDS->GetSession()->GetLastError() );
2373
2374 for( i = 0; i < bind_num; i++ )
2375 CPLFree(bind_buffer[i]);
2376 CPLFree(bind_buffer);
2377
2378 #ifdef SQL_SS_UDT
2379 CPLFree(bind_datalen);
2380 #endif
2381
2382 return OGRERR_FAILURE;
2383 }
2384 else if(nFID == OGRNullFID && pszFIDColumn != nullptr && (bIsIdentityFid || poDS->AlwaysOutputFid() ))
2385 {
2386 // fetch new ID and set it into the feature
2387 if (oStatement.Fetch())
2388 {
2389 GIntBig newID = atoll(oStatement.GetColData(0));
2390 poFeature->SetFID(newID);
2391 }
2392 }
2393
2394 for( i = 0; i < bind_num; i++ )
2395 CPLFree(bind_buffer[i]);
2396 CPLFree(bind_buffer);
2397
2398 #ifdef SQL_SS_UDT
2399 CPLFree(bind_datalen);
2400 #endif
2401
2402 return OGRERR_NONE;
2403 }
2404
2405 /************************************************************************/
2406 /* AppendFieldValue() */
2407 /* */
2408 /* Used by CreateFeature() and SetFeature() to format a */
2409 /* non-empty field value */
2410 /************************************************************************/
2411
AppendFieldValue(CPLODBCStatement * poStatement,OGRFeature * poFeature,int i,int * bind_num,void ** bind_buffer)2412 void OGRMSSQLSpatialTableLayer::AppendFieldValue(CPLODBCStatement *poStatement,
2413 OGRFeature* poFeature, int i, int *bind_num, void **bind_buffer)
2414 {
2415 int nOGRFieldType = poFeatureDefn->GetFieldDefn(i)->GetType();
2416 int nOGRFieldSubType = poFeatureDefn->GetFieldDefn(i)->GetSubType();
2417
2418 // We need special formatting for integer list values.
2419 if( nOGRFieldType == OFTIntegerList )
2420 {
2421 //TODO
2422 poStatement->Append( "null" );
2423 return;
2424 }
2425
2426 // We need special formatting for real list values.
2427 else if( nOGRFieldType == OFTRealList )
2428 {
2429 //TODO
2430 poStatement->Append( "null" );
2431 return;
2432 }
2433
2434 // We need special formatting for string list values.
2435 else if( nOGRFieldType == OFTStringList )
2436 {
2437 //TODO
2438 poStatement->Append( "null" );
2439 return;
2440 }
2441
2442 // Binary formatting
2443 if( nOGRFieldType == OFTBinary )
2444 {
2445 int nLen = 0;
2446 GByte* pabyData = poFeature->GetFieldAsBinary( i, &nLen );
2447 char* pszBytes = GByteArrayToHexString( pabyData, nLen);
2448 poStatement->Append( pszBytes );
2449 CPLFree(pszBytes);
2450 return;
2451 }
2452
2453 // Datetime values need special handling as SQL Server's datetime type
2454 // accepts values only in ISO 8601 format and only without time zone
2455 // information
2456 else if( nOGRFieldType == OFTDateTime )
2457 {
2458 char *pszStrValue = OGRGetXMLDateTime( (*poFeature)[i].GetRawValue() );
2459
2460 int nRetCode = SQLBindParameter( poStatement->GetStatement(),
2461 (SQLUSMALLINT)((*bind_num) + 1), SQL_PARAM_INPUT, SQL_C_CHAR,
2462 SQL_VARCHAR, strlen(pszStrValue) + 1, 0, (SQLPOINTER)pszStrValue,
2463 0, nullptr );
2464 if( nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO )
2465 {
2466 bind_buffer[*bind_num] = pszStrValue;
2467 ++(*bind_num);
2468 poStatement->Append(
2469 "CAST(CAST(? AS datetimeoffset) AS datetime)" );
2470 }
2471 else {
2472 poStatement->Append(
2473 CPLSPrintf( "CAST(CAST('%s' AS datetimeoffset) AS datetime)",
2474 pszStrValue ) );
2475 CPLFree( pszStrValue );
2476 }
2477 return;
2478 }
2479
2480 // Flag indicating NULL or not-a-date date value
2481 // e.g. 0000-00-00 - there is no year 0
2482 OGRBoolean bIsDateNull = FALSE;
2483
2484 const char *pszStrValue = poFeature->GetFieldAsString(i);
2485
2486 // Check if date is NULL: 0000-00-00
2487 if( nOGRFieldType == OFTDate )
2488 {
2489 if( STARTS_WITH_CI(pszStrValue, "0000") )
2490 {
2491 pszStrValue = "null";
2492 bIsDateNull = TRUE;
2493 }
2494 }
2495 else if ( nOGRFieldType == OFTReal )
2496 {
2497 char* pszComma = strchr((char*)pszStrValue, ',');
2498 if (pszComma)
2499 *pszComma = '.';
2500 }
2501
2502 if( nOGRFieldType != OFTInteger && nOGRFieldType != OFTInteger64 && nOGRFieldType != OFTReal
2503 && !bIsDateNull )
2504 {
2505 if (nOGRFieldType == OFTString)
2506 {
2507 if (nOGRFieldSubType == OFSTUUID)
2508 {
2509 int nRetCode = SQLBindParameter(poStatement->GetStatement(), (SQLUSMALLINT)((*bind_num) + 1),
2510 SQL_PARAM_INPUT, SQL_C_CHAR, SQL_GUID, 16, 0, (SQLPOINTER)pszStrValue, 0, nullptr);
2511 if ( nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO )
2512 {
2513 poStatement->Append( "?" );
2514 bind_buffer[*bind_num] = CPLStrdup(pszStrValue);
2515 ++(*bind_num);
2516 }
2517 else
2518 {
2519 OGRMSSQLAppendEscaped(poStatement, pszStrValue);
2520 }
2521 }
2522 else
2523 {
2524 // bind UTF8 as unicode parameter
2525 wchar_t* buffer = CPLRecodeToWChar( pszStrValue, CPL_ENC_UTF8, CPL_ENC_UCS2);
2526 size_t nLen = wcslen(buffer) + 1;
2527 if (nLen > 4000)
2528 {
2529 /* need to handle nvarchar(max) */
2530 #ifdef SQL_SS_LENGTH_UNLIMITED
2531 nLen = SQL_SS_LENGTH_UNLIMITED;
2532 #else
2533 /* for older drivers truncate the data to 4000 chars */
2534 buffer[4000] = 0;
2535 nLen = 4000;
2536 CPLError( CE_Warning, CPLE_AppDefined,
2537 "String data truncation applied on field: %s. Use a more recent ODBC driver that supports handling large string values.", poFeatureDefn->GetFieldDefn(i)->GetNameRef() );
2538 #endif
2539 }
2540 #if WCHAR_MAX > 0xFFFFu
2541 // Shorten each character to a two-byte value, as expected by
2542 // the ODBC driver
2543 GUInt16 *panBuffer = reinterpret_cast<GUInt16 *>(buffer);
2544 for( unsigned int nIndex = 1; nIndex < nLen; nIndex += 1 )
2545 panBuffer[nIndex] = static_cast<GUInt16>(buffer[nIndex]);
2546 #endif
2547 int nRetCode = SQLBindParameter(poStatement->GetStatement(), (SQLUSMALLINT)((*bind_num) + 1),
2548 SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WVARCHAR, nLen, 0, (SQLPOINTER)buffer, 0, nullptr);
2549 if ( nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO )
2550 {
2551 poStatement->Append( "?" );
2552 bind_buffer[*bind_num] = buffer;
2553 ++(*bind_num);
2554 }
2555 else
2556 {
2557 OGRMSSQLAppendEscaped(poStatement, pszStrValue);
2558 CPLFree(buffer);
2559 }
2560 }
2561 }
2562 else
2563 OGRMSSQLAppendEscaped(poStatement, pszStrValue);
2564 }
2565 else
2566 {
2567 poStatement->Append( pszStrValue );
2568 }
2569 }
2570