Skip to main content
simply more productive

The Data Specialist

Thanks to our intuitive transformation designer, you can convert data effortlessly to a variety of formats without knowing how to program. It’s not only efficient, it’s also easy to use.
Flowheater Download

SQL Heater Insert Into

More
7 years 4 months ago #2937 by JD Cox
SQL Heater Insert Into was created by JD Cox
This is the first time I've tried to write back to the read db I'm fine with the parameters but I'm getting a Syntax error.
INSERT INTO ttblXports([strDIR], [strFileName]) VALUES '$DIR$', '$FileName$'



Thanks for all your help, JD
Attachments:

Please Log in to join the conversation.

More
7 years 4 months ago - 7 years 4 months ago #2940 by FlowHeater-Team
Replied by FlowHeater-Team on topic SQL Heater Insert Into
Hi JD,

I guess I’ve just forgot the bracket for the SQL values section!

SQL Heater Troubleshooting) You can easly check the syntaxt after you receive the error. Open the red marked SQL Heater and press the „Reveal effective SQL statement after parameter subtitution“. Now you see the real SQL Statement that you can execute on the database to check the syntax.

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Last edit: 7 years 4 months ago by FlowHeater-Team.

Please Log in to join the conversation.

More
7 years 4 months ago #2942 by JD Cox
Replied by JD Cox on topic SQL Heater Insert Into
I do reveal the effects to be sure I have the parameters set right, and all looked good.
I added the parenthesis to the VALUE section...thanks sorry I missed that.
I'm no longer getting the error but it does not insert the data and it makes the table I'm trying to write to Read-Only.

FYI, this is not the same table I'm exporting from, does that matter?

Please Log in to join the conversation.

More
7 years 4 months ago #2943 by FlowHeater-Team
Replied by FlowHeater-Team on topic SQL Heater Insert Into
Hi JD,

you can use any table on the selected side/database inside the SQL Heater that should be not the problem.

Do you use on READ and WRITE the same database? In this case you have to switch „Which side“ to „WRITE“. The Reason why is FlowHeater use database transactions uasally just for the WRITE side. If you use an SQL Heater on the READ FlowHeater creates automatically a new transaction on READ and this transaction blocks the WRITE side if the same database.

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Please Log in to join the conversation.

More
7 years 4 months ago #2944 by JD Cox
Replied by JD Cox on topic SQL Heater Insert Into
To give you a little more info the Read side is an AccessAdapter (Front end) where we create an end of day file for our customers. When we export that file Write TextFileAdapter (.csv) I want to record some of that transaction back in a table on the Read side where we'll use that on the customer portal so they can retrieve their own files.

Thanks, JD

Please Log in to join the conversation.

More
7 years 4 months ago - 7 years 4 months ago #2945 by FlowHeater-Team
Replied by FlowHeater-Team on topic SQL Heater Insert Into
Hi JD,

for me it´s not clear what you mean with „makes the table I'm trying to write to Read-Only“. Did you receive another error message or does the export hang?

I´ve made a short example for you including an Access database with test data, please have a look to the attachment.

For a bit more analytic would it be posible to post your created FlowHeater Defintionen.

File Attachment:

File Name: access-exp...sert.zip
File Size:85 KB

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Attachments:
Last edit: 7 years 4 months ago by FlowHeater-Team.

Please Log in to join the conversation.

More
7 years 4 months ago #2946 by JD Cox
Replied by JD Cox on topic SQL Heater Insert Into
Well I was hoping you would not have to go to that much trouble but I think I see the problem.
First, it locks the table I'm trying to write to. When I close fh it frees up the table but I notice it does use the auto numbers.
So it is getting to the right place. If I manually key in data, auto number, skips the number of tries with fh.

But, thanks to your sample I see there is one difference in the sql heater, the adapter dropdown selection.
could that be the cause?

Please Log in to join the conversation.

More
7 years 4 months ago #2947 by FlowHeater-Team
Replied by FlowHeater-Team on topic SQL Heater Insert Into
Hi JD,

The Adapter dropdown has been changed with version 4.0.5 . This change was just for multi adapter Definitions and should be no affect in singel adapter Definitions (one adapter on each side). Which version do you use, does the example work like expected?

Please try to switch of “Use database transaction” on the advanced tab for the Access Adapter on the READ side.

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Please Log in to join the conversation.

More
7 years 4 months ago #2948 by JD Cox
Replied by JD Cox on topic SQL Heater Insert Into
V3.5.3

But you’re right it does not matter. I’ll spare you all the details but I recreated your example on our environment and it worked fine. But, when I do the same thing in the def I’m working or it does not work. I’m going to start fresh next week if I discover the problem I’ll be sure to let you know.

Thanks or all your help.

JD

Please Log in to join the conversation.

More
7 years 4 months ago #2951 by JD Cox
Replied by JD Cox on topic SQL Heater Insert Into

File Attachment:

File Name: xport_test.zip
File Size:14 KB


Here’s a reduced copy of what I’m working with. I hope you can tell me what it is I’m doing wrong.
Attachments:

Please Log in to join the conversation.

More
7 years 4 months ago #2952 by FlowHeater-Team
Replied by FlowHeater-Team on topic SQL Heater Insert Into
Hi JD,

I’ve found the issue. The issue is related to the Sort Heater and the “Memory” execution mode. Use instead a “ORDER BY” in the Access Adapter and the “Massdata” mode and the defintion will run.

For example: select * from [tblzOrdersXport] order by strOrderNumber

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.

Please Log in to join the conversation.

More
7 years 4 months ago #2953 by JD Cox
Replied by JD Cox on topic SQL Heater Insert Into
As always, Robert I can’t thank you enough. I never thought about order by in the adapter. It works great and this is going to be a big help on a current project. On a side note we’ll be updating to 4.0 soon I’m really looking forward to flowheater server but we need to make some upgrades on our side first. Thanks again, JD

Please Log in to join the conversation.

Time to create page: 0.426 seconds

FlowHeater - The Data Specialist

Efficient data integration and transformation with FlowHeater – the perfect solution for a seamless transfer of your data.

Rechtliches

Support & Contact

Kontaktinformation

Telefon:0951 / 99339792 E-Mail:This email address is being protected from spambots. You need JavaScript enabled to view it.

Copyright © 2009-2024 by FlowHeater GmbH. All rights reserved.