Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Correcting Design Mistake - Primary Key Type

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Correcting Design Mistake - Primary Key Type

    I made a stupid mistake when I initially setup my SQL database a few years ago.
    The mistake was that I have used two different data types in two tables to refer to the same field. For example:

    Table 1:
    Machines with primary key machineid, data type numeric(12,0) <-- This is good

    Table 2:
    Repairs with primary key repairid.
    This table also has field machineid but here it was defined as datatype char(12) and is actually holding characters not numeric values. <-- This is bad!

    I want machineid in table 2 to be numeric not char.
    Ideally, I would like to convert machineid char(12) to numeric(12,0) but that's not possible.

    So, the thought was to rename machineid to machineid_old and create a new machineid field properly defined as numeric.
    Then I could do an UPDATE in table 2 and set machineid='1' where say machineid_old='Machine1'
    Of course, this would entail fixing all my code which used to refer to char but now refers to numeric.

    I got through the first part okay renaming and updating but then I quickly got bogged down with Alpha and got myself into too much of a mess.
    So basically I got frustrated and gave up but it still bugs the heck out of me.

    Do any of you database gurus have any sage advise or an easier way to approach this design mistake?
    Alpha Anywhere v12.4.6.3.0 Build 7582-5569 IIS v10.0 on Windows Server 2019 Std in Hyper-V

  • #2
    Re: Correcting Design Mistake - Primary Key Type

    I guess if you have a lot of linking grids etc it might be an issue. But I have done this typically in fairly short order. One way to do it if it is a real problem for you is to keep the machineid field just as it is for now. Add the new numeric id with a different name like mac_id or something. Update that field. Put in a trigger for you inserts to update the machineid based upon the mac_id. That keeps the old system in sync with the new one. Then do your Alpha conversions at your own pace. When it's all done. backup the sql and remove the machineid field and the trigger.
    Peter
    AlphaBase Solutions, LLC

    Peter@AlphaBaseSolutions.com
    https://www.alphabasesolutions.com


    Comment


    • #3
      Re: Correcting Design Mistake - Primary Key Type

      Peter,

      I never thought about doing it that way, I like your way of thinking. This sounds very doable.
      I'll have to read up on how to set up triggers.

      Thanks very much for your thoughts. Really appreciate it.

      Stephen
      Last edited by iRadiate; 12-10-2013, 02:01 PM.
      Alpha Anywhere v12.4.6.3.0 Build 7582-5569 IIS v10.0 on Windows Server 2019 Std in Hyper-V

      Comment


      • #4
        Re: Correcting Design Mistake - Primary Key Type

        Well it took me a lot of looking at how to do triggers but I finally have it set up and working. When a user inserts or updates a record, the MacID is set to the proper numeric MachineID based on the incorrect char MachineID.

        Code:
        CREATE TRIGGER tr_Repairs_Set_MacID
        ON Repairs
        FOR Insert, Update
        AS
        BEGIN
        
           DECLARE @ID int
           SELECT @ID = RepairID From Inserted
        
           UPDATE Repairs
        
              SET MACID = CASE WHEN MachineID = '21A' THEN 1
                               WHEN MachineID = '21B' THEN 25
                               WHEN MachineID = '21C' THEN 19
                               WHEN MachineID = '21D' THEN 4
                               WHEN MachineID = '21E' THEN 21
                               WHEN MachineID = '21F' THEN 17
                               WHEN MachineID = '21G' THEN 7
                               WHEN MachineID = '21H' THEN 8
                               WHEN MachineID = '21I' THEN 9
                               WHEN MachineID = 'Tomo' THEN 10
                               WHEN MachineID = 'CT1' THEN 11
                               WHEN MachineID = 'CT2' THEN 24
                               WHEN MachineID = 'Pantak' THEN 13
                               ELSE NULL
                          END
        
        END
        So now I have it being kept in sync. On to making the web components work with the new MacID. Feel like I'm getting somewhere with this now.
        Thanks again Peter
        Alpha Anywhere v12.4.6.3.0 Build 7582-5569 IIS v10.0 on Windows Server 2019 Std in Hyper-V

        Comment


        • #5
          Re: Correcting Design Mistake - Primary Key Type

          Okay, well maybe not quite there yet.
          The trigger is updating ALL MacID values when I really only want to update the current record.

          Is there a way in the SQL query get the current record and UPDATE only the current record. I think I need a WHERE @ID = RepairID From Inserted or something like that but I can't make it work with the CASE statement?
          Alpha Anywhere v12.4.6.3.0 Build 7582-5569 IIS v10.0 on Windows Server 2019 Std in Hyper-V

          Comment

          Working...
          X