Hexagon Geospatial
MENU

Developer Discussions

Discuss topics with other Hexagon Geospatial Power Portfolio developers and experts to get the most out of our products.
Showing results for 
Search instead for 
Do you mean 
Reply
Frequent Contributor
Posts: 155
Registered: ‎01-14-2018
Accepted Solution

Automating Join between 2 text files giving "Error from SQL parser or SQL syntax error" message

Hello all,

 

I made a connection to 2 textfiles(called: .PNT, .PN2) successfully in c#, now I am trying to join between these 2 connections, I can make this through the GUI and it succeeded, but when trying the below code it gives me the error message " Error from SQL parser or SQL syntax error".

 

 

PClient.Connection pClientConnectionPNT = new PClient.Connection(); 
PClient.Connection pClientConnectionPN2 = new PClient.Connection(); PClient.OriginatingPipe _PNT_origPipe = null; PClient.OriginatingPipe _PN2_origPipe = null; PClient.GRecordset _PNT_pclientRS; PClient.GRecordset _PN2_pclientRS; //PNT pClientConnectionPNT.Type = "TextFile.GDatabase";
pClientConnectionPNT.Name = "Text File Connection PNT";
pClientConnectionPNT.Location = @"C:\Users\A\Desktop\SDRFiles\AG617394d26.PNT";
pClientConnectionPNT.ConnectInfo = @"DATA=C:\Users\A\Desktop\SDRFiles\AG617394d26.PNT;FORMAT=C:\Users\A\Desktop\SDRFiles\TFD TNT.tfd;CSF=C:\Users\A\Desktop\SDRFiles\EgyptRedBelt.csf";
pClientConnectionPNT.Mode = 0; pClientConnectionPNT.Connect(); pClientConnectionPNT.CreateOriginatingPipe(out _PNT_origPipe); _PNT_origPipe.Table = "_PNT_Table"; _PNT_pclientRS = _PNT_origPipe.OutputRecordset;
//PN2 pClientConnectionPN2.Type = "TextFile.GDatabase";
pClientConnectionPN2.Name = "Text File Connection PN2"; pClientConnectionPN2.Location = @"C:\Users\A\Desktop\SDRFiles\AG617394d26.PN2";
pClientConnectionPN2.ConnectInfo = @"DATA=C:\Users\A\Desktop\SDRFiles\AG617394d26.PN2;FORMAT=C:\Users\A\Desktop\SDRFiles\TFD PN2.tfd;CSF=C:\Users\A\Desktop\SDRFiles\EgyptRedBelt.csf";
pClientConnectionPN2.Mode = 0; pClientConnectionPN2.Connect(); pClientConnectionPN2.CreateOriginatingPipe(out _PN2_origPipe); _PN2_origPipe.Table = "_PN2_Table"; _PN2_pclientRS = _PN2_origPipe.OutputRecordset;

Note: I tried to change the type of 2 GRecordsets (_PNT_pclientRS, _PN2_pclientRS)  with GDO or PDBPipe but gave the same error.

 

 

Another question: Should I create an "EquiJoinPipe" to make the join between the 2 textfiles?

 

Thanks Smiley Happy

Khaled

 

Technical Evangelist
Posts: 1,032
Registered: ‎09-11-2015

Re: Automating Join between 2 text files giving "Error from SQL parser or SQL syntax error&quot

Hi Khaled,

 

The table name of the Originating Pipe should be the file name (without extension), so in your case AG617394d26.

 

This code (with sample data) works fine:

PClient.Connection pClientConnectionPNT = new PClient.Connection();
PClient.OriginatingPipe _PNT_origPipe = null;
PClient.GRecordset _PNT_pclientRS;

pClientConnectionPNT.Type = "TextFile.GDatabase";
pClientConnectionPNT.Name = "Text File Connection PNT";
pClientConnectionPNT.Location = @"C:\Temp\GNIS\Madison_Co_GNIS.txt";
pClientConnectionPNT.ConnectInfo = @"DATA=C:\Temp\GNIS\Madison_Co_GNIS.txt;FORMAT=C:\Temp\GNIS\GNIS_pipe.tfd;CSF=C:\Temp\GNIS\GeographicWGS84.csf";
pClientConnectionPNT.Mode = 0;
pClientConnectionPNT.Connect();

pClientConnectionPNT.CreateOriginatingPipe(out _PNT_origPipe);

_PNT_origPipe.Table = "Madison_Co_GNIS";
_PNT_pclientRS = _PNT_origPipe.OutputRecordset;
_PNT_pclientRS.MoveFirst();
_PNT_pclientRS.MoveLast();
// just for testing purposes
int cnt = _PNT_pclientRS.RecordCount;

And yes, EquiJoinPipe is used for building JOIN queries.

 

Regards,

Jan

Jan Neumann
Post Sales Engineer Web Applications
Hexagon Geospatial

Frequent Contributor
Posts: 155
Registered: ‎01-14-2018

Re: Automating Join between 2 text files giving "Error from SQL parser or SQL syntax error&

Thank you @jan.neumann 

 

I have another question regarding the equijoin:

I made an equijoin object to join between the 2 text files, and the 2 fields I want to join are both named "Point",

I got an error as follows:

 

The join field names property has not been set, or it is not set correctly. All non-union joins require at least one field to join on. The field names have to go in a 2-dimensional array, each dim corresponding to the Left and Right Recordsets.

 

and my code is below:

 

        PDBPipe.EquijoinPipe _Points_equijoinPipe = new PDBPipe.EquijoinPipe();

        //Points files fields names array
        string [,] PointsArray = new string[1, 2];
        PointsArray[0, 0] = "Point";
        PointsArray[0, 1] = "Point";

        _Points_equijoinPipe.LeftRecordset = _PNT_GRecordset as PDBPipe.GRecordset;
        _Points_equijoinPipe.RightRecordset = _PN2_GRecordset as PDBPipe.GRecordset;
        _Points_equijoinPipe.JoinFieldNames = PointsArray; it gives me the error here
        _Points_equijoinPipe.JoinType = 2;

Thanks

Khaled

Technical Evangelist
Posts: 1,032
Registered: ‎09-11-2015

Re: Automating Join between 2 text files giving "Error from SQL parser or SQL syntax error&

Hi Khaled,

 

Try with this array - it has to be 2x1

 

//Points files fields names array
string [,] PointsArray = new string[2, 1];
PointsArray[0, 0] = "Point";
PointsArray[1, 0] = "Point";
Jan Neumann
Post Sales Engineer Web Applications
Hexagon Geospatial

Highlighted
Frequent Contributor
Posts: 155
Registered: ‎01-14-2018

Re: Automating Join between 2 text files giving "Error from SQL parser or SQL syntax error&

Thanks Jan,

 

When I use the GUI to make this Join, this join is automatically appearing available to be added to the legend entry. I think because it is also saved as a query.

So I made a code to make a query as follows:

PDBPipe.Queries myQueries = null;
PDBPipe.Query myQuery;
myQuery = _Points_Joined_GRecordset.GetExtension("A") as PDBPipe.Query;
myQuery.Name = "Join of PNT and PN2";

myQueries.Append(myQuery);

I got an error "Extension, A, not found".

 

So should I change the string types in the GetExtension("") to something else?

 

Thanks 

Khaled

Technical Evangelist
Posts: 1,032
Registered: ‎09-11-2015

Re: Automating Join between 2 text files giving "Error from SQL parser or SQL syntax error&

Hi Khaled,

 

Please check "Extensions & Extended Properties" topic in the GM Object Reference. It lists only these extensions for GRecordset:

 

  • The following extensions may be retrieved:
    Extension Name Object Type Returned
    Name Query
    Notification Notification
    Extended Property Set Extended Property Set
    Equality EqualityExtension
    Errors GErrors

In your case, you should use "Name"

 

Jan Neumann
Post Sales Engineer Web Applications
Hexagon Geospatial

Frequent Contributor
Posts: 155
Registered: ‎01-14-2018

Re: Automating Join between 2 text files giving "Error from SQL parser or SQL syntax error&

Hello jan,

 

I wrote the string as "Name" and made an instance of PDBPipe.Queries then appended my qury in it but it didn't appear in the Queries so as I can add it to the legend

 

PDBPipe.Queries myQueries = new PDBPipe.Queries();
PDBPipe.Query myQuery;
myQuery = _Points_Joined_GRecordset.GetExtension("Name") as PDBPipe.Query;
myQuery.Name = "Join of PNT and PN2";

PDBPipe.QueryFolder myQueryFolder = this._document.QueryFolder as PDBPipe.QueryFolder;
myQueries.Append(myQuery);

Thanks

Khaled 

Technical Evangelist
Posts: 1,032
Registered: ‎09-11-2015

Re: Automating Join between 2 text files giving "Error from SQL parser or SQL syntax error&

Hi Khaled,

 

As I said previously there is a couple of conditions that you must fulfil:

  1. Connection must be created via CreateService method, in order to keep them in the same memory address space as GeoMedia.exe
  2. Both connections must be added to the Document's Connections object
  3. Queries folder is actualy nested on the second level in QueryFolders hierachy (on the same level with Features)

This code will work:

Intergraph.GeoMedia.GeoMedia.Document objDoc = (Intergraph.GeoMedia.GeoMedia.Document)gobjGeoApp.Document;

PClient.Connections docConnections = objDoc.Connections as PClient.Connections;
PClient.Connection pClientConnectionPNT = gobjGeoApp.CreateService("GeoMedia.Connection") as PClient.Connection;
PClient.Connection pClientConnectionPN2 = gobjGeoApp.CreateService("GeoMedia.Connection") as PClient.Connection;

PClient.OriginatingPipe _PNT_origPipe = null;
PClient.OriginatingPipe _PN2_origPipe = null;

PClient.GRecordset _PNT_pclientRS;
PClient.GRecordset _PN2_pclientRS;


//PNT 
pClientConnectionPNT.Type = "TextFile.GDatabase";
pClientConnectionPNT.Name = "Text File Connection PNT";
pClientConnectionPNT.Location = @"C:\Temp\GNIS\Madison_Co_GNIS.txt";
pClientConnectionPNT.ConnectInfo = @"DATA=C:\Temp\GNIS\Madison_Co_GNIS.txt;FORMAT=C:\Temp\GNIS\GNIS_pipe.tfd;CSF=C:\Temp\GNIS\GeographicWGS84.csf";
pClientConnectionPNT.Mode = 0;
docConnections.Append(pClientConnectionPNT);
pClientConnectionPNT.Connect();

pClientConnectionPNT.CreateOriginatingPipe(out _PNT_origPipe);

_PNT_origPipe.Table = "Madison_Co_GNIS";
_PNT_pclientRS = _PNT_origPipe.OutputRecordset;

//PN2 
pClientConnectionPN2.Type = "TextFile.GDatabase";
pClientConnectionPN2.Name = "Text File Connection PN2";
pClientConnectionPN2.Location = @"C:\Temp\GNIS\Sample.txt";
pClientConnectionPN2.ConnectInfo = @"DATA=C:\Temp\GNIS\Sample.txt;FORMAT=C:\Temp\GNIS\GNIS_pipe.tfd;CSF=C:\Temp\GNIS\GeographicWGS84.csf";
pClientConnectionPN2.Mode = 0;
docConnections.Append(pClientConnectionPN2);
pClientConnectionPN2.Connect();

pClientConnectionPN2.CreateOriginatingPipe(out _PN2_origPipe);

_PN2_origPipe.Table = "Sample";
_PN2_pclientRS = _PN2_origPipe.OutputRecordset;

PDBPipe.EquijoinPipe _Points_equijoinPipe = gobjGeoApp.CreateService("GeoMedia.EquijoinPipe") as PDBPipe.EquijoinPipe;

string[,] PointsArray = new string[2, 1];
PointsArray[0, 0] = "County_Name";
PointsArray[1, 0] = "County_Name";

_Points_equijoinPipe.LeftRecordset = _PN2_pclientRS as PDBPipe.GRecordset;
_Points_equijoinPipe.RightRecordset = _PNT_pclientRS as PDBPipe.GRecordset;
_Points_equijoinPipe.JoinFieldNames = PointsArray;
_Points_equijoinPipe.JoinType = 2;

PClient.GRecordset outRS = (PClient.GRecordset)_Points_equijoinPipe.OutputRecordset;

PDBPipe.Query myQuery = outRS.GetExtension("Name") as PDBPipe.Query;
myQuery.Name = "My Query1";

PDBPipe.QueryFolder qF = (PDBPipe.QueryFolder)objDoc.QueryFolder;
qF = qF.QuerySubFolders["Queries"];
qF.Queries.Append(myQuery);
Jan Neumann
Post Sales Engineer Web Applications
Hexagon Geospatial

Do you need immediate support?
Please submit a Ticket through our
Development Ticket Portal.