SQL Server Barcodes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Michael Coles » SQL Server Barcodes

22 posts, Page 2 of 3

««1 2 3 »»

SQL Server Barcodes

Rate Topic

Display Mode

Topic Options

Author

Message

apinnelli

Posted Tuesday, September 13, 2005 9:33 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 05, 2011 8:42 AM
Points: 37, Visits: 11

GREAT!!!

Post #219245

David Clark-195882

Posted Tuesday, September 13, 2005 5:05 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 5:46 PM
Points: 1, Visits: 3

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

Post #219397

Mike C

Posted Wednesday, September 14, 2005 8:57 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:22 PM
Points: 1,276, Visits: 1,112

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.

Post #219654

Ryan Hathaway

Posted Monday, November 07, 2005 7:13 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, August 10, 2007 9:50 AM
Points: 461, Visits: 1

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) < 11) OR (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) < 7) OR (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) < 12) OR (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

Post #235432

William Millett

Posted Monday, November 21, 2005 2:52 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 23, 2009 12:06 PM
Points: 11, Visits: 2

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

Post #238670

Mike C

Posted Tuesday, November 29, 2005 8:01 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:22 PM
Points: 1,276, Visits: 1,112

I reviewed the barcode TTF for Code 128, and it appears that the codes for "C" and "F" were not saved correctly. I suspect it had something to do with using a trial version of the font editing software. I've used another package to update the fonts and have emailed them to you directly. I'll review the remaining barcode fonts and submit the corrected versions to Sql Server Central after I test them further.

Thanks for the feedback!

Post #240353

William Millett

Posted Tuesday, November 29, 2005 1:07 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 23, 2009 12:06 PM
Points: 11, Visits: 2

Michael,

Thanks for your update and response. We are not having any other issues.

BillyBoy

Post #240487

Peter Chenery

Posted Monday, April 16, 2007 10:35 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 9:49 AM
Points: 370, Visits: 179

Hi,

Very nice article, which was just the kind of thing I was looking for. However, when I create a barcode for BarCode 1, it won't scan. Producing one for arCode 1 works fine, so there seems to be a problem with the B. I notice a previous poster had a similar issue which you resolved. Would it be possible to email the new files, or update the site?

Regards

Peter

Post #358667

Mike C

Posted Wednesday, April 18, 2007 12:48 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:22 PM
Points: 1,276, Visits: 1,112

Hi Peter,

Private message me your email address and I'll send it to you as soon as I can - either late tonight or sometime tomorrow. I'll also send to Steve and ask him to update on the site.

Thanks,

Mike C.

Post #359373

steven.johnson

Posted Friday, December 14, 2007 8:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 14, 2007 11:34 AM
Points: 1, Visits: 4

Can anyone help a newbie?

I am not a prgrammer. Just a networking guy stuck with managing a new database that manages our maintenance program and inventory.

The work order section is easy. But the inventory section is a nightmare in that the inventory arrives at my warehouse, then gets checked out for initial testing and configuration, then checked back into warehouse inventory, then gets issued to contractors who install it in the field (becomes part of field inventory by location), and then my technicians maintain it in the field often swapping out parts that also must be tracked.

What I want to do is create a bar code upon initial entry into the database and use that to track the item through its lifecycle at different locations. I understand that barcode readers are simply I/O devices like a keyboard. What I need is a means to interface a barcode reader with my database so that I can track the movement of this inventory.

Database setup is SQl 2005 server with a web interface using Apache and JBoss.

Any suggestions on compatible readers and 3rd party interfaces that may be a good starting point for this solution?

Thanks

Post #433419

« Prev Topic | Next Topic »

22 posts, Page 2 of 3

««1 2 3 »»

Permissions