[flexcoders] Re: SQL Insert operation fails silently
Greg, My apologies for not getting back to the board sooner but at least I come bearing solutions. The problem was both an Adobe bug and problems with my code and the dB. Like the perfect storm it all came together to deny me the simple pleasure of an INSERT operation. Flex updater 2.0.1 fixed the child form INSERT problem (Thank you very much to Farah Gron and her team for working with me and including the fix in the update). For a child form (usually a dataGrid) inside a parent, the ArrayCollection that holds the child data is not populated correctly when adding a new record. The fix was to force the array from the array collection with this line: this.detailObject.childBeans = getChildBeansArray(); The wizard now puts this line just above this.datamanager.save(item) in the function saveItem(item:Object) Okay, that fixed Adobe's bug but still didn't work for me completely. In order to get the primary key from the parent table and insert it as the foreign key in the child, the wizard uses a SELECT statement with a WHERE clause that uses every field from the parent table. That might work and might not. In my case, several of those fields were not part of the parent form and so contained nulls (why my dB allowed nulls is a matter I'll have to discuss with my DBA -- oh yeah, that's me-- shoot!). What do you get when the WHERE clause returns nulls? You got it, nothing. I deleted those fields from the WHERE clause and added a timestamp to ensure I got the right foreign key and all is well in Flex land. PS the dB no longer allows nulls -- duh Thanks for your input Greg. It got me looking and helped out a great deal. Cheers, Dan --- In flexcoders@yahoogroups.com, greg h [EMAIL PROTECTED] wrote: Dan, Your writing the following makes me wonder if there might not be a conditional block somewhere that results in the INSERT child statement not being executed. You wrote: The INSERT for the child table simply isn't even attempted. That would be sensible since executing INSERT child without the necessary foreign key would result in an error at run time :-) Could you check the code around whatever calls the insert for any IF/ELSE logic? Where the initial subject line was fails silently, maybe the error is that there is no logic to select back the newly inserted primary key for the PARENT, causing conditional logic to skip INSERT child since, again, executing INSERT child without the foreign key would result in a runtime error. So now I really, really am curious. So please do post back once you get it all working. g On 12/20/06, nall_daniel [EMAIL PROTECTED] wrote: Thanks Greg, Your advice is appreciated and helpful. I'm using MS SQL server, CF 7.02, and of course Flex 2. I have nothing in my CF logs, nothing in SQL logs (SQL errors show up in the browser anyway ie fault=server_fault(event)). I use the Flash Tracer extension for Firefox (very helpful BTW) and see nothing there either. The INSERT for the child table simply isn't even attempted. I've contacted Adobe and they have verified the problem and submitted it as a bug. In the meantime, I have done this with CF flash forms before so I'll try the same technique I used there. INSERT parent, Lock the dB, grab primary key, INSERT child, release lock. We'll see. If I can get it working I'll definately post a solution. Hopefully Adobe will beat me to the punch. Thanks again... Dan
[flexcoders] Re: SQL Insert operation fails silently
Thanks Greg, Your advice is appreciated and helpful. I'm using MS SQL server, CF 7.02, and of course Flex 2. I have nothing in my CF logs, nothing in SQL logs (SQL errors show up in the browser anyway ie fault=server_fault(event)). I use the Flash Tracer extension for Firefox (very helpful BTW) and see nothing there either. The INSERT for the child table simply isn't even attempted. I've contacted Adobe and they have verified the problem and submitted it as a bug. In the meantime, I have done this with CF flash forms before so I'll try the same technique I used there. INSERT parent, Lock the dB, grab primary key, INSERT child, release lock. We'll see. If I can get it working I'll definately post a solution. Hopefully Adobe will beat me to the punch. Thanks again... Dan --- In flexcoders@yahoogroups.com, greg h [EMAIL PROTECTED] wrote: Dan, What database are you using? e.g. MySQL, MS SQL 2000, Oracle, DB2, Access, etc? Database vendor's implementations vary on how they automatically generate surrogate primary keys. Oracle: sequencehttp://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/views.htm#sthref3092 MySQL: AUTO_INCREMENThttp://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html(see Forta re: capturing using ColdFusion http://www.forta.com/blog/index.cfm?mode=entryentry=A61BCDEA-3048-80A9-EFE79327EB7554DC ) Microsoft: @@identityhttp://msdn2.microsoft.com/en-us/library/ms187342.aspx (see Forta re: capturing using ColdFusionhttp://www.forta.com/blog/index.cfm?mode=eentry=221 ) etc. Basically, after the parent row is inserted, your logic must know the new primary key value before executing the insert into the child row. I just cross checked for questions like this over on the CF-Talk listhttp://www.houseoffusion.com/groups/CF-Talk/and found the following link detailing How do I grab the ID of the record I just created?: http://mysecretbase.com/get_the_last_id.cfm Regarding the code generated by the CF Wizard, I have not yet attempted using it for any parent/child insert operations like you are attempting. If the code generated by the wizard is deficient in capturing the necessary foreign key identifier before inserting the child, then you will need to edit the code that it generates for you. btw ... the error on the child inserts likely is not failing silently. It may be in your ColdFusion logs. Or if you start ColdFusion from the command prompthttp://www.forta.com/blog/index.cfm?mode=entryentry=A61BD25F-3048-80A9-EF9FCB7441C5C6C0you will see all errors on the console. It only appears to be silent because there is no handling for it to display Flex. (Gee, I sure wish there were a NetConnection debugger for Flex 2/AS3/AMF3 ... hint, hint Adobe ;-) If you test the CFC where the error is occurring by calling it from a .CFM test page, likely you will see the full error message too. Sorry that I can't be more helpful. I can not say where you can get best support specifically on issues related to the Flex CF Wizard. In addition to posting here on FlexCoders, you might also try crossposting on these other two lists: http://www.houseoffusion.com/groups/Flex/ http://www.houseoffusion.com/groups/CF-Talk/ Of course, for ColdFusion specific issues you are likely to get the best response on lists like CF-Talkhttp://www.houseoffusion.com/groups/CF-Talk/rather than here on the Flex list. It would be nice if code generators worked perfectly. Sometimes I think they work best in helping experienced coders get their work done faster. If you do resolve this problem, please post back on this thread with an update on what the solution was. hth, g On 12/19/06, nall_daniel [EMAIL PROTECTED] wrote: No one has any thoughts or suggestions on this? I read another post in CFlex describing the same problem but no solution there either. Has anyone else experienced this? Dan
Re: [flexcoders] Re: SQL Insert operation fails silently
Dan, Your writing the following makes me wonder if there might not be a conditional block somewhere that results in the INSERT child statement not being executed. You wrote: The INSERT for the child table simply isn't even attempted. That would be sensible since executing INSERT child without the necessary foreign key would result in an error at run time :-) Could you check the code around whatever calls the insert for any IF/ELSE logic? Where the initial subject line was fails silently, maybe the error is that there is no logic to select back the newly inserted primary key for the PARENT, causing conditional logic to skip INSERT child since, again, executing INSERT child without the foreign key would result in a runtime error. So now I really, really am curious. So please do post back once you get it all working. g On 12/20/06, nall_daniel [EMAIL PROTECTED] wrote: Thanks Greg, Your advice is appreciated and helpful. I'm using MS SQL server, CF 7.02, and of course Flex 2. I have nothing in my CF logs, nothing in SQL logs (SQL errors show up in the browser anyway ie fault=server_fault(event)). I use the Flash Tracer extension for Firefox (very helpful BTW) and see nothing there either. The INSERT for the child table simply isn't even attempted. I've contacted Adobe and they have verified the problem and submitted it as a bug. In the meantime, I have done this with CF flash forms before so I'll try the same technique I used there. INSERT parent, Lock the dB, grab primary key, INSERT child, release lock. We'll see. If I can get it working I'll definately post a solution. Hopefully Adobe will beat me to the punch. Thanks again... Dan
[flexcoders] Re: SQL Insert operation fails silently
No one has any thoughts or suggestions on this? I read another post in CFlex describing the same problem but no solution there either. Has anyone else experienced this? Dan
Re: [flexcoders] Re: SQL Insert operation fails silently
Dan, What database are you using? e.g. MySQL, MS SQL 2000, Oracle, DB2, Access, etc? Database vendor's implementations vary on how they automatically generate surrogate primary keys. Oracle: sequencehttp://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/views.htm#sthref3092 MySQL: AUTO_INCREMENThttp://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html(see Forta re: capturing using ColdFusion http://www.forta.com/blog/index.cfm?mode=entryentry=A61BCDEA-3048-80A9-EFE79327EB7554DC ) Microsoft: @@identityhttp://msdn2.microsoft.com/en-us/library/ms187342.aspx (see Forta re: capturing using ColdFusionhttp://www.forta.com/blog/index.cfm?mode=eentry=221 ) etc. Basically, after the parent row is inserted, your logic must know the new primary key value before executing the insert into the child row. I just cross checked for questions like this over on the CF-Talk listhttp://www.houseoffusion.com/groups/CF-Talk/and found the following link detailing How do I grab the ID of the record I just created?: http://mysecretbase.com/get_the_last_id.cfm Regarding the code generated by the CF Wizard, I have not yet attempted using it for any parent/child insert operations like you are attempting. If the code generated by the wizard is deficient in capturing the necessary foreign key identifier before inserting the child, then you will need to edit the code that it generates for you. btw ... the error on the child inserts likely is not failing silently. It may be in your ColdFusion logs. Or if you start ColdFusion from the command prompthttp://www.forta.com/blog/index.cfm?mode=entryentry=A61BD25F-3048-80A9-EF9FCB7441C5C6C0you will see all errors on the console. It only appears to be silent because there is no handling for it to display Flex. (Gee, I sure wish there were a NetConnection debugger for Flex 2/AS3/AMF3 ... hint, hint Adobe ;-) If you test the CFC where the error is occurring by calling it from a .CFM test page, likely you will see the full error message too. Sorry that I can't be more helpful. I can not say where you can get best support specifically on issues related to the Flex CF Wizard. In addition to posting here on FlexCoders, you might also try crossposting on these other two lists: http://www.houseoffusion.com/groups/Flex/ http://www.houseoffusion.com/groups/CF-Talk/ Of course, for ColdFusion specific issues you are likely to get the best response on lists like CF-Talkhttp://www.houseoffusion.com/groups/CF-Talk/rather than here on the Flex list. It would be nice if code generators worked perfectly. Sometimes I think they work best in helping experienced coders get their work done faster. If you do resolve this problem, please post back on this thread with an update on what the solution was. hth, g On 12/19/06, nall_daniel [EMAIL PROTECTED] wrote: No one has any thoughts or suggestions on this? I read another post in CFlex describing the same problem but no solution there either. Has anyone else experienced this? Dan
RE: [flexcoders] Re: SQL Insert operation fails silently
Greg h's response is pretty much on target. You will just have to debug this problem, and dispense with the wizard if required. Tracy From: flexcoders@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of nall_daniel Sent: Tuesday, December 19, 2006 11:15 AM To: flexcoders@yahoogroups.com Subject: [flexcoders] Re: SQL Insert operation fails silently No one has any thoughts or suggestions on this? I read another post in CFlex describing the same problem but no solution there either. Has anyone else experienced this? Dan
[flexcoders] Re: SQL Insert operation fails silently
I have duplicated the problem with a very simple test db. I have two tables, PARENT CHILDREN. There is a one to many relationship From PARENT to CHILDREN. Using the CF wizard I generated a Master list of parents, and a Detail form. The Detail form allows users to enter parent information and has a datagrid subform where users can enter children of the parent. On new forms, the generated code fails to insert children into the db. On subsequent edits of the parent, children information is successfully updated into the db. Seems like because the PARENT primary key (foriegn key in CHILDREN) is auto-created by SQL, it is not available for CHILDREN insert operations and so fails. On edits, the key exists and updates are successful. however, this is a fairly common way of generating primary keys and should be handled by the wizard. Again anyone have any ideas? Dan --- In flexcoders@yahoogroups.com, nall_daniel [EMAIL PROTECTED] wrote: My sub-form in a CF Wizard generated form is not inserted into the database. I have a purchase form with a main form and a subform (datagrid). Main form is based on a table and subform on a related table. The form was generated by using the CF Wizard in Flex Builder. For a new purchase, the create (SQL INSERT) operation will insert the main form data into the database but does not insert the subform data. It does not fail or error out. It simply doesn't insert the data. If I then edit the same purchase, the update (SQL UPDATE) function works perfectly and all data is passed. The are no flash debug errors nor SQL errors. Any ideas? Dan
