TOPIC: SQL Heater Insert Into

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

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.

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?

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.

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

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.

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?

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.

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

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

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

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