SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Barcodes


SQL Server Barcodes

Author
Message
apinnelli
apinnelli
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 11
GREAT!!!



David Clark-195882
David Clark-195882
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
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
Mike C
Mike C
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2707 Visits: 1168

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.


Ryan Hathaway
Ryan Hathaway
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 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
William Millett
William Millett
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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


Mike C
Mike C
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2707 Visits: 1168

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!


William Millett
William Millett
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 2

Michael,

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

BillyBoy


Peter Chenery
Peter Chenery
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 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
Mike C
Mike C
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2707 Visits: 1168

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.


steven.johnson
steven.johnson
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search