Dates not storing properly in PostgreSQL - Help please!

Databases
21

Found the problem. My dumb thing…

Result = oLicense.GenerateSiteLicense(SID, d)    <<<<====  this function does some stuff AND saves dates...

If Result = 0 Then 
  //---- Log Success here ----//
  Dim d1 As Date
  LicTrans.ExpDateNew = d1
  LicTrans.ExpDateOld = d1
  LicTrans.SiteID = SID
  LicTrans.StatusCode = Result
  LicTrans.StatusDesc =  CurrentMethodName +  " License Creator - New license generated for Site ID " + SID.ToString
  Result = LicTrans.Update_SID(SID)
  
  SiteList.UpdateNow = False
  Result = SiteList.Update_SID(SID)   <<<<===== This is what causes the issue.

My thought was that this command already had in it the correct dates and other variables. However, since they were created in a different instance of SiteList those variable had not been set.

To correct, I added this one line:

 SiteList.UpdateNow = False
 Result = SiteList.getSiteID(SID)  <<<<===== This is what fixes the issue.  Just load the current db table values....

 Result = SiteList.Update_SID(SID)

Sorry all of the circle chasing! I appreciate all of your responses!

Tim

2 Likes
22

I know, but this is really very old code and not interactive, it parses data loggers files and it’s very difficult to insert sql there as files are parsed as you can see in date, not possible even someone has access to files repository to make a frame that injects sql.

Anyway, thanks by your sugestion. I cared about that, but in this case it can be leaved that way. There are only numbers and if the frame don’t parses right, is discarded. No place for format errors. Only numbers,

23

If you are processing a lot of log lines or files in one go you should look into prepared statements. They use the same syntax as ExecuteSQL, however they are only “prepared” once and then executed repeatedly with each dataset. This provides a significant performance boost to the time take to update the database.

SQL engines spend about half their time working out how to best execute your command. Prepared statements make that decision once and then use the results as many times as you like. The more times they are used the more you gain.

This goes for update, insert and select statements.

To give a feel for this we updated an application that processes 1,000,000 records and gained over a 60% improvement using SQLite.

24

Just one thing:
You are doing this:

Don’t try to convert variants into sql-friendly strings. Actually variants are exactly what you want. With them you’d just say:
try
var SQL as string= “Insert into myTable (field1,field2,field3) values ($1,$2,$3)”
mydbConnection.ExecuteSQL(SQL,myVariant1,myVariant2,myVariant3)
catch …

This is safe and you won’t have to deal with any dateformat issues. Your ValueString - method is frail as any omitted vartypes in your routine (you missed case variant.TypeDateTime) will break your code.