Skip to main content
Solved

Replace function in SQLExecutor? Or any alternative?

R
Forum|alt.badge.img
Hello all,

 

 

I'm trying to update an Access table of mine using generated GUIDs, lets say I have the following:

 

Table_one

 

PARENT_GUID,FIELD_GUID

 

ABC123-EFT1,TEST23

 

ABC123-EFT1,TEST24

 

ABC123-EFT2,TEST23

 

ABC123-EFT2,TEST25

 

 

Also:

 

Table_two

 

PARENT_GUID,MEMO_FILLED_WITH_GUIDS

 

ABC123-EFT1,#IF##AND##CONDITION#{TEST23}ISNULL#ENDCONDITION##THEN##DO#{TEST24}ISMANDATORY

 

ABC123-EFT2,#IF##AND##CONDITION#{TEST23}ISNULL#ENDCONDITION##THEN##DO#{TEST25}ISMANDATORY

 

 

Now, due to some reasons, as you can see the FIELD_GUID is non-unique. I want to make it unique, and replace the FIELD_GUIDs with freshly replaced ones. However, seeing Table_two.MEMO_FILLED_WITH_GUIDS is a memo field filled with guids, I'd thought of using the replace function, within a SQLExecutor, like this:

 

 

UPDATE Table_two SET MEMO_FILLED_WITH_GUIDS= replace(MEMO_FILLED_WITH_GUIDS, @Value(FIELD_GUID, @Value(NEW_GUID_GENERATED)) WHERE PARENT_GUID = @Value(PARENT_GUID)

 

 

Where NEW_GUID_GENERATED is a GUID generated using FME.

 

 

However, this results in the following:

 

ERROR |MS Access Reader: Query failed. (-2147217900) Undefined function 'replace' in expression.'

 

 

Is there a way I can still use replace? Or is there a FME transformer which I can use to replace a value in a string based on another column, and use the PARENT_GUID in a where kinda clause?

 

 

Thanks in advance!

Best answer by takashi

Hi,

 

 

I'm not sure why the replace function cannot be used in the SQLExecutor, but this way could be a workaround.

 

1) Use an SQLExecutor to merge MEMO_FILLED_WITH_GUIDS to the initiator features.

 

SQL Statement:

 

-----

 

SELECT MEMO_FILLED_WITH_GUIDS

 

FROM Table_two WHERE PARENT_GUID = '@Value(PARENT_GUID)'

 

-----

 

Attributes to Expose: MEMO_FILLED_WITH_GUIDS

 

Combine Attributes: Keep Initiator (or Result) Attributes if Conflict

 

Note: Maybe the Joiner can be also used instead of the SQLExecutor.

 

 

2) Send Result features into a StringReplacer transformer to replace FIELD_GUID with NEW_GUID_GENERATED.

 

Attributes: MEMO_FILLED_WITH_GUIDS

 

Text to Match: FIELD_GUID (Attribute Value)

 

Replacement Text: NEW_GUID_GENERATED (Attribute Value)

 

 

3) Send Output features into 2nd SQLExecutor

 

SQL Statement:

 

-----

 

UPDATE Table_two

 

SET MEMO_FILLED_WITH_GUIDS = '@Value(MEMO_FILLED_WITH_GUIDS)'

 

WHERE PARENT_GUID = '@Value(PARENT_GUID)'

 

-----

 

 

Hope this helps,

 

Takashi
View original
Did this help you find an answer to your question?
This post is closed to further activity.
It may be a question with a best answer, an implemented idea, or just a post needing no comment.
If you have a follow-up or related question, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

5 replies

takashi
Influencer
  • takashiInfluencer
  • Best Answer
  • May 18, 2014
Hi,

 

 

I'm not sure why the replace function cannot be used in the SQLExecutor, but this way could be a workaround.

 

1) Use an SQLExecutor to merge MEMO_FILLED_WITH_GUIDS to the initiator features.

 

SQL Statement:

 

-----

 

SELECT MEMO_FILLED_WITH_GUIDS

 

FROM Table_two WHERE PARENT_GUID = '@Value(PARENT_GUID)'

 

-----

 

Attributes to Expose: MEMO_FILLED_WITH_GUIDS

 

Combine Attributes: Keep Initiator (or Result) Attributes if Conflict

 

Note: Maybe the Joiner can be also used instead of the SQLExecutor.

 

 

2) Send Result features into a StringReplacer transformer to replace FIELD_GUID with NEW_GUID_GENERATED.

 

Attributes: MEMO_FILLED_WITH_GUIDS

 

Text to Match: FIELD_GUID (Attribute Value)

 

Replacement Text: NEW_GUID_GENERATED (Attribute Value)

 

 

3) Send Output features into 2nd SQLExecutor

 

SQL Statement:

 

-----

 

UPDATE Table_two

 

SET MEMO_FILLED_WITH_GUIDS = '@Value(MEMO_FILLED_WITH_GUIDS)'

 

WHERE PARENT_GUID = '@Value(PARENT_GUID)'

 

-----

 

 

Hope this helps,

 

Takashi
R
Forum|alt.badge.img
  • ragje
  • Author
  • May 19, 2014
Hey Takashi,

 

 

That would nearly work! The thing is, MEMO_FILLED_WITH _GUIDS could theoretically contain an infinite amount of unique GUIDs (Well, up to max memo field length / GUID length, but that doesn't matter).

 

 

For the example above, I'd have to run the Workbench twice, seeing it once would replace the value:

 

#IF##AND##CONDITION#{TEST23}ISNULL#ENDCONDITION##THEN##DO#{TEST25}ISMANDATORY

 

to:

 

#IF##AND##CONDITION#{N3W-TEST23}ISNULL#ENDCONDITION##THEN##DO#{TEST25}ISMANDATORY

 

but in the same run:

 

#IF##AND##CONDITION#{TEST23}ISNULL#ENDCONDITION##THEN##DO#{N3W-TEST25}ISMANDATORY

 

 

And on second run:

 

#IF##AND##CONDITION#{TEST23}ISNULL#ENDCONDITION##THEN##DO#{N3W-TEST25}ISMANDATORY

 

 

to:

 

#IF##AND##CONDITION#{N3W-TEST23}ISNULL#ENDCONDITION##THEN##DO#{N3W-TEST25}ISMANDATORY

 

 

So the best way would be a replace function, to just replace the string that needs replacing, instead of the whole attributevalue... But how...

 

david_r
Celebrity
  • david_rCelebrity
  • May 19, 2014
Hi,

 

 

rather than using the FME Replace function, you should probably be using the MS Access Replace() function in stead.

 

 

See the documentation here (http://www.techonthenet.com/access/functions/string/replace.php).

 

 

David
R
Forum|alt.badge.img
  • ragje
  • Author
  • May 19, 2014
Hey David,

 

 

I'm afraid that is the Replace() function from Acces ;) 

 

 

However, Access doesn't support batch query running, and I found out Replace() function isn't usable anywhere outside of Access... And I don't want to run 1200 queries (Rows in Table_one) by hand.

 

 

I've used a workaround for now, exporting Table_two to SQLite, then using Replace(), then importing it back to Access.

 

 

To be honest, I'll be happy when I finally won't have to use Access anymore.

 

 

For me this is solved for now, if anyone knows a better solution, that would be awesome.

 

 

Thanks to you both!
david_r
Celebrity
  • david_rCelebrity
  • May 19, 2014
It might be that your Access database is too old to support the Replace function in the API calls.

 

 

There is a nice workaround here (http://www.access-programmers.co.uk/forums/showthread.php?t=142513). Might be worth a try.

 

 

David

Helpful Members This Week

Community Stats

31,331
Posts
118,863
Replies
38,957
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
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.