SQL Insert

[jiaseby]

I have two Voiceguide servers running the same script. One of them can insert into our visual foxpro table and the other one can't. I'm attaching the engine log. The insert is just after line 180712.429. The insert is INSERT INTO logfile (check,linenumber,logdate,logtime,linesinuse,type,language) VALUES (1221,2,"032326","1807",1,"C",1). 

Thank you in advance for any help you can provide. The interesting thing is that we can query, but not insert. 

0323_1806_vgEngine.txt

[SupportTeam]

vgEngine trace shows:
 

180712.428  11   2   2    1 state [SQLLogCheck] DB Query | 
180712.428  11   2   2    1       rv    replace start 
------------------------------
INSERT INTO logfile (check,linenumber,logdate,logtime,linesinuse,type,language) VALUES ($RV[GetCheck],$RV_PORTNUMBER,"$RV_MM$RV_DD$RV_YY","$RV_HH$RV_NN",$RV_LINESINUSE,"C",$RV[EnglishSpanish])

------------------------------
180712.429  11   2   2    1       rv    replace RV_LINESINUSE begin
180712.429  11   2   2    1       1:Null 2:Connected 3:Null 4:Null 5:Null 6:Null 7:Null 8:Null 
180712.429  11   2   2    1       rv    replace RV_LINESINUSE iLinesInConnectedState=1
180712.430  11   2   2    1       rv    replace end   
------------------------------
INSERT INTO logfile (check,linenumber,logdate,logtime,linesinuse,type,language) VALUES (1221,2,"032326","1807",1,"C",1)

------------------------------
180712.430  11   2   2    1       dbstrDatabaseName=[] strDbConnectString=[Provider=vfpoledb;Data Source=C:\ivrdata\;Collating Sequence=general;]
180712.430  11   2   2    1       db    did not find ado.net data provider : []
180712.430  11   2   2    1       moh not set
180712.430  11   2   2    1       db    using ado.net OleDb : OleDbConnection/OleDbCommand/etc
180712.430  11   2   2    1       dir operations failed on []

180712.430  11   2   2    1       dboledb RunQuery_OleDb [SQLLogCheck] , Provider=vfpoledb;Data Source=C:\ivrdata\;Collating Sequence=general;, INSERT INTO logfile (check,linenumber,logdate,logtime,linesinuse,type,language) VALUES (1221,2,"032326","1807",1,"C",1)

180712.430  11   2   2    1       dboledb  thread init (threadpool)
180712.430  11   2   2    1       dboledb  thread started (threadpool)
180712.430  11   2   2    1       db    iRunWait=1
180712.430   7   2   2    1       dboledb ConnectAndRun_OleDb begin. Create connection: [Provider=vfpoledb;Data Source=C:\ivrdata\;Collating Sequence=general;]
180712.430  11   2   2    1 t     timer set   30 sec : EV_TIMEOUT_HANGUP
180712.430  11   2   2    1       RunModule finish
180712.430   7   2   2    1       dboledb connectionOleDbConn open call [Provider=vfpoledb;Data Source=C:\ivrdata\;Collating Sequence=general;]
180712.432   7   2   2    1       dboledb connectionOleDbConn open returned.
180712.432   7   2   2    1       dboledb [SQLLogCheck] INSERT INTO logfile (check,linenumber,logdate,logtime,linesinuse,type,language) VALUES (1221,2,"032326","1807",1,"C",1)

180712.433   7   2   2    1 ERROR v7.6.5 - 7.6.6977.23886 (2019-02-07 13:16:12.65) ConnectAndRun_OleDb : One or more errors occurred during processing of command.

   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at (Object )
   at �.�.�()
180712.433   7   2   2    1       dbcompleted. [SQLLogCheck]  SqlQueryType=NONQUERY, RowsCount=0, sRv=
180712.433   7   2   2    1       rv    add   SQLLogCheck_error|One or more errors occurred during processing of command.

 

 

The error in your log indicates a failure during the execution of a nonquery (INSERT) command through the Visual FoxPro (VFP) OLEDB provider.

The most likely causes are a reserved word conflict or a data type mismatch in the SQL syntax, or that that Visual FoxPro fiel is currently open by another user.

 

Potential Causes

• Reserved Word Conflict (CHECK and TYPE):
  • The column name check is a highly restricted reserved word in VFP. It is used for validation rules (e.g., CHECK clauses).
  • The column name type is also a reserved word/function in VFP.
  • Fix: Enclose these column names in square brackets: INSERT INTO logfile ([check], linenumber, logdate, logtime, linesinuse, [type], language) ....
• Data Type Mismatch (Date/Time Strings):
  • VFP expects specific formats for date and time fields. You are passing "032326" (likely MMDDYY) and "1807" (likely HHMM) as double-quoted strings.
  • If logdate is a Date or DateTime field, VFP will reject these string formats. VFP's literal date format is {^YYYY-MM-DD}.
  • Fix: If the fields are character types (C), ensure the field lengths can accommodate the string. If they are Date/DateTime types, use the proper VFP date literals or parameters.
• String Delimiter Issues:
  • The query uses double quotes (") for string values like "032326". While sometimes accepted, VFP standard SQL often prefers single quotes (').
  • Fix: Try replacing double quotes with single quotes: VALUES (1221, 2, '032326', '1807', 1, 'C', 1).
• Table/File Permissions:
  • Since the error occurs at ExecuteNonQuery, the OLEDB provider might have successfully opened the connection to C:\ivrdata\ but lacks write permissions to logfile.dbf or its associated index (.cdx) or memo (.fpt) files.

Recommended Troubleshooting Steps

1. Wrap column names: Rename or bracket [check] and [type].
2. Standardise quotes: Use single quotes for all string literals.
3. Check Data Types: Confirm if logdate is a Character or Date field. If it's a Date field, your string "032326" will definitely fail.
4. Verify File Locks: Ensure another process (like a legacy Visual FoxPro app) doesn't have the table opened exclusively. 

 

Quote

One of them can insert into our visual foxpro table and the other one can't 

Can you post the vgEngine trace from the other systems that does the INSERT successfully? We can then compare the two calls.

[jiaseby]

Attached is the vgEngine trace from the system that is updating the table. This is a much bigger file because it is running all the time. If you do a search for Logfile, which is the table name you will find the first time it happened was 000445.422 line. Thanks for looking into this. 

0324_0000_vgEngine.txt

[jiaseby]

Attached is the vgEngine trace from the system that is updating the table. This is a much bigger file because it is running all the time. If you do a search for Logfile, which is the table name you will find the first time it happened was 000445.422 line. Thanks for looking into this. 

 

Adding to this. I found the issue. I'm not sure why it is working on our live system, but I found this old post that I sent to your company on 6/20/2019 saying I found this exact issue. See the post. below. 

Posted 06/20/2019 03:40 PM

[SupportTeam]

Trace from other system shows exactly the same SQL running fine on this other system:
 

000445.422  11   6   6 17477 state [SQLLogCheck] DB Query | 
000445.422  11   6   6 17477       rv    replace start 
------------------------------
INSERT INTO logfile (check,linenumber,logdate,logtime,linesinuse,type,language) VALUES ($RV[GetCheck],$RV_PORTNUMBER,"$RV_MM$RV_DD$RV_YY","$RV_HH$RV_NN",$RV_LINESINUSE,"C",$RV[EnglishSpanish])

------------------------------
000445.423  11   6   6 17477       rv    replace RV_LINESINUSE begin
000445.423  11   6   6 17477       1:Null 2:Null 3:Null 4:Null 5:Null 6:Connected 7:Null 8:Null 
000445.423  11   6   6 17477       rv    replace RV_LINESINUSE iLinesInConnectedState=1
000445.423  11   6   6 17477       rv    replace end   
------------------------------
INSERT INTO logfile (check,linenumber,logdate,logtime,linesinuse,type,language) VALUES (4701443635,6,"032426","0004",1,"C",1)

------------------------------
000445.424  11   6   6 17477       dbstrDatabaseName=[] strDbConnectString=[Provider=vfpoledb;Data Source=C:\ivrdata\;Collating Sequence=general;]
000445.424  11   6   6 17477       db    did not find ado.net data provider : []
000445.424  11   6   6 17477       moh not set
000445.424  11   6   6 17477       db    using ado.net OleDb : OleDbConnection/OleDbCommand/etc
000445.424  11   6   6 17477       dir operations failed on []
000445.424  11   6   6 17477       dboledb RunQuery_OleDb [SQLLogCheck] , Provider=vfpoledb;Data Source=C:\ivrdata\;Collating Sequence=general;, INSERT INTO logfile (check,linenumber,logdate,logtime,linesinuse,type,language) VALUES (4701443635,6,"032426","0004",1,"C",1)

000445.424  11   6   6 17477       dboledb  thread init (threadpool)
000445.424  11   6   6 17477       dboledb  thread started (threadpool)
000445.424  11   6   6 17477       db    iRunWait=1
000445.424  51   6   6 17477       dboledb ConnectAndRun_OleDb begin. Create connection: [Provider=vfpoledb;Data Source=C:\ivrdata\;Collating Sequence=general;]
000445.425  11   6   6 17477 t     timer set   30 sec : EV_TIMEOUT_HANGUP
000445.425  11   6   6 17477       RunModule finish
000445.425  51   6   6 17477       dboledb connectionOleDbConn open call [Provider=vfpoledb;Data Source=C:\ivrdata\;Collating Sequence=general;]
000445.431  51   6   6 17477       dboledb connectionOleDbConn open returned.
000445.431  51   6   6 17477       dboledb [SQLLogCheck] INSERT INTO logfile (check,linenumber,logdate,logtime,linesinuse,type,language) VALUES (4701443635,6,"032426","0004",1,"C",1)

000445.432  51   6   6 17477       dbcompleted. [SQLLogCheck]  SqlQueryType=NONQUERY, RowsCount=1, sRv=[SQLLogCheck_RowCount]{1}
000445.432  51   6   6 17477       rv    add   SQLLogCheck_error|noerror
000445.432  51   6   6 17477       rvns  add   SQLLogCheck_RowCount|1
000445.433  51   6   6 17477       dbcompleted. iRunWait=1, WavPlayWasStarted=0, WavPlayHasNowFinished=0
000445.433  51   6   6 17477       dbcompleted. iRowsAffected=1
000445.433  51   6   6 17477       find_next_vgm module title is=[SQLCheck] (trigger_used={SUCCESS})

 

is it possible that the Visual FoxPro database on the system that has issues has a slightly different column definition? have you tried copying the Visual FoxPro database from the working system to the problem system and checking operation?

Ultimately there is something different between the two systems and you just need to compare every component and setup/config between the two systems.

 

In that 2019 post apparently the resolution was:

Quote

Apparently when you are doing a select you don't have put the file name in, only the folder name.  Once I added the logfile.dbf in the string after the folder name it worked.  

so that might be worth trying again.