I am getting an error talking to a Pervasive SQL database, but without much details about what could be wrong. Do you have any advice about how I could debug where the problem is
Pervasive.Data.SqlClient.Lna.k: [LNA][PSQL][SQL Engine]Syntax Error.
at Pervasive.Data.SqlClient.Lna.w.a(String , ad , l )
at Pervasive.Data.SqlClient.PsqlCommand.a(Boolean , CommandBehavior , Boolean )
at Pervasive.Data.SqlClient.PsqlCommand.ExecuteNonQuery()
Here is the code
using (PsqlCommand command = new PsqlCommand(sql, psqlConnection))
{
command.Parameters.Clear();
command.CommandText = sql;
command.Parameters.AddWithValue("@KidDate", companyKIDDate);
command.Parameters.AddWithValue("@KidTime", companyKIDTime);
command.Parameters.AddWithValue("@KidMult", companyKIDMult);
command.Parameters.AddWithValue("@KidComm", companyKIDComm);
command.Parameters.AddWithValue("@DateGmt", companyKIDDate);
command.Parameters.AddWithValue("@TimeGmt", companyKIDTime);
command.Parameters.AddWithValue("@Seconds", companyKIDTime);
result = command.ExecuteNonQuery();
}
The query sql in the code is (keeping the weird formatting for exactitude).
INSERT INTO "FMUPDATE"
("KID - date", "KID - time", "KID - user", "KID - mult", "KID - comm",
"Date GMT", "Time GMT",
"Flag",
"Pax Update",
"User ID",
"Count",
"Seconds",
"RockstarFlag")
VALUES(?, ?, 'RF01', ?, ?,
?, ?,
1,
1,
'RF01',
1,
?,
1);
And here is the PsqlTrace of the error
00000001 09:21:53.704 T1 I43089283 Command::ExecuteNonQuery ENTER
CommandText: INSERT INTO "FMUPDATE"
("KID - date", "KID - time", "KID - user", "KID - mult", "KID - comm",
"Date GMT", "Time GMT",
"Flag",
"Pax Update",
"User ID",
"Count",
"Seconds",
"RockstarFlag")
VALUES(?, ?, 'RF01', ?, ?,
?, ?,
1,
1,
'RF01',
1,
?,
1);
CommandType: Text
CommandTimeout: 30
UpdatedRowSource: Both
Par #1: @KidDate, Input, String, 0, System.Int32, 44755
Par #2: @KidTime, Input, String, 0, System.Int32, 982
Par #3: @KidMult, Input, String, 0, System.Int32, 128
Par #4: @KidComm, Input, String, 0, System.Int32, 1
Par #5: @DateGmt, Input, String, 0, System.Int32, 44755
Par #6: @TimeGmt, Input, String, 0, System.Int32, 982
Par #7: @Seconds, Input, String, 0, System.Int32, 982
Error or Warning Received from Pervasive Server
Number = 0
Message = Pervasive.Data.SqlClient.Lna.k: [LNA][PSQL][SQL Engine]Syntax Error.
Error Position = 37000
00000001 09:21:53.751 T1 I43089283 Command::ExecuteNonQuery EXIT
Return: 0
The MKDE.TRA file is a Btrieve / Transactional engine level trace. The error you are seeing is a Relational engine error. Some Relational engine errors are the same as Transactional / Btrieve errors but most are not. You can turn on tracing within the Pervasive.Data.SqlClient interface using code like below:
PsqlTrace.TraceFile = "C:\\source\\debug\\MyTrace.txt";
PsqlTrace.RecreateTrace = 1;
PsqlTrace.EnableTrace = 1;
This will generate a file that has a format like:
Assembly Name: Pervasive.Data.SqlClient, Version=4.1.0.0, Culture=neutral, PublicKeyToken=c84cd5c63851e072
Assembly File Version: 4.1.0.005024
00000001 19:48:31.703 T1 I30015890 Command::ExecuteNonQuery ENTER
CommandText: INSERT INTO Table1 ("KID -NAME", name, number) VALUES (?, ?, ?)
CommandType: Text
CommandTimeout: 30
UpdatedRowSource: Both
Par #1: @KIDname, Input, String, 0, System.String, "name"
Par #2: name, Input, String, 0, System.String, "name"
Par #3: number, Input, String, 0, System.Int32, 88
Error or Warning Received from Pervasive Server
Number = 0
Message = Pervasive.Data.SqlClient.Lna.w: [LNA][Pervasive][ODBC Engine Interface]Invalid column name: 'KID -NAME'.
Error Position = 0
00000001 19:48:31.759 T1 I30015890 Command::ExecuteNonQuery EXIT
Return: 0
Since this doesn't always happen, I would enable / disable the trace only around the calls that fail. Once you get the error, take a look at the trace and see if there's any more information. Posting here might get more eyes on it. If I had to guess, I would say that some value you are passing is invalid (00-00-0000 for a date, string for an integer, etc.).