Results 1 to 10 of 10

Thread: SQL Constraints lost during updates

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,836

    Default SQL Constraints lost during updates

    This has happened to me twice in a row:

    I am changing the structure on a file using Structure_Start and Structure_End


    It works perfect every time on my development computer, but when I run the process on the production server, the SQL Constraints get deleted from the file being modified and then I have to recreate them manually.

    Click image for larger version. 

Name:	lostconstaints.jpg 
Views:	139 
Size:	472.5 KB 
ID:	13106

    I use the same process at 100s of clients without issue (thankfully), but on this one production server (which happens to be mine) this has happened twice

    DF19.0
    SQL Express 2017 (on Production Computer)
    Runtime Version: 19.0.33
    Package Version: 19.0.1
    FMAC Version: 19.0.97
    Database driver(s): DATAFLEX, ODBC_DRV (6.2.0.42), MSSQLDRV (6.2.0.42)
    MSSQLDRV Client Version: ODBC Driver 13 for SQL Server (SQL Server 2016 client)


    Any ideas why this would be happening?

  2. #2
    Join Date
    Feb 2009
    Location
    Colombia
    Posts
    2,508

    Default Re: SQL Constraints lost during updates

    hi Mike

    Did you make sure to fix?

    Set pbApplyGlobalSQLFilters to True

    Edgar

  3. #3
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,836

    Default Re: SQL Constraints lost during updates

    Thanks Edgar

    I will look at that, although I am not sure why the same application has 2 different results on two servers.

  4. #4
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,836

    Default Re: SQL Constraints lost during updates

    This doesn't seem to be the case here.

    pbApplyGlobalSQLFilters is all about Data Dictionaries and I don't use those. The problem is something that happens between STRUCTURE_START and STRUCTURE_END

    Inside that block, something decides to delete all of the existing constraints in the SQL table. These have to do with fields that do not allow Nulls.

    In other words (as I understand it), if the SQL field does not allow Nulls, the SQL table MUST have a "Constraint" telling it what a blank value will be instead of Null. This is an SQL Constraint NOT to be confused with a Dataflex Constraint (totally different).

    The way that I am interpreting the problem is that something causes Dataflex to think that the fields inside of a table are all set to ALLOW NULL values when in fact they aren't

    OR

    maybe Dataflex starts by deleting all of the Constraints for the table that it is changing and then (in this 1 case) doesn't recreate them.

  5. #5
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,836

    Default Re: SQL Constraints lost during updates

    In the Help for Structure_Start, I found this single line:

    Note: If you change the structure of a table, any previous constraints on that table may no longer be valid; you should reset them after the restructure operation.
    I don't know if this is talking about the SQL Constraints (which is what is being lost), or the DF Constraints. And there is no further mention of it (at least as far as I can find)

  6. #6
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,836

    Default Re: SQL Constraints lost during updates

    To recap, this is what I do for all of my conversions... This one is the only one that has been a problem and thankfully it is on my own production server and not a clients.

    It has happened twice since deployment, and this is the most recent section of code that caused the deletion of the Constraints in the SQL Table.
    I was adding 2 new columns "BILLED" and "BILLINGSOURCE" (25 and 26) to an existing table that had 24 other columns. After the end, all of the 24 pre-existing constraints were gone from the SQL table but there were 2 new constraints for the new fields:

    Code:
          Close THREADS
          
          Open THREADS mode DF_EXCLUSIVE
          If (Found) Begin
              Send update_StatusPanel to (Status_Panel(Self)) 'Opening THREADS in Exclusive Mode'
          End
          Else Move 1 to iReady
          If (iReady=1) Begin
              Send info_box 'Your computer is unable to open the files in EXCLUSIVE mode at this time.  Conversion cannot continue.'
              Send ImmedAbort to (Client_Area(main(current_object)))
          End
          
          
          Send Initialize_StatusPanel to (Status_Panel(Self)) "Please Wait... Converting Data v29" "Converting Data Files" "" "-c0"
          Send Start_StatusPanel to (Status_Panel(Self))
    
    
              
          Send Update_StatusPanel to (Status_Panel(Self)) 'Updating file THREADS'
          Move THREADS.File_Number to iHdle
          Structure_Start iHdle sDriver
             
             
             Move (fNewField(iHdle,'BILLED','A',1,0)) to iRet
             Move (fNewField(iHdle,'BILLINGSEQUENCE','N',12,0)) to iRet
             
             
             Move 0 to iIndex
             Create_Index iHdle at iIndex
             Set_Attribute DF_INDEX_NUMBER_SEGMENTS of iHdle iIndex to 4
             Set_Attribute DF_INDEX_SEGMENT_FIELD of iHdle iIndex 1 to 25
             Set_Attribute DF_INDEX_SEGMENT_FIELD of iHdle iIndex 2 to 4
             Set_Attribute DF_INDEX_SEGMENT_FIELD of iHdle iIndex 3 to 26
             Set_Attribute DF_INDEX_SEGMENT_FIELD of iHdle iIndex 4 to 11
             
             
             Set_Attribute DF_FIELD_INDEX of iHdle 25 to 6
             Set_Attribute DF_FIELD_INDEX of iHdle 26 to 6
             
             
             
          Structure_End iHdle DF_STRUCTEND_OPT_NONE
    If you are wondering "sDriver" is parsed out of the INT file earlier in the process as "MSSQLDRV"

    And the "fNewField" looks like this:

    Code:
    Function fNewField Global Integer iHdle String sName String sType Integer iLength Integer iDecimals Returns Integer
            Move (Trim(sName)) to sName
            If (sName='') Function_Return 0
            Integer iNewField
            Move 0 to iNewField
            Create_Field iHdle at iNewField
            Set_Attribute DF_FIELD_NAME of iHdle iNewField to sName
            If (sType='A') Set_Attribute DF_FIELD_TYPE of iHdle iNewField to DF_ASCII
            If (sType='D') Set_Attribute DF_FIELD_TYPE of iHdle iNewField to DF_DATE
            If (sType='T') Set_Attribute DF_FIELD_TYPE of iHdle iNewField to DF_TEXT
            If (sType='N') Set_Attribute DF_FIELD_TYPE of iHdle iNewField to DF_BCD
            Set_Attribute DF_FIELD_LENGTH of iHdle iNewField to iLength
            If (sType='N') Set_Attribute DF_FIELD_PRECISION of iHdle iNewField to iDecimals
            Function_Return 1 
    End_Function
    The 2 new fields "BILLED" and "BILLINGSEQUENCE" got created fine and there were 2 new "Constraints" showing up in the SQL Database Table BUT the Constraints for the previous 24 columns where now missing from the SQL Table and I had to recreate each one of them separately in SQL like this:

    Code:
    USE [RM10]
    GO
    
    
    ALTER TABLE [dbo].[THREADS] ADD  DEFAULT ((0)) FOR [SEGMENTS]
    GO
    So, of course, this could be devastating if we were modifying several tables which could have hundreds of existing columns. Thankfully I've never seen this before (and have been using basically the same process for at least 10 years at 100s of customer sites. So what would cause this?
    Last edited by Mike Cooper; 23-Sep-2019 at 02:17 AM.

  7. #7
    Join Date
    Feb 2009
    Location
    Hengelo, Netherlands
    Posts
    10,869

    Default Re: SQL Constraints lost during updates

    Mike,

    These constraints have nothing to do with what we call constraints in DataFlex. Constraints in DataFlex are filters but we named them constraints about 30+ years ago. The SQL constraints in this are default values for columns.

    What most likely happens is that you use structure cache (.cch) files and that they do not contain the SQL constraints. This is why the Studio in 19.0 disables table structure caching during development. Table structure caching is only used to speed up table open operations and could be limited to end-user environments. Even there you can see if it makes sense to keep/generate them.
    Regards,
    Data Access Worldwide
    Vincent Oorsprong

  8. #8
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,836

    Default Re: SQL Constraints lost during updates

    Hi Vincent

    Thanks for your reply.

    I totally agree that the SQL Constraints (the ones that are getting dropped) have absolutely nothing to do with the Dataflex Constraints.

    Yes CCH files are being used. I never bother deleting them prior to a Structure_Start. Are you saying that they should be deleted, or are you saying Caching should be turned off prior to a structure change? What do you think the next logical step should be?

    Mike

  9. #9
    Join Date
    Feb 2009
    Location
    Hengelo, Netherlands
    Posts
    10,869

    Default Re: SQL Constraints lost during updates

    Mike,

    Turn off the use of cache files is advisable as they might give information about the tables that is not completely correct anymore.

    You can turn it off by:
    - Changing USE_CACHE in the <driver>.INT file
    - Setting the DF_DRIVER_USE_CACHE driver attribute in your application to false (do this before (re)opening the tables).
    Regards,
    Data Access Worldwide
    Vincent Oorsprong

  10. #10
    Join Date
    Feb 2009
    Location
    Castlegar, BC Canada
    Posts
    4,836

    Default Re: SQL Constraints lost during updates

    OK. Thanks Vincent. I will give that a go and see if the behavior is different.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •