>I have two tables, one FoxPro and one Oracle which are have the same, as is possible, data types and field names. When I try to insert the records from the FoxPro table into the Oracle table nothing happens. I receive no error messages but no records are inserted. I am using an ODBC connection. When I execute an append query in Access 97 using the same ODBC connection the records are promptly inserted. I have even copied the SQL statement used in the Access query to a program in VFP and it still doesn't work. I have made sure that both the Transactions and the SendUpdates properties are set to 1. All the fields in the remote view for the Oracle tables are also set to Updateable. If anyone could provide insight into possible remedies to this situation I would be extremely grateful.
>
>Michael Runatz
>
>List below is the program I am using.
>-----------------------------------------------------
>wait 'Connecting...' window nowait
>nch = sqlconnect('STP-FOXPRO','FOXPRO','FOXPRO')
>if nch = -2
> = messagebox('Connection is not successful.',16,'')
>endif
>
>nsp = SQLSETPROP(nCH, 'Transactions', 2) && Manual transactions
>if nsp <> 1
> = messagebox('Property has not been set.',16,'')
>endif
>
>sqlstatement = "INSERT INTO FOXPRO_TRANSFERS_TEST ( TRANS_TIME, TRANS_NO, TRANS_STATUS, ACTION_CODE, ACTION_RECNO, "
>sqlstatement = sqlstatement + "ACTION_RECTOTAL, ORD_NUM, ORD_ITEM, MULT_LEN, MULT_LEN_TOLPLUS, MULT_LEN_TOLMINUS, GRADE, "
>sqlstatement = sqlstatement + "SRM_OD_FINISHED, SRM_OD_ROLLED, SRM_OD_ROLLED_TOLPLUS, SRM_OD_ROLLED_TOLMINUS, "
>sqlstatement = sqlstatement + "SRM_WALL_FINISHED, SRM_WALL_ROLLED, SRM_WALL_ROLLED_TOLPLUS, SRM_WALL_ROLLED_TOLMINUS, "
>sqlstatement = sqlstatement + "SRM_SETUP_TYPE, MM_OD_FINISHED, MM_WALL_FINISHED, PM_OD_FINISHED, PM_WALL_FINISHED, "
>sqlstatement = sqlstatement + "BILLET_OD, "
>sqlstatement = sqlstatement + "BILLET_LENGTH_MIN, BILLET_LENGTH_MAX, BILLET_LENGTH_ALT, MM_BAR_OD, MM_FRONT_CROP_FLAG, "
>sqlstatement = sqlstatement + "MM_REAR_CROP_FLAG, CCS_FRONT_CROP, CCS_REAR_CROP, CUSTOMER, END_USE, SCAT_NUM, "
>sqlstatement = sqlstatement + "BUNDLING_INSTRUCTION, ORD_COMMENT, LUP_NUM, SRM_LENGTH, MM_LENGTH, PM_LENGTH, "
>sqlstatement = sqlstatement + "BILLET_LENGTH, BILLET_WEIGHT, NUMBER_BILLETS, MULTS_PER_BILLET, CAST_FORGE_FLAG, "
>sqlstatement = sqlstatement + "HEAT_NUM, END_FINISH, PROCESS, LUP_LOCATION, LUP_COMMENT ) "
>sqlstatement = sqlstatement + "SELECT vfptransfers.trans_time, vfptransfers.trans_no, vfptransfers.trans_status, "
>sqlstatement = sqlstatement + "vfptransfers.action_code, vfptransfers.action_recno, "
>sqlstatement = sqlstatement + "vfptransfers.action_rectotal, vfptransfers.ord_num, vfptransfers.ord_item, vfptransfers.mult_len, "
>sqlstatement = sqlstatement + "vfptransfers.mult_len_tolplus, transfers.mult_len_tolminus, transfers.grade, "
>sqlstatement = sqlstatement + "transfers.srm_od_finished, transfers.srm_od_rolled, transfers.srm_od_rolled_tolplus, "
>sqlstatement = sqlstatement + "transfers.srm_od_rolled_tolminus, transfers.srm_wall_finished, transfers.srm_wall_rolled, "
>sqlstatement = sqlstatement + "transfers.srm_wall_rolled_tolplus, transfers.srm_wall_rolled_tolminus, "
>sqlstatement = sqlstatement + "vfptransfers.srm_setup_type, vfptransfers.mm_od_finished, vfptransfers.mm_wall_finished, "
>sqlstatement = sqlstatement + "vfptransfers.pm_od_finished, vfptransfers.pm_wall_finished, vfptransfers.billet_od, "
>sqlstatement = sqlstatement + "vfptransfers.billet_length_min, vfptransfers.billet_length_max, vfptransfers.billet_length_alt, "
>sqlstatement = sqlstatement + "vfptransfers.mm_bar_od, vfptransfers.mm_front_crop_flag, vfptransfers.mm_rear_crop_flag, "
>sqlstatement = sqlstatement + "vfptransfers.ccs_front_crop, vfptransfers.ccs_rear_crop, vfptransfers.customer, vfptransfers.end_use, "
>sqlstatement = sqlstatement + "vfptransfers.scat_num, vfptransfers.bundling_instruction, vfptransfers.ord_comment, "
>sqlstatement = sqlstatement + "vfptransfers.lup_num, vfptransfers.srm_length, vfptransfers.mm_length, vfptransfers.pm_length, "
>sqlstatement = sqlstatement + "vfptransfers.billet_length, vfptransfers.billet_weight, vfptransfers.number_billets, "
>sqlstatement = sqlstatement + "vfptransfers.mults_per_billet, vfptransfers.cast_forge_flag, vfptransfers.heat_num, "
>sqlstatement = sqlstatement + "vfptransfers.end_finish, vfptransfers.process, vfptransfers.lup_location, vfptransfers.lup_comment "
>sqlstatement = sqlstatement + "FROM vfptransfers;"
>
>wait 'Adding...' window nowait
>= sqlexec(nch,sqlstatement)
>
>wait 'Saving...' window nowait
>nc = SQLCOMMIT(nCH) && Commit the changes
>if nc <> 1
> = messagebox('Committment is not successful.',16,'')
>endif
>
>wait 'Done...' window
Let me say few remarks on you code. Not what you have asked, but important
You did not used Oracle schemas name in your code. So all your users must eneter into Oracle with same Oracle name. This is bad.
You have to write insert into oracleschemaname.yourtable ( fieldsnames) values ( values)
Same for select , update.
Thus way users with different Oracle name and password could enter to same database.
You did not use “?” mark ( look my paper at www.vfug.org newsletters in November or near)
This will help you to avoid many problems
You have to write insert into oracleschemaname.yourtable ( fieldsnames) values ( ?vfpvariables)
How coud you tell , your insert was successfull , if you wrote = sqlexec(nch,sqlstatement)
You have to write
If sqlexec(nch,sqlstatement)>
Else
Calling your procedure for errors
EndifI am pretty sure, your insert was not right, if after commit you have nothing
If you used asinchronous mode , you have to call select to know about possitive results untill select will return 1 or something positive.
Packege (batch) mode also has influence on the right code.