I'm trying to perform a SQL query on a TAB file.
The error message I get indicates that I should check the CanExecuteSqlQueries property on the layer which is false.
Is this correct? The corresponding DAT file is a dbase file and I can get properties from it for the selected feature.
Is there any way to perform sql queries on tab files?
Cheers
Steve
I see from a previous answer that sql queries are not supported on tab files. The answer was back in February. Any idea when this will be added? Makes tab files useless for me if I cannot search and highlight a particular feature.
Cheers
Steve
Hi Steve,
Sorry to tell you we haven’t find a way to access the .dat file of the tab file. The dat file has the same structure as dbf file, we tried to change the extension of dat file to dbf, it could be queried by Oledb, but when we change it back, it can not. So we’ll keep on doing the research on this and maybe one day we’ll find out the solution.
Regards,
Edgar
Hi Edgar
I’ll see if I can find a way round this that you can use.
OleDb causes me problems with other files. If I have lots of shape files with long filenames where the first 8 characters are the same, it can confuse OleDb which seems to use short filenames.
Cheers
Steve
Hello Steve,
Thanks for your help, please let us know if there is a way.
Also don’t hesitate to let us know your problem, we are happy to help you.
Regards,
Gary
Hi Gary
One possible solution is for us to create a copy of the .DAT file as a .DBF file. This depends on how the layer determines CanExecuteSqlQuery - is this based purely on the layer type or is it determined by if there is a .DBF file present? If the former can this be changed to the latter then I will be able to work round the missing search functionality.
Regards
Steve
Steve,
Thank you for you suggest, creating a copy does solve the execute problem, but what time to copy that? Only two ideas come to my mind,
1), before the executing, copy the file and if there are changes, replace the original file.
2), copy the file when Opening and replace the file when closing.
But the two ways both have the performance issue if the file is big. We’ll continue working on this till we find a solution.
Regards,
Edgar
Hi Edgar
I'm not suggesting you should copy the file. What I'm suggesting is that you should check to see if there is a .dbf and if so allow a sql query to be run.
I have a GIS utility that my customers use to import GIS layers into our system. As part of an import I generate index files. I can also copy .dat files as .dbf files.
My main problem is that I think you turn off sql queries for .tab files. What I'm asking is for you not to do this but instead to check if there is a .dbf file.
Cheers
Steve
Oh I see, I think there is a workaround for you.
public class MyTabFeatureLayer : TabFeatureLayer
{
public MyTabFeatureLayer()
: this(string.Empty, TabFileReadWriteMode.ReadOnly)
{ }
public MyTabFeatureLayer(string fileName)
: this(fileName, TabFileReadWriteMode.ReadOnly)
{ }
public MyTabFeatureLayer(string fileName, TabFileReadWriteMode mode)
{
FeatureSource = new MyTabFeatureSource(fileName, mode);
}
}
public class MyTabFeatureSource : TabFeatureSource
{
public MyTabFeatureSource()
: base()
{ }
public MyTabFeatureSource(string fileName)
: base(fileName)
{ }
public MyTabFeatureSource(string fileName, TabFileReadWriteMode mode)
: base(fileName, mode)
{ }
protected override bool CanExecuteSqlQueryCore
{
get
{
return true;
}
}
protected override DataTable ExecuteQueryCore(string sqlStatement)
{
string fileName = Path.GetDirectoryName(this.TabPathFilename) + “\” + Path.GetFileNameWithoutExtension(this.TabPathFilename) + “.dbf”;
if (!File.Exists(fileName))
{
File.Copy(this.TabPathFilename, fileName);
}
OleDbConnection conn = new OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + Path.GetDirectoryName(this.TabPathFilename) + “\;Extended Properties=dBASE III”);
DataTable dt = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(sqlStatement, conn);
conn.Open();
adapter.Fill(dt);
conn.Dispose();
adapter.Dispose();
return dt;
}
}
The sample code just overrided the ExecuteQueryCore method, you can overrided the others like that. We’ll keep on researching to find a good solution.
Hope it helps,
Edgar
Thanks Edgar. That works great.
I have found a way to access the .DAT files from a SQL query. If you perform a query like select * from parcels then the odbc driver looks for a parcels.dbf file. If you perform a query like select * from parcels.dat then you can specify the exact file to query. Tried it and it seems to work. May help you move forward.
One more question. How do you link the row in the query result set to the shape? In a shape file you can add a recid column using the ShapeFileFeatureLayer.BuildRecordIdColumn command. There does not appear to be a similar command for a TabFeatureLayer?
Cheers
Steve
Hi Edgar
After further testing my suggestion above about adding the extension to the query does not work.
I still need the answer to the question: How do you link the row in the query result set to the shape in tab files? In a shape file you can add a recid column using the ShapeFileFeatureLayer.BuildRecordIdColumn command. There does not appear to be a similar command for a TabFeatureLayer?
Cheers
Steve
Steve,
The tab file’s structure is not the same as the shape file, e.g. the first record’s column value might not be the first record in dat file, so the records in .map file and .dat file are not one on one. The only way to get the feature from the information in table is the following code,
Collection<Feature> features = new Collection<Feature>();
foreach (DataRow item in ta.Rows)
{
//Assume the first column is the “ID” column and it’s the primary key.
string columnValue = item.ItemArray[0].ToString();
features.Add(tab.QueryTools.GetFeaturesByColumnValue(“ID”, columnValue)[0]);
}
Hope it helps,
Edgar
Hi Edgar
Thanks for the code. Unfortunately it does not work. The first column in the dat file is not an ID. I cannot see any column that could be an ID.
How does it work going from the shape to the database? This works OK - if I select a shape from the layer I can get all columns so there must be a link from the shape to the database. If so I should be able to follow it the other way.
I have attached the tab file set so you can examine it.
Cheers
Steve
Hi Steve,
Sorry for my previous reply that I told you the structure is different from the shape file, but in fact, they are the same, that means using GetFeatureById(n) can get the nth record in the dat file, so we added a method TabFeatureSource.BuildRecordIdColumn to build the Id Column in the dat file, after that you can get the features by
foreach (DataRow item in ta.Rows)
{
//Assume the last column is the "ID" column and it's the primary key.
string id = item.ItemArray[last].ToString();
features.Add(tab.QueryTools.GetFeatureById(id, ReturningColumnType.AllColumns);
}
please get 6.0.136.0 and have a try.
Regards,
Edgar
Thanks Edgar.
I cannot see 136 on the download area - only 135.
Cheers
Steve
The 136 will be available at tomorrow. Please wait for it.
Edgar
Hi Edgar
Have downloaded and tested version 136. Works fine on the tab file I sent but throws an ArgumentOutOfRangeException exception on one I created through map suite:
{"Index and length must refer to a location within the string.\r\nParameter name: length"}
at System.String.InternalSubStringWithChecks(Int32 startIndex, Int32 length, Boolean fAlwaysCopy)
at System.String.Substring(Int32 startIndex, Int32 length)
at ThinkGeo.MapSuite.Core.TabFeatureSource.BuildRecordIdColumn(String tabFileName, String fieldName, BuildRecordIdMode rebuildNeeded, Int32 startNumber, Encoding encoding)
at ThinkGeo.MapSuite.Core.TabFeatureLayer.BuildRecordIdColumn(String tabFileName, String fieldName, BuildRecordIdMode rebuildNeeded)
at Kern.Client.Windows.Controls.UserControls.MapBase.SaveTabAsset(String userAssetPath, Feature feature, IEnumerable`1 assetFieldValues, String safeJobNumber) in c:\TFS\KernMobile_V5\Main\Kern.Client.Windows\Kern.Client.Windows.Controls\UserControls\MapBase.xaml.cs:line 2430
I have attached the tab file that causes the issue.
Cheers
Steve
Thank you for your file, the issue has been fixed, please get the 6.0.137.0 and have a try.
Edgar
Hi Edgar
137 looks good thanks.
I see that BuildRecordIdColumn generates a dbf file. Does this mean that TAB file ExecuteQuery now looks for .dbf or do I still need to use my own?
Cheers
Steve