| Author |
Topic |
|
Onamuji
Aged Yak Warrior
504 Posts |
 Posted - 2002-02-04 : 09:58:04
|
I have a table with a binary data type that i'm using for doing AND's and OR's to get multiple items from a single select... I was wondering if there is an easier way to define a check constraint for the binary data column ...CHECK ( Property = 0x1 OR Property = 0x2 OR Property = 0x4 OR Property = 0x8 OR Property = 0xF OR Property = 0x20 OR Property = 0x40 OR Property = 0x80 OR Property = 0x100 OR Property = 0x200 OR Property = 0x400 OR Property = 0x800 OR Property = 0x1000 OR Property = 0x2000 OR Property = 0x4000 OR Property = 0x8000 OR Property = 0x10000 ) I want to make sure the value has only one bit set... thus the values above... which comprimises of 16-bits ... and the check should make sure the value inserted/updated is valid... I was hoping someone could either point me in a better direction or show me something that would be a lot simplier to implement say I wanted to do this for a 16-byte field... i knew i should have finished that discreet mathematics class :-p- Onamuji |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-04 : 21:47:16
|
| Only one bit set means (mathematically) that the value (Log base 2) is a whole number....So what you want is...CHECK ((Property > 0) AND (FLOOR( LOG(Property)/LOG(2)) = (LOG(Property)/LOG(2))))and that should work for as many bytes as you like....And now for the Maths lesson (if you're interested)Obviously - according to your rules, the number can't be zero....[b](Property > 0) All the numbers with only one bit set, are all powers of 2 - ie Log of the number (base 2) will be a whole number.Log base 2 of 6 is not a whole number (=2.5849....)I have first found Log Base 2 of the value - to do this take either natural log or log base 10 and divide it by log of 2ie Log base 2 of 6 = Log(6)/Log(2) So then I just compare the result of this against the integer part of the resultFLOOR(1.2) = 1.2 - FailFLOOR(1.0)= 1.0 - OK!FINALLY - A USE FOR ALL THAT TIME IN LECTURES!!!!!--I hope that when I die someone will say of me "That guy sure owed me a lot of money"Edited by - rrb on 02/04/2002 21:50:25 |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-02-04 : 23:08:00
|
| Nice one rrb!!!!!Wow, this theory stuff might catch on....DavidMTomorrow is the same day as Today was the day before. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-02-05 : 00:08:13
|
| Thanks a lot guys! :-)- Onamuji |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-05 : 00:13:38
|
quote:
Wow, this theory stuff might catch on....
Nah, I just want to know how I can insert an image into sql server using a cursor, and have my trigger fire off an HTML email using SQL Mail.Please post your solution.  Damian |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-02-05 : 00:21:58
|
quote:
Nah, I just want to know how I can insert an image into sql server using a cursor, and have my trigger fire off an HTML email using SQL Mail.
I'd use the undocumented extended stored procedure xp_crashmyservernow===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-05 : 00:25:45
|
quote:
Nice one rrb!!!!!Wow, this theory stuff might catch on....
Thanks David, 53 posts and probably my first really helpful one --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-02-05 : 00:28:13
|
LOL Merkin  My solution would be:1) Open the File using sp_OACreate to create a binary stream2) Loop through stream and Insert into Graphic table.3) Access Hotmail Email server via sp_OACreate4) Open cursor to Graphic table and iterate until the graphic is rebuilt5) Send Email6) Get the sackDavidMTomorrow is the same day as Today was the day before. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-05 : 00:32:35
|
quote:
3) Access Hotmail Email server via sp_OACreate
Don't say that, someone will ask how  Damian |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-05 : 00:33:59
|
How?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" Edited by - rrb on 02/05/2002 00:34:47 |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-05 : 00:49:07
|
| Dammit.Damian |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-05 : 00:50:01
|
follow nr's posts for cursors  .and therez a guy called Merkin in his saner days he had written a Article on Mailing check it .About Image Insertion wait for Rob he will come and give you a good hint how to insert it (REad the second post of Rob is thishttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12378)HTH--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God is |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-05 : 01:25:44
|
| My Saner Days ???I don't recall when they were.Damian |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-05 : 01:26:58
|
| By the way Nazim. You didn't need to answer it seriously. We were being sarcastic, and as we say in Australia"Taking the piss"Damian |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-05 : 01:41:21
|
Sorry Nazim, I should've resisted, but when Merkin set him self up like that, I just couldn't.... --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-05 : 02:27:23
|
| Merkin, how did you come to the conclusion that i answered it seriously. it was just for fun. nr will not teach you how to use cursors and u wanted it using cursors.if you look at the link i provided about Rob , he talks abt Orgasms there.yeah , i had to point it to you anyhow and had to mention that article to tell that rite now you are not acting sane .--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God isEdited by - Nazim on 02/05/2002 02:28:21 |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-05 : 02:30:07
|
I guess I should've checked the link... Aw shucks, you're all so sweet--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-05 : 07:06:29
|
quote:
if you look at the link i provided about Rob , he talks abt Orgasms there.
Well, thats Rob. He has too much spare time on his hands at the moment. See, who said I was sane ?Damian |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2002-02-19 : 14:27:36
|
| >> I have a table with a binary data type that i'm using for doing AND's and OR's to get multiple items from a single select... I was wondering if there is an easier way to define a check constraint for the binary data column ... <<Binary data is a reallllllly bad idea. At least comment the code that you have written as non-standard, not portable, unmaintainable, and not even up to First Normal Form. Oh, and include your home phone number, so the poor bastard that has to figure out this code after you have left can find you. If each of the 16 bits is an attribute, it needs to be in its own column with a proper data element name, constraints and a metadata repository description. Save this kind of programming for operating systems and not for high level code. --CELKO--Joe Celko, SQL Guru |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-20 : 03:13:42
|
| Think that's a bit strong.Would agree that it's probably not a good idea to use this structure but not that it won't be understandable/maintainable.Anyone from an assembler/coms background or working for a telecoms/engineering company should be familiar with bitmaps.And so really should ppeople working on sql server considering that they are presented in columns_updated() as well as the database structure and system tables.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-02-20 : 08:27:07
|
They are used as identifiers to the front end application ... the front end application loads these values into it's own objects... the programmers consuming the object don't have to know anything about these properties ... I created them because I choose to use multiple lookup tables for different types in tables ... i.e. Projects - ProjectID - ProjectStatusID - Name - etc... ProjectStatuses - ProjectStatusID - Name The problem with this approach is that I must either A) Lookup ProjectStatusID either by ID (hard coding ID is not good makes updating a bitch) or B) by name (managers have been known to want to change this name so I must hunt down every PROC that uses it and replace it). I concluded that if I could give each row a bit attribute that was unique and use that in code then that bit could be an identifier to the row saying this is the row you want to use ... so if the ID changes or the Name changes the code will still operate properly... Maybe my reasoning is off but I have so far enjoyed programming like this ... before updating those kinds of tables were a nightmare ... ... Onamuji |
|
|
|
Next Page
|
Binary Data Type Question