SQL Server Barcodes
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/sqlserverbarcodes.asp
-
I am a little confused
looking at the title of the article, i was expecting of some way to print barcodes using SQL !!!!!I think, the title of article is not appropriate..
-
Nice piece of code, Mike.
From what I see, one needs to simply use the correct font (after validating characters) to print directly from SQL--is this correct?
Also, do any other TTFs exist for other symbologies such as UPC?
Dan
-
Awesome article! Very helpful. Until now I have been stuck with an ancient 3rd party tool(I know there are newer ones) and MS Access as the reporting vehicle. I am psyched to integrate this into Reproting Services. . .
-
Great code. Thanks
-
Thanks for the feedback. You are correct - the TTF fonts can be used to print from most applications. SQL Server doesn't provide much in the way of direct user input/output, and that's normally handled by a front end application. Storing your barcode text in a database and generating the barcodes through the UI should be fairly easy with the TTFs installed (see sample application).
There are other symbologies available, but generally they cost $$$. I focused on Code 39 and Code-128, because I couldn't find a good free Code-128 font. I haven't created any other TTFs yet myself, although UPC/EAN would not be overly difficult; though the rules for actually assembling a UPC/EAN barcode can be a little tricky.
-
I originally titled it "Barcode Toolkit", but it was modified.
-
Great! If you don't mind, I'd love an update on your work in that area; I'm sure it would be helpful to others (myself included)! Thanks.
-
Thanks, and enjoy!
-
very cool, save it in my personal collection
-
GREAT!!!
-
Very interesting, it has given me something to research.... but I am confused by one thing. If I use your code to generate Code 128 barcodes for the string 'Code128', I get the following for Code A, B and C:
Code A: Äcode128DÇ
Code B: Åcode128EÇ
Code C: Æcode128FÇ
But if you go to http://www.adams1.com/pub/russadam/128table.html, it says:
"To get the extended ASCII decimal, add 32 to the decimal value in the first column. There is a nice extended ASCII table here."
If I add 32 to 103 (Start A), 104 (Start B), 105 (Start C) and 106 (Stop) I do not get the characters: Ä, Å, Æ and Ç. See their referenced ASCII table.
It seems to be some sort of impedance mismatch. What am I missing?
TIA
dc
-
The ASCII character codes for the control characters (Start A, Start B, etc.) are not standardized. The standard defines the relationship between the generated barcode and the barcode reader/scanner. The barcode scanner doesn't differentiate between which keyboard characters you used to generate a barcode, and which you didn't. After all, if we're using EBCDIC or some other character set the relationship between font codes and their generated barcodes changes completely. Of course, simple usability keeps us from assigning the barcode for "Z" to the font generated when the letter "d" is printed in any character set.
The reason I chose these particular ASCII characters to represent the control codes is that they are part of a large block of font codes above the standard ASCII character set that are all uppercase.
The uppercase part is important if you want to generate barcodes using the TTF's in MS Word, since by default this input:
äcode128DÇ
Is automatically capitalized in Word to become:
Äcode128DÇ
A very subtle but annoying difference, and just enough to keep your barcodes from scanning properly.
-
I have found occasion to need a way in SQL server to validate and/or calculate UPCa and EAN-8 check digits. I wrote a few UDFs in SQL Server 2000 to accomplish this. I also wrote one for EAN-13 since it was so similar to UPCa, but haven't tested/needed it yet.
I will include the functions here in case anyone wants to improve upon them. Probably a little logic could be added to combine some of them into a single function. Possibly perfomance could be increased by loading the characters of the string into an array to perform the validatation and calculations. These functions currently accept the UPC codes with or without the check digit. It ignores it either way, so input == output if the check digit is correct.
--This Function takes an 11 or 12 character input and outputs the full UPCa with correct check digit calculated.
--Possible improvements include checking input string for '[0-9]' input only, and perhaps placing input string into and integer array.
--Also, this could perhaps be modified to work for EAN-8 and maybe even EAN-13 as well.
CREATE FUNCTION [dbo].[GenerateFullUPCa] (@InputUPC nchar(20))
RETURNS nchar(12)
BEGIN
DECLARE @Sum int
DECLARE @ChkDgt nchar(1)
IF ( (Len(@InputUPC) 12) )
BEGIN
RETURN NULL
END
ELSE IF (Len(@InputUPC) = 12)
BEGIN
SET @InputUPC = Left(@InputUPC, 11)
END
SET @Sum = 3 * ( CAST(Substring(@InputUPC,1,1) AS int) + CAST(Substring(@InputUPC,3,1) AS int) + CAST(Substring(@InputUPC,5,1) AS int) + CAST(Substring(@InputUPC,7,1) AS int) + CAST(Substring(@InputUPC,9,1) AS int) + CAST(Substring(@InputUPC,11,1) AS int) ) + CAST(Substring(@InputUPC,2,1) AS int) + CAST(Substring(@InputUPC,4,1) AS int) + CAST(Substring(@InputUPC,6,1) AS int) + CAST(Substring(@InputUPC,8,1) AS int) + CAST(Substring(@InputUPC,10,1) AS int)
IF (@Sum % 10) = 0 BEGIN SET @ChkDgt = '0' END
ELSE IF (@Sum % 10) = 1 BEGIN SET @ChkDgt = '9' END
ELSE IF (@Sum % 10) = 2 BEGIN SET @ChkDgt = '8' END
ELSE IF (@Sum % 10) = 3 BEGIN SET @ChkDgt = '7' END
ELSE IF (@Sum % 10) = 4 BEGIN SET @ChkDgt = '6' END
ELSE IF (@Sum % 10) = 5 BEGIN SET @ChkDgt = '5' END
ELSE IF (@Sum % 10) = 6 BEGIN SET @ChkDgt = '4' END
ELSE IF (@Sum % 10) = 7 BEGIN SET @ChkDgt = '3' END
ELSE IF (@Sum % 10) = 8 BEGIN SET @ChkDgt = '2' END
ELSE IF (@Sum % 10) = 9 BEGIN SET @ChkDgt = '1' END
ELSE BEGIN SET @ChkDgt = 'Z' END
RETURN Left(@InputUPC,11) + @ChkDgt
END
--This Function takes a 7 or 8 character input and outputs the full EAN-8 with correct check digit calculated.
--Possible improvements include checking input string for '[0-9]' input only, and perhaps placing input string into and integer array.
--Also, this could perhaps be integrated with UPCa and maybe even EAN-13 functions as well.
CREATE FUNCTION [dbo].[GenerateFullEAN8] (@InputUPC nchar(20))
RETURNS nchar(8)
BEGIN
DECLARE @Sum int
DECLARE @ChkDgt nchar(1)
IF ( (Len(@InputUPC) 8) )
BEGIN
RETURN NULL
END
ELSE IF (Len(@InputUPC) = 8)
BEGIN
SET @InputUPC = Left(@InputUPC, 7)
END
SET @Sum = 3 * ( CAST(Substring(@InputUPC,1,1) AS int) + CAST(Substring(@InputUPC,3,1) AS int) + CAST(Substring(@InputUPC,5,1) AS int) + CAST(Substring(@InputUPC,7,1) AS int) ) + CAST(Substring(@InputUPC,2,1) AS int) + CAST(Substring(@InputUPC,4,1) AS int) + CAST(Substring(@InputUPC,6,1) AS int)
IF (@Sum % 10) = 0 BEGIN SET @ChkDgt = '0' END
ELSE IF (@Sum % 10) = 1 BEGIN SET @ChkDgt = '9' END
ELSE IF (@Sum % 10) = 2 BEGIN SET @ChkDgt = '8' END
ELSE IF (@Sum % 10) = 3 BEGIN SET @ChkDgt = '7' END
ELSE IF (@Sum % 10) = 4 BEGIN SET @ChkDgt = '6' END
ELSE IF (@Sum % 10) = 5 BEGIN SET @ChkDgt = '5' END
ELSE IF (@Sum % 10) = 6 BEGIN SET @ChkDgt = '4' END
ELSE IF (@Sum % 10) = 7 BEGIN SET @ChkDgt = '3' END
ELSE IF (@Sum % 10) = 8 BEGIN SET @ChkDgt = '2' END
ELSE IF (@Sum % 10) = 9 BEGIN SET @ChkDgt = '1' END
ELSE BEGIN SET @ChkDgt = 'Z' END
RETURN Left(@InputUPC,7) + @ChkDgt
END
--This Function takes a 12 or 13 character input and outputs the full EAN-13 UPC with correct check digit calculated.
--Possible improvements include checking input string for '[0-9]' input only, and perhaps placing input string into and integer array.
--Also, this could perhaps be modified to work for UPCa and EAN-8 functions.
CREATE FUNCTION [dbo].[GenerateFullEAN13] (@InputUPC nchar(20))
RETURNS nchar(13)
BEGIN
DECLARE @Sum int
DECLARE @ChkDgt nchar(1)
IF ( (Len(@InputUPC) 13) )
BEGIN
RETURN NULL
END
ELSE IF (Len(@InputUPC) = 13)
BEGIN
SET @InputUPC = Left(@InputUPC, 12)
END
SET @Sum = 3 * ( CAST(Substring(@InputUPC,2,1) AS int) + CAST(Substring(@InputUPC,4,1) AS int) + CAST(Substring(@InputUPC,6,1) AS int) + CAST(Substring(@InputUPC,8,1) AS int) + CAST(Substring(@InputUPC,10,1) AS int) + CAST(Substring(@InputUPC,12,1) AS int) ) + CAST(Substring(@InputUPC,1,1) AS int) + CAST(Substring(@InputUPC,3,1) AS int) + CAST(Substring(@InputUPC,5,1) AS int) + CAST(Substring(@InputUPC,7,1) AS int) + CAST(Substring(@InputUPC,9,1) AS int) + CAST(Substring(@InputUPC,11,1) AS int)
IF (@Sum % 10) = 0 BEGIN SET @ChkDgt = '0' END
ELSE IF (@Sum % 10) = 1 BEGIN SET @ChkDgt = '9' END
ELSE IF (@Sum % 10) = 2 BEGIN SET @ChkDgt = '8' END
ELSE IF (@Sum % 10) = 3 BEGIN SET @ChkDgt = '7' END
ELSE IF (@Sum % 10) = 4 BEGIN SET @ChkDgt = '6' END
ELSE IF (@Sum % 10) = 5 BEGIN SET @ChkDgt = '5' END
ELSE IF (@Sum % 10) = 6 BEGIN SET @ChkDgt = '4' END
ELSE IF (@Sum % 10) = 7 BEGIN SET @ChkDgt = '3' END
ELSE IF (@Sum % 10) = 8 BEGIN SET @ChkDgt = '2' END
ELSE IF (@Sum % 10) = 9 BEGIN SET @ChkDgt = '1' END
ELSE BEGIN SET @ChkDgt = 'Z' END
RETURN Left(@InputUPC,12) + @ChkDgt
END
-
Mike,
I liked the article and I had been hoping for a chance to use it, then along came a project (in a hurry, naturally). Well, I thought it would be no problem and luckily I gave no such promises because we are having a problem with alphanumeric values using the ttf that was included in the download. The font sometimes prints unreadable barcodes and sometimes prints incorrect characters (i.e. it prints "E" when given an "F"). The interesting thing is that if we could stick to numeric characters we'd be okay; but, of course, no project in a hurry would come in with simple requirements. I see no similar responses to the article so I am really confused
BillyBoy
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply