MS-SQL loses records

questions
, ,
1

Hello All,

I have a major issue at a customer live site. We run into records not being saved from Clarion 11 to MS-SQL. Sometimes, aparently, the saved record shows up in the detail-list, but gets removed soon after.

Finally I found the SQL Profiler to log what’s being recieved by MSSQL.

<Event id="10" name="RPC:Completed">
  <Column id="1" name="TextData">exec sp_execute 143,19037,'2022-02-11 00:00:00','2022-02-11 00:00:00',1355,0,25,'1. Producten geleverd',30216,27,3,302,'Safiland','Peper Rood','Normaal','','I                   ','MA',9001,'3kg                 ','1         ',1.00,3.000,'1',0.00,10.00,'pepro3',13.50000000,1.00,0.00000000,13.50000000,13.50000000,13.50000000,'',7096099,'','',0,NULL,'','',0,'','1 ',0,0.00,182321,0,'',0,NULL,0.00000000,0.00,'',NULL,'','',0,0.00,0,0.0000</Column>
  <Column id="9" name="ClientProcessID">42992</Column>
  <Column id="2" name="BinaryData">000000003E0000001400730070005F0065007800650063007500740065001400000003000600380069006E0074008F0000001400000003000600380069006E0074005D4A000032000000870018002A006400610074006500740069006D0065003200280033002900E60702000B000000000000000000000032000000870018002A006400610074006500740069006D0065003200280033002900E60702000B000000000000000000000035000000830018006A0064006500630069006D0061006C00280037002C00300029000700014B0500000000000000000000000000003700000083001A006A0064006500630069006D0061006C002800310031002C00300029000B0001000000000000000000000000000000001400000003000600380069006E007400190000003900000081001600A7207600610072006300680061007200280033003000290015000000312E2050726F64756374656E2067656C657665726435000000830018006A0064006500630069006D0061006C00280039002C00300029000900010876000000000000000000000000000035000000830018006A0064006500630069006D0061006C00280039002C00300029000900011B00000000000000000000000000000035000000830018006A0064006500630069006D0061006C00280035002C00300
029000500010300000000000000000000000000000035000000830018006A0064006500630069006D0061006C00280035002C00300029000500012E0100000000000000000000000000002C00000081001600A7207600610072006300680061007200280032003500290008000000536166696C616E642E00000081001600A720760061007200630068006100720028003500300029000A000000506570657220526F6F642B00000081001600A72076006100720063006800610072002800320030002900070000004E6F726D61616C2400000081001600A72076006100720063006800610072002800320035002900000000003800000081001600A720760061007200630068006100720028003200300029001400000049202020202020202020202020202020202020202600000081001600A72076006100720063006800610072002800320030002900020000004D4135000000830018006A0064006500630069006D0061006C00280039002C0030002900090001292300000000000000000000000000003800000081001600A7207600610072006300680061007200280032003000290014000000336B6720202020202020202020202020202020202E00000081001600A720760061007200630068006100720028003100300029000A0000003120202020202020202035000000830018006A0064006500630069006D0
061006C00280037002C00320029000702016400000000000000000000000000000035000000830018006A0064006500630069006D0061006C00280039002C0033002900090301B80B00000000000000000000000000002300000081001400A7207600610072006300680061007200280031002900010000003135000000830018006A0064006500630069006D0061006C00280037002C0032002900070201000000000000000000000000000000003700000083001A006A0064006500630069006D0061006C002800310031002C00320029000B0201E80300000000000000000000000000002A00000081001600A7207600610072006300680061007200280031003200290006000000706570726F333700000083001A006A0064006500630069006D0061006C002800310035002C00380029000F0801805D775000000000000000000000000035000000830018006A0064006500630069006D0061006C00280037002C0032002900070201640000000000000000000000000000003700000083001A006A0064006500630069006D0061006C002800310035002C00380029000F0801000000000000000000000000000000003700000083001A006A0064006500630069006D0061006C002800310035002C00380029000F0801805D77500000000000000000000000003700000083001A006A0064006500630069006D0061006
C002800310039002C0038002900130801805D77500000000000000000000000003700000083001A006A0064006500630069006D0061006C002800310039002C0038002900130801805D77500000000000000000000000002200000081001400A7207600610072006300680061007200280031002900000000001400000003000600380069006E00740023476C002200000081001400A7207600610072006300680061007200280036002900000000002400000081001600A72076006100720063006800610072002800320030002900000000001900000011000E003000740069006E00790069006E0074000022000000870018002A406400610074006500740069006D00650032002800330029002400000081001600A72076006100720063006800610072002800340030002900000000002600000081001800A720760061007200630068006100720028003100300030002900000000001400000003000600380069006E007400000000002200000081001400A7207600610072006300680061007200280038002900000000002600000081001600A720760061007200630068006100720028003200300029000200000031201400000003000600380069006E007400000000003700000083001A006A0064006500630069006D0061006C002800310033002C00320029000D0201000000000000000000000000000000001
400000003000600380069006E00740031C802001400000003000600380069006E007400000000002400000081001600A72076006100720063006800610072002800310038002900000000001900000011000E003000740069006E00790069006E0074000022000000870018002A406400610074006500740069006D00650032002800330029003700000083001A006A0064006500630069006D0061006C002800310035002C00380029000F0801000000000000000000000000000000003700000083001A006A0064006500630069006D0061006C002800310031002C00320029000B0201000000000000000000000000000000002400000081001600A720760061007200630068006100720028003100300029000000000022000000870018002A406400610074006500740069006D00650032002800330029002600000081001800A720760061007200630068006100720028003100390039002900000000002600000081001800A720760061007200630068006100720028003100390039002900000000001900000011000E003000740069006E00790069006E0074000035000000830018006A0064006500630069006D0061006C00280037002C0032002900070201000000000000000000000000000000001400000003000600380069006E007400000000003700000083001A006A0064006500630069006D0061006C0
02800310031002C00340029000B0401000000000000000000000000000000001400000003000600380469006E007400FCFFFFFF</Column>
  <Column id="10" name="ApplicationName">InVerVo</Column>
  <Column id="11" name="LoginName">invervo</Column>
  <Column id="12" name="SPID">81</Column>
  <Column id="13" name="Duration">2965</Column>
  <Column id="14" name="StartTime">2022-02-11T10:35:03.49+01:00</Column>
  <Column id="15" name="EndTime">2022-02-11T10:35:03.493+01:00</Column>
  <Column id="16" name="Reads">3</Column>
  <Column id="17" name="Writes">0</Column>
  <Column id="18" name="CPU">16</Column>
  <Column id="35" name="DatabaseName">Invervo</Column>
</Event>

What I noticed is the fact that the Writes are zero. When I look at the other records saved, the Writes are from 1 to 5 or so.

I am not the programmer myself (I am the product owner, who has a bit of programming experience, so I can read the code quite a bit), but my programmers have been stuck with this issue for a week nog (so I spend this weekend testing and extracting from milions of log lines)

The code involved looks straight forward: It creates a new “NrInLine”, to register the next number to be used for this order. The SalesRecords table also contains an auto number field.
The last 2 rows of code is where I feel things are failing

   LOC:NrInLine = 0
   
   Clear(SalesRecords2)
   SLR2:Ordernumber = SAL:Ordernumber
   SLR2:NrInLine = 99999999999999
   Set(SLR2:KeyOrderLine,SLR2:KeyOrderLine)
   Previous(SalesRecords2)                     //Due to translation error this said SalesLines before.
   LOC:NrInLine = SLR2:NrInLine + 1
   Clear(SLR2:Record)
   If LOC:NrInLine <= 0
      LOC:NrInLine = 1
   end
   SLR1:NrInLine = LOC:NrInLine
   SLR1:InvoiceNumber = SAL:InvoiceNumber

   IF Access:SalesRecords1.TryInsert() <> Level:Benign
      Message(ErrorCode() & ' ' & Error() & '|' & FILEERRORCODE() & ':' & CLIP(FILEERROR()) )

There is a TryInsert, and the result is obviously benign, otherwise the user should have had an error warning (we also tested with a lot of trace logging, and it shows the same path of execution.

But whilest Clarion tells us, all is well, the SQL database has found no reason to save it.

I would welcome any bright idea on the matter.

(Code above is translated by me. Actual source has Dutch fields and variables)

2

Hi Marque

at first glance I see lots of problems here - perhaps I am misunderstanding the intent but at least check these things.

first check the format of SLR2:NrInLine. If it is LONG then that will overflow. ie. 99999999999999 is too big.

maybe you are better using clear(SLR2:NrInLine,1) or starting at the lowest row of the next order number:

SLR2:Ordernumber = SAL:Ordernumber + 1
SLR2:NrInLine = 0 ! or perhaps clear(SLR2:Ordernumber, -1)
previous(…

next do error checking after previous()

Previous(SalesLines2)
if errorcode() or SLR2:Ordernumber <> SAL:Ordernumber
  LOC:NrInLine = 1
else
  LOC:NrInLine = SLR2:NrInLine + 1
end

next concern is that you are attempting to set up the key for SalesLines2 file but then adding to a different file SalesRecords1. Is that intended? More information required.

I am not sure if this is related to your problem but definitely worth cleaning up the code as a first step.

cheers

Geoff R

3

In addition to what Geoff said, Losing a record from SQL is indicative of a Rollback of a transaction occurring. Now depending on your code that may be an error that occurs After a successful insert but is part of the same transaction.

You don’t mention if LOGOUT/COMMIT is used at all for an explicit transaction, but if so, any error in between the 2 Might cause a rollback on the server.

4

So this code is very wrong.
a) TryInsert suppresses a Message command anyway, so this is extra code for no purpose. At the very least it can be replaced with
Access:SalesRecords1.Insert()
This is coded so that if an error occurs it will be displayed.

b) The values in Error(), ErrorCode(), FileError() and FileErrorCode() are not (necessarily) set after a call to TryInsert. So inspecting them after a call to TryInsert will likely return 0 values, or worse, the result of some later error. If you want to access the errors that happened use
GlobalErrors.GetError(ErrClarion)
GlobalErrors.GetError(ErrFile) ,
GlobalErrors.GetErrorCode(ErrClarion)
GlobalErrors.GetErrorCode(ErrFile)

Next, you have an error in this code;

You go to great lengths to set up SalesRecords2, but the Previous is on SalesLines2.
So that seems wrong.

Like Geoff, I recommend replacing the line

with
Clear(SLR2:NrInLine,1)

5

Thanks for the advice. I have forwarded it to my programmers. However there are a few remarks/questions:

  • Indeed, I made an error whilst translating the code from Dutch to English. I altered the code above to reflect what it ought to be. SalesLines=SalesRecords.
  • The first part is to find the next NrInLine. The unique combination is OrderNumber-NrInLine. These are the different salesrecords, within the current sale. So they have to find the next number. Using the next ordernumber to go backward from there, is not an option, as often the new sale is the latest ordernumber too. So there is no next record to go back from. In SQL I would ask “Select max(OrderInLine)+1 NewValue from SalesRecords where ordernummer=@ordernr”, to achieve what my programmers have build here.
  • My programmer claims that Logout/Commit is not usable. This would lock the whole table for everybody. With dozens of salesreps typing in orders at the same time, it should not be blocking for other orders in any way. I found an Hold also exists, which would not block other orders, only hold them whilst the other one is saved, but that is not available for inserts. Nor would it actually be needed for inserts in SQL, as it is an transactional database, thus holds the statements to fill the tables sequentially anyway. This is why they did not implement an Logout/Commit. Me, on the other hand, would think that this would be the key to solve the issue, so who is right?

Once again, any advice is much appreciated.

6

Not very familiar with MS SQL, but that statement does not sound right. For any transactional database the point of the transaction is to save changes when all related tables have been updated to avoid referential integrity issues. Cannot think of any transactional database that locks a table when starting a transaction (at least when it comes to Postgres, MariaDB/MySQL, MS SQL cannot be any different) . Transactions have all-or-nothing effect, but rather create a local user (connection) copy of date than lock the entire database. That simply would make no sense.

7

I assume you have a Key on these 2 fields. SLR2:KeyOrderLine.
In that case the correct way to prime the record is

 Clear(SalesRecords2)
 SLR2:Ordernumber = SAL:Ordernumber
 Clear(SLR2:NrInLine,1)
 Set(SLR2:KeyOrderLine,SLR2:KeyOrderLine)
 Previous(SalesLines2)
 if errorcode() or SLR2:Ordernumber <> SAL:Ordernumber
   LOC:NrInLine = 1
 else
   LOC:NrInLine = SLR2:NrInLine + 1
 end
8

The suggestion was not to use Logout/Commit, but to see if that was already in play and would cause the original effect you are seeing.

I fear you misunderstand what Set / Previous is doing. The code above is correct.

9

Well Clarion mentions on https://clarion.help/doku.php?id=logout_begin_transaction_.htm that it is locking FILES. As I would read this, with TPS this means the whole table, right? So I would expect the same goes for other databases when using this command.
(but once again, I am not the programmer)

10

I was not aware of the function Clear(fieldname,1) being the max available value for this field. I assume my programmers weren’t either, as I did not see this in the source. I will put this to them in the next general programmers meeting. Thanks for this.

11

My programmers had a big pow wow yesterday, all working together on this issue. What they came up with, is the fact that the DCT has the ID-field as autonumbering. However, this is an autonumbering by Clarion and not by MS SQL. But as SQL does check the uniqueness of the field, this should cause the issue.

Even though I fully agree that when using a transactional database, as you would not want to have the program assigning these unique keys, I dismissed this as the cause of the problem. Mainly as I already checked with the example above that the original key was still not in use. However, as we could not come up with a better solution, we did tackle this issue. And as it stands, it apears that my programmers were right. This morning the solution was installed on the customers live server, and we haven’t come accross a lost orderline since. So fingeres crossed that this was indeed the holy grail…

12

Hi Marque

I am not clear what you ended up with. Generally people using SQL databases prefer NOT to have autoinc on the client side (ie. done by Clarion) but rather leave that to the server.

also did you correct all the things mentioned in the code? Maybe show the code as it is now. I suggest you cut and paste it (even if it is in Dutch) so it is accurate and also show the file definitions.

cheers

Geoff R

13

Yes, TPS requires a table lock, mostly to update indexes I would think.
NO that does NOT happen with an SQL database. It starts a transaction on the backend. That will bnot lock tables. Not under any SQL DB I’ve used, including MSSQL

14

Hello Geoff,

Correct. The thing is that they rely on the DCT to set the database tables, fields, keys and indexes. As a result, the SQL did recieve from the updater the command to create a non duplicate field, but Clarion kept the Auto Numbering for itself. It’s only now when the software is in production we noticed this. We have solved it for the offending table and will look at all the other tables at a later date to do the same.

The source I quotes is not changed yet. The changes to the autonum was in other parts and only today in the general programmers meeting I have put the CLEAR(field,1) option and the other errorcode to them. This as more code needs to be checked, so I rather they check it all in one go.

What I forgot to add, is that the original code, had also tracing using an if then else, stating if the TryInsert <> IsBenign failed a trace was written, and another traceline if it succeeded. So am I right in assuming that TryInsert will always return a ‘IsBenign’, so I always need to have checked the “GlobalErrors.GetError(code)” fields to determine if the TryInsert was successful or not?

15

Thanks for the info Seanh

16

No TryInsert() returns Level:Benign only on success otherwise something else. So it lets you know there’s an error, but you have to handle error processing yourself.

.Insert() returns the same values but also does error handling and show a message for you.

17

The pedant in me wants to point out that SQLite does lock the entire database for a transaction (indeed for every write) but that’s a SQL for a different use case.

18

The pedant in me wants to point out that Sean said

and perhaps Sean hasn’t used SQLite.

perhaps that would better have been stated as “with a client server database”

but enough of the pedantry :slightly_smiling_face:

19

vitesse
perhaps that would better have been stated as “with a client server database”.
but enough of the pedantry

Hah? You think YOU get the last word on useless pedantry?

Not so fast, me hearty!

Highly unlikely for an insert, but MSSQL can definitely lock a table for something like an update applied to a large number of rows:

and

2 Likes
20

OK you win :grinning: