Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2008
    Posts
    7

    Unanswered: User defined variable

    Hello

    Is there a way that I can create a user defined variable in my database? I have a fairly large chunk of xml that I would like to store in the database that can be updated by clients ( in rare cases ). I could use other mechinisms to allow access to it, but all other application related information is stored nicely in this one place, I hate to vary for this single piece.

    Thank you for your time and thoughts.
    Patrick

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    in sql 2005 there is a xml datatype and all kinds of bells and whistles for querying and manipulating it.

    SQL 2K also has some xml functionality, but no defined datatype for it and you have to store it as a text.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2008
    Posts
    7
    Hi Thrasymachus

    Thanks for your reply. Yep, I found that date type, it is great for the XML data I am keeping in some of my columns. My issue is that the one piece of data in question is really a singleton. I thought of an ugly solution of keeping a table of "Variables" that basically has a single row with each column representing one of the variables, it would seem that there would be a nicer way to do this type of thing - even though it is probably not a proper design / use of SQL Server.

    Thanks again
    Patrick

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Patrick

    I'm confused and I'll bet others are. How about backing up a bit and instead of explaining the solution (user defined variables in SQL Server) you try explaining the problem (e.g. I want my application to keep track of certain session level values supplied by the user...)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    CREATE TABLE user_defined_stuff
    ( userid VARCHAR(16) NOT NULL PRIMARY KEY /* one chunk per user */
    , large_chunk_of_xml BLOB NOT NULL
    );

    my job here is done

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2008
    Posts
    7
    Hello

    Sure, good idea.

    My application keeps track of settings for all users. The settings are made up of a combination of layers that are merged at runtime. So it works in this order.

    1. On application startup the default settings are read and applied
    2. User updated settings are read and applied

    The default settings are the same for all users and may need to be updated from time to time by some administrator. These default settings are the singleton I speak of - that I would rather not duplicate.

    Does this make more sense? I should have probably specified earlier that I am working with SQL Server 2005.

    Thanks,
    Patrick

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It does - thank you. Do these settings need to persist between sessions (i.e. when the user comes back the next day the settings are as they were left the previous day)?
    If not - why can you not cache all this stuff in the front end application? What is your application software BTW?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2008
    Posts
    7
    Hi

    Yep, they must persist. The users may travel and use different workstations to access the application. Their individual settings must follow them always using the current default settings as a base.

    Thanks
    Patrick

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I unnerstan' now

    Yep - your solution is fine (though you'll need to include the users login name in there). Or Rudy's XML table - more this if the variables are likely to change over time.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2008
    Posts
    7
    Hi There

    Interesting, just to clarify, you are thinking that the solution of using a "Variables" table in the way I am is ok? Do people do this? I was pretty much expecting to get flamed for it. I was thinking that you all would point me to some way to store this value into a variable of some type - if that is possible.

    Thanks
    Patrick

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to persist this between sessions & clients. So it must be persisted to disk in a central location. You have a database server... where else but stick it in the database? I would have a load of variables at the client (or xml whatever) that you use to cache these values for the session. Populate these on log on and write changes to the database.

    You don't really typically want to have SQL Server keep application data in memory between commands. You want to connect, get\ write data, disconnect. You don't (ever I think...) want SQL Server to "remember" stuff in memory between requests. (Of course it does cache data etc but you don't need to worry about this).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Mar 2008
    Posts
    7
    Hi

    I would completely agree with not having the server keep things stored in memory. I was hoping that it may have a mechinism to cache single values to disk that could be referenced as would a variable. The thing that gave me the idea was the now deprecated CREATE DEFAULT TSQL command, it seems like that would have given me exactly what I wanted.

    Thanks for all of your help, I really appreciate it. Is this forum always so responsive?
    Patrick

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by PatrickCBrown
    Thanks for all of your help, I really appreciate it. Is this forum always so responsive?
    Patrick
    One tries ones best
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by PatrickCBrown

    Thanks for all of your help, I really appreciate it. Is this forum always so responsive?
    Patrick
    none of us really work. we just fart around on the Net all day.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  15. #15
    Join Date
    Mar 2008
    Posts
    7
    Sorry, I just dozed off there for a bit...what was that?

Posting Permissions

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