Skip to main content
Question

SQLCreator 2022 Exposing attributes from query does not expose any attributes, without any error message

  • June 20, 2023
  • 5 replies
  • 332 views
s.jager
Influencer
Forum|alt.badge.img+26

Hi,

 

I have a bunch of fairly complicated SQL queries, which I frequently use in an SQL Creator. This saves me a tremendous amount of featuremergers and such, because the sql basically re-creates the business object from the normalized database tables. Up to version 2020 this worked just fine: insert an SQLCreator, select DB Connection (SQL Server 2016 SP3), copy-and-paste SQL from my code snippets, click on the ellipsis to Expose attributes, click on Populate from SQL Query, and I'd be done.

 

Now that we've upgraded to 2022, that does not work anymore. The Attributes to Expose dialog stays empty, and no error messages or warnings appear in the log. It is rather annoying because I have to do this all the time, and now I have to copy the attribute names manually from my sql statement. It's causing me quite a bit of extra work.

 

Am I missing something? I am currently using FME(R) 2022.2.6.0 (20230523 - Build 22800 - WIN64) , and up to now this method has always worked just fine, no matter how complicated the sql (comments could sometimes cause a problem, but with-clauses, joins, where-clauses never were a problem). But now, with that selection of Schema, Schema&Data, Data selector, this does not work anymore.

 

TIA,

Stefan

 

5 replies

itsmatt
Celebrity
Forum|alt.badge.img+48
  • itsmattCelebrity
  • Celebrity
  • June 20, 2023

Hmm, it works ok for me. I did find though that I needed to make sure I wasn't using any @values in the call but that makes sense.

 

I'm on FME 2022.2.5

 

 

nielsgerrits
VIP
Forum|alt.badge.img+67

As an alternative, use an AttributeExposer downstream and choose Import From Feature Cache.

Buckle your seatbelt Dorothy, cause Kansas is going bye-bye...
s.jager
Influencer
Forum|alt.badge.img+26
  • s.jagerInfluencer
  • Author
  • Influencer
  • June 20, 2023

I'm not using any @ values or specific SQL Server dialect, it is plain ANSI SQL. Sometimes I use a value from a parameter, but that has never been a problem (just replace it with a value when exposing attributes, and it works just fine).

 

AttributeExposer is indeed a solution, but it is also a workaround for something that used to work perfectly - and is very convenient to use (when it works ;-) ). For now I guess I'll resort to doing that...

s.jager
Influencer
Forum|alt.badge.img+26
  • s.jagerInfluencer
  • Author
  • Influencer
  • June 20, 2023

Update: It seems that the With-clause is causing the problem. If I take that out, then it actually works. My biggest beef is, that this has worked in 2020... I've always used this, and it saves a lot of featuremergers, transformers, memory use, performance etc if I let the database do most of the work...

df_cos
Contributor
Forum|alt.badge.img+17
  • df_cosContributor
  • Contributor
  • August 25, 2026

Found this post as I was having a similar issue in 2022.2.5 (yes, we’ll be upgrading in the next few months) where attributes would not populate from SQL Query.  Although I could get around it by manually typing the attributes, the geometry would not come through as part of SQLCreator.  So the results consisted of an attribute table and no map.  Spent about 30 minutes investigating and trying to figure out why the geometries would not come through from the SQLCreator….removed the “ORDER BY” clause I had in the SQL query (which I had copied from testing in SQL Server)...and voila, the geometries appeared!  The attributes were then also able to be populated from the SQL query.

So in my case, removing the ORDER BY command from the SQL query got the “Populate from SQL Query” command working, and also brought all the geometries through.  Huzzah!

Probably common knowledge but thought I’d post in here in case it helps anyone with a similar issue in the future :-)

FME: Is there anything it can't do?!

    Community Stats

    32,887
    Posts
    124,752
    Replies
    41,523
    Members
    Terms & ConditionsAccessibility statement
    A product of
    Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.