Search lines in database

[UGent_Student]

Hello,

For a school project I have to be able to scan a barcode with a camera and then search in a database if that barcode is in the file and then let the data behind the barcode show in labview. 

I have almost all solutions but one.

I struggle with searching the scanned barcode in the database , to show the name and etc. in labview .

Below is a picture of what I have for now.

So i can see data in a speciefic line but i cannot change that line to show the line for a speciefic barcode.

Anyone who can assist?

(P.S. Many Karma point can be earnd by this )😁

[Yamaeda]

When you Select data from a DB you always get a set of data, so you need to filter it down through a Where-clause. In this case you want 'All data Where the scanned text = DB-barcode'.

If you use Get all instead of Fetch you can also do a sanity check be controlling that you do in fact only get 1 tuple back, else you or the DB is in error.

/Y

[RolfO]

There may be different alternatives dependent on the goal of your school project, to learn/use LabVIEW or SQL, or use LabVIEW and Database as a tool for solving a task.

The SQL-way would be to include a where-clause in your query string.

The LabVIEW way would be to loop through all data in the table and let LabVIEW find the correct line. If the current line in the returned data does not match correct barcode, then get next line with DB Tools Fetch Next Recordset (NXT).

I would suggest to let the database handle as much work as possible and use a where clause in the query string. And as Yamaeda mentioned, a sanity check would also be good, so either return all data to an LabVIEW array, or check if Fetch Next gives an error. (I am not familiar with fetch next so i do not know how to best use that function.)

[Yamaeda]

"I would suggest to let the database handle as much work as possible"

This is a very important point, as i upgraded a program some years ago that had become very slow at start when loading the orders. The system downloaded all of the order DB and filtered in the software instead of at the DB, causing it to download 50MB+ each start of the program. This was 10+ years ago, so 10MB networks were still fairly common ...

By changing it to a Where-question, the time dropped from some ~20s to 1s.

/Y

[UGent_Student]

Hello,
Because i don't know a lot about databases, i tried the LabVIEW way.
In attachment is the program I wrote. (photo's )
It kind of works but i get an error at the fetch block I don’t know how to fix it.

I got inspiration for this program at the examples list ( database fetch).
So i copied a few things from there.
The idea is that Scanned barcode will check in the first kollem (with the fetch) if it is the same. if not it should go to the next line in the database. show again and check again. if one's correct it shows the full data of that line.

There is probably an easier way but I don't find it for now.

some body has any ideas?

PS sorry for potato English

Kind regards
Joachim

[Yamaeda]

A simpler way would be to only grab the correct lines instead of reading through all of the DB (which in time can grow to millions of rows).

So, change the query to 'Select * where Code=[scan]' and check that you only get 1 result.

/Y

[UGent_Student · Accepted solution]

Hello, 

I would like to thank everyone fot the help.

I kinda solved the problem, maybe with some extra steps but it works.

Probebly won't handle any big databases but its a start.

Below i will put my final program incuding the filming of the barcode.

I hope you all have a wonderfull 2020

Kind regards

A greatfull student