Example: Setup DSN Less  Connection – O2001SSW to MS SQLServer Express

Task: transfer data from OPN2001 barcode scanner to MS SQL Server

Step 1. Prepare table in MS SQL server

 

Use MyDatabase

GO

 

create table barcodelog

(

Barcode nvarchar(50) not null,

ScannerID nvarchar(24),

BarcodeType nvarchar(24),

ScannerTStamp DateTime,

LogTime DateTime,

BarcodeCount Int

)

Warning: user with limited privileges or only insert privilege on barcodelog table is strongly advised!

Modify above SQL statements according to your database/security administrator recommendation.

Step 2. Setting up MSSQL DSN-Less connection.

DSN-Less connection does not require invoking ODBC manager. Connection descriptor can be entered directly in O2001SSW configuration dialog.

Click on ‘configure log to database’ button.

Enter following value in Connection Descriptor field:

Driver={SQL Server Native Client 10.0};Server=.\SQLExpress;Database=MyDatabase;Trusted_Connection=Yes;

Enter SQL insert template in the Insert SQL field:

Insert into barcodelog(Barcode,ScannerID,BarcodeType,ScannerTStamp,LogTime,BarcodeCount) values (?,?,?,?,?,?)

Note: question marks are placeholders for format – field selection variables which will be replaced by actual values read from OPN2001 scanner when SQL insert statement is executed.

Enter exact number of format variables as number of question marks in Format Field Selection:

{Barcode}, {ScannerID}, {BarcodeType}, {ScannerRTC}, {SysTime}, {BarcodeCount}

image

Image: O2001SSW odbc to setup – save barcodes from OPN2001 memory to MSSQL Server Express.

Click ‘Test Now’ button to verify setup.

Available values for format:

{Barcode}

Barcode stored in OPN-2001 memory

{ScannerID}

Unique scanner ID (set by manufacturer)

{BarcodeType}

Barcode type (EAN13, UPC, EAN128 …)

{ScannerRTC}

Scan time - Scanner real time clock (If enabled)

{SysTime}

System – computer time

{BarcodeCount}

Total number of barcodes in scanner memory

{SessionCounter}

Total number of barcodes read from scanner since program was started

 

Visit:  http://www.connectionstrings.com/sql-server-2008  for more connection string examples.