Dynamic data import / export

datenbank

simply more productive
Up to 10,000 records can be processed with the freeware version!
Welcome, Guest
Username: Password: Remember me
Welcome to the FlowHeater support forum.

Questions about using FlowHeater are answered here.
  • Page:
  • 1

TOPIC: SQL Heater Insert Into

SQL Heater Insert Into 3 months 3 days ago #2937

  • JD Cox
  • JD Cox's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 39
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 or Create an account to join the conversation.

SQL Heater Insert Into 3 months 3 days ago #2940

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Administrator
  • Posts: 243
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.

Please Log in or Create an account to join the conversation.

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: by FlowHeater-Team.

SQL Heater Insert Into 3 months 2 days ago #2942

  • JD Cox
  • JD Cox's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 39
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 or Create an account to join the conversation.

SQL Heater Insert Into 3 months 2 days ago #2943

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Administrator
  • Posts: 243
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.

Please Log in or Create an account to join the conversation.

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.

SQL Heater Insert Into 3 months 2 days ago #2944

  • JD Cox
  • JD Cox's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 39
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 or Create an account to join the conversation.

SQL Heater Insert Into 3 months 2 days ago #2945

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Administrator
  • Posts: 243
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-export-and-insert.zip
File Size: 85 KB
Attachments:

Please Log in or Create an account to join the conversation.

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: by FlowHeater-Team.

SQL Heater Insert Into 3 months 1 day ago #2946

  • JD Cox
  • JD Cox's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 39
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 or Create an account to join the conversation.

SQL Heater Insert Into 3 months 1 day ago #2947

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Administrator
  • Posts: 243
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.

Please Log in or Create an account to join the conversation.

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.

SQL Heater Insert Into 3 months 13 hours ago #2948

  • JD Cox
  • JD Cox's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 39
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 or Create an account to join the conversation.

SQL Heater Insert Into 2 months 4 weeks ago #2951

  • JD Cox
  • JD Cox's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 39

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 or Create an account to join the conversation.

SQL Heater Insert Into 2 months 4 weeks ago #2952

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Administrator
  • Posts: 243
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

Please Log in or Create an account to join the conversation.

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.

SQL Heater Insert Into 2 months 3 weeks ago #2953

  • JD Cox
  • JD Cox's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 39
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 or Create an account to join the conversation.

  • Page:
  • 1
Moderators: FlowHeater-Team
Time to create page: 0.199 seconds