6 Replies Latest reply on Mar 3, 2016 9:33 AM by beverly

    Query Globals via ODBC

    belg4mit

      How does one access GLOBALS via ODBC?

        Select * FROM GLOBALS;

      Returns a list of columns but not values. How do I specify an index to access the globals for a given record? I've tried supplying the foreign key used for other tables in a WHERE clause to no avail.

       

      Merci beaucoup!

        • 1. Re: Query Globals via ODBC
          Mike_Mitchell

          Globals can't be indexed. The values are session-specific to the user.

           

          I'm not sure what you mean by "for a given record". A field using global storage has the same value across all records. Can you describe in more detail what you're trying to accomplish here?

          • 2. Re: Query Globals via ODBC
            belg4mit

            I'm coming at this from an RDBMS background, and working with a database someone else had designed. We have several somewhat normalized tables, that all have a foreign key set the primary key of a Master table. There are a number of globals that relate to the data in the normalized tables, but they relate to the primary key rather than individual entries and I'm trying to figure out how to access them i.e; Given:

             

              Master.pKey, Master.Name...

              Item1.fKey, Item1.Name...

              GLOBALS

             

            I can

             

              Select * FROM Item1 WHERE fKey=...;

             

            But I need to access the globals for the same foreign keys as well. Globals are at least partially exposed via ODBC:

             

              jpierce@dbi:ODBC:DSN> Select * From GLOBALS;/

            PreviousLayout,Scratch,Dim1,Dim2,Area,Thickness,InsType,Rvalue,Inches,Feet,Rise,VaultArea,Watts,BtuHr,Tons,

            Room,RoomNumber,BulbType,BulbShape,SpecialtyFeature,FixtureNumber,Hardwired,BulbQuantity,ThermostatType,

            ThermostatNote,ThermostatSummerDaySetPt,ThermostatSummerNightSetPt,ThermostatWinterDaySetPt,

            ThermostatWinterNightSetPt,BDBaseline,BDRing,BDCFM50,BDNotes,BsmtArea,BsmtCFACV,BsmtNotes

            BsmtFinished,BsmtDirectCond,SearchField,SortField,DuctLocation,DuctMaterial,DuctSupplyPctArea,

            DuctReturnPctArea,DuctReturnRvalue,DuctSupplyRvalue,DuctInsulation,PhotoType,FaucetRoom,FaucetType,

            FaucetFlowRate,FaucetNotes,FaucetRoomNum,FaucetAerator,BDPhoto,PoolType,PoolHeated,PoolNotes,

            DuctSealing,SiteFilter,REMFile,Input1,Input2,Input3,Input4,Input5,Input6,SunspaceWindowGlassType,

            SunspaceWindowUvalue,SunspaceWindowSHGC,SunspaceWindowArea,SunspaceWindowOHDepth,

            SunspaceWindowOHTop,SunspaceWindowOHBottom,SunspaceWindowOrientation,SunspaceWindowNotes,

            SunspaceSkylightPitch,SunspaceWallArea,SunspaceWallMassType,SunspaceWallMassThickness,

            SunspaceWallRvalue,SunspaceWallAutoFanCouple,SunspaceWallFanCFM,SunspaceWallNotes,

            SunspaceInteriorMassStorage,InteriorMassLocation,InteriorMassDrywallThickness,LightingRoomType,

            LightingRoomNumber,DevelopmentSearch

            [0 rows of 90 fields returned]

             

            ...so I'm assuming there must be some way

             

            I cannot put the globals in the relevant normalized tables, because they do not pertain to any given feature in that table, but instead all of them that match the foreign key.

            • 3. Re: Query Globals via ODBC
              Mike_Mitchell

              There are no globals "for" a given primary or foreign key. This is because there's only one value of a global, and it can't be indexed, and thus can't be the target of a relationship. Globals can be changed from anywhere in the solution, regardless of any relational joins, so there's really no specific value of a global for any given record. It just has one value, and since it can't be indexed, you might not be able to access it directly via ODBC (honestly, I've never tried).

               

              I really don't understand your description of the database. A globals table is sometimes used as the parent side of a relationship, but you can't go the other way with it.

               

              But globals will have only the value they had when the database was last closed locally. They change when they're set during a user session, but those changes can't be seen externally because they're local to that user only.

               

              So again, for what purpose are you needing to access the global values, and what are you expecting?

              • 4. Re: Query Globals via ODBC
                belg4mit

                To clarify, GLOBALS is a table where all of our global fields are put. When viewed via the manage database interface or via SQL it s listed as having 0 records. But the corresponding values are accessible in hosted views.

                • 5. Re: Query Globals via ODBC
                  belg4mit

                  Doh. I see. I was misreading the layout. The global is being used as a temporary variable, it's value is later added elsewhere. Sorry for the confusion.

                  • 6. Re: Query Globals via ODBC
                    beverly

                    And in reality a global field can have "value", but the layout have NO records.

                    I'd be interested to see if there is a difference in SQL query results (via ODBC sharing or ExecuteSQL) if the global fields have value, but no records.

                     

                    beverly