[gdal-dev] Join issues with ODBC

[Yewondwossen Assefa]

Hi all,

  I was trying to do an a join using a shape file as a primary table and 
an ODBC connection as the secondary table.


I encountered a couple of issues:

1- joining with a string field did not work

query looks something like this:

ogrinfo f:/msapps/test/parcels_sp.shp -sql "SELECT shp.APN2, 
mdb.prc_parcel_no  FROM parcels_sp shp LEFT JOIN 
'ODBC:SanCarlosHansen_mdb'.dbo_casemain mdb ON shp.APN2 = mdb.prc_parcel_no"

The resulting query string that is used as the attribute filter on the 
ODBC layer end up being : ... prc.parcel="the_value" ..
For some reason, using double quotes in the query would not work but 
using single quotes would return expected values.

2 - values of some fields seem to be "corrupted"

  Still using  a join, when an id exists in the first table but not in 
the 2nd table, some of the fields values returned on the resulting shape 
(fields that comes from the 2nd table) seem to be "corrupted" (instead 
of being set to null or properly initialized).

Here is an example of a result where csm_status (coming from the 2nd 
table) is incorrect.
 From what I can see, there might be a need to initialize the resulting 
fields when the joining returns "empty".
Note that this issue is not particular to ODBC, since I could replicate 
it with a DFB file used as a 2nd table

OGRFeature(shp):38
   APN (String) = 050081090
   APN2 (String) = 050 081 090
   AREA_SQ_FT (Real) =             7287
   AREA_ACRES (Real) =               0.17
   FIREHAZARD (String) = No
   ...
   csm_name_mi (String) = (null)
   csm_projname (String) = 38
   csm_recd_by (String) = POLYGON
   csm_recd_date (DateTime) = (null)
   csm_status (String) = POLYGON ((6049455.0897085527 
2008278.2315386848,6049547.8169212556 2008346.6708414906,6049556.03
41119012 2008335.0614490749,6049585.4742749501 
2008300.3358252922,6049496.2063308069 2008225.7139653133,6049455.08970855
27 2008278.2315386848,6049455.0897085527 2008278.2315386848))
   csm_frozen (String) = (null)
   csm_auto_cond (String) = (null)
   csm_updateby (String) = (null)
   csm_updated (DateTime) = (null)
   csm_projno (String) = (null)
   csm_mastno (String) = (null)
   csm_tracking_no (Integer) = (null)
   POLYGON ((6049455.0897085527 2008278.2315386848,6049547.8169212556 
2008346.6708414906,6049556.0341119012 2008335.06144
90749,6049585.4742749501 2008300.3358252922,6049496.2063308069 
2008225.7139653133,6049455.0897085527 2008278.2315386848,



  Did some one came across such issues. Note that I am working on 
Windows using gdal from svn.
I have attached simple patch used locally to correct/show the issues


Thanks


-- 
----------------------------------------------------------------
Assefa Yewondwossen
Software Analyst

Email: assefa at dmsolutions.ca
http://www.dmsolutions.ca/

Phone: (613) 565-5056 (ext 14)
Fax:   (613) 565-0925
----------------------------------------------------------------

-------------- next part --------------
Index: ogr_gensql.cpp
===================================================================
--- ogr_gensql.cpp	(revision 17611)
+++ ogr_gensql.cpp	(working copy)
@@ -666,7 +666,9 @@
 
           case OFTString:
             // the string really ought to be escaped. 
-            sprintf( szFilter+strlen(szFilter), "\"%s\"", 
+            //sprintf( szFilter+strlen(szFilter), "\"%s\"", 
+            //       psSrcField->String );
+            sprintf( szFilter+strlen(szFilter), "'%s'", 
                      psSrcField->String );
             break;
 
@@ -685,8 +687,25 @@
         poJoinFeature = poJoinLayer->GetNextFeature();
 
         if( poJoinFeature == NULL )
+        {
+            for( int iField = 0; iField < psSelectInfo->result_columns; iField++ )
+            {
+                swq_col_def *psColDef = psSelectInfo->column_defs + iField;
+                if( psColDef->table_index == psJoinInfo->secondary_table )
+                {
+                    switch (poJoinLayer->GetLayerDefn()->GetFieldDefn(psColDef->field_index)->GetType())   
+                    {
+                      case OFTString:
+                        poDstFeat->SetField( iField, NULL);
+                        break;
+
+                      default:
+                        break;
+                    }
+                }
+            }
             continue;
-
+        }
         // Copy over selected field values. 
         for( int iField = 0; iField < psSelectInfo->result_columns; iField++ )
         {