OpenALPR Tool - Save and Query CSV Exports

[DLONG2]

Hi hndymann, I never tested Gymratz' alerts process. Hope either he or another might be able to fill you in soon. The viewer app can set the plates to be alerted while the downloader app will push out the alerts when it sees a match in the alerts listings. Where and how the configuration occurs I wish I could help.

 

[hndymann]

I went through the code to see how alerts are supposed to work.
If you select a license plate within the viewer, you have the option to select status, fill in description and fill out an alert box.
If any value is entered within Alert? box and saved, it will alert you via email or Pushover app (depending on your settings).
I had entered an incorrect login for my email and it caused the program to crash when an alert was found and attempted to send. Make sure your settings are correct if you decide to use this function.

Luke

 

[d.lux]

Here are a few hurdles I encountered while setting things up --

LPR Viewer
MaxDate on the DateTimePicker was set to a date in the past and the "new time" (Now) was greater than that date.
I just set the MaxDate on the DTP object to something like year 9999

Currently the viewer is failing on the "Set_Plate_Details" function/routine, I'm assuming this is due to the database being empty?
Not sure why I am not seeing data flowing from OpenALPR "cloud", but I am seeing plates being read and them being stored by the OpenALPR application.
LPR downloader is connected and is running, but I am not seeing it import or skip anything?


LPR Downloader
In attempts at getting the SQL connection string setup, I was tired of changing it and "hoping I would see something work".
I added a "connection string test button" to the application with the following code

        private void btnTestSqlSvrConn_Click(object sender, EventArgs e)
        {
            using (sql_Connection = new SqlConnection(Constants.str_SqlCon))
            {
                Console.WriteLine(Constants.str_SqlCon);
                try
                {
                    sql_Connection.Open();
                    txtConnStatus.ForeColor = Color.Green;
                    txtConnStatus.Text = "Success";
                }
                catch (SqlException)
                {
                    txtConnStatus.ForeColor = Color.Red;
                    txtConnStatus.Text = "Failed";
                }
            }
        }

Noted that my source had to be "localhost" for the connection to work...


Now to figure out why the "data isn't flowing"...I'll keep picking at it...

 

[d.lux]

Hi again,

Something is amiss in this section of code (specifically the execute command / routine UploadStream) --

                    using (sql_Connection = new SqlConnection(Constants.str_SqlCon))
                    {
                        foreach (DataRow csvRow in dt_CSVData.Rows) / For each record, we're going to do a separate insert.
                        {
                            using (sql_Command = new SqlCommand("Insert Into LPR_PlateHits (" + str_FieldNames + ") VALUES (" + str_ValueNames + ")", sql_Connection))
                            {
                                foreach (DataColumn csvColumn in dt_CSVData.Columns) / For each available column, add the parameter.
                                {
                                    sql_Command.Parameters.AddWithValue("@" + csvColumn.ColumnName, csvRow[csvColumn.ColumnName].ToString());
                                }

                                sql_Connection.Open();
                                Console.WriteLine(sql_Command.CommandText);
                                sql_Command.ExecuteNonQuery();
                                sql_Connection.Close();
                            }
                        }
                    }

I added the write line so I could see what it was attempting to execute...

Within VS the error is - Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll'

I googled a few reasons for this and attempted to make the pk auto increment and this didn't change the behavior...

Any idea what's going on?

Will the downloaded CSV help in this case?

Maybe shoot me some general ideas on where to look?

I am running the 2019 iterations of all the software packages to attempt to make this work...

Thanks for any input

 

[hndymann]

Hi again,

Something is amiss in this section of code (specifically the execute command / routine UploadStream) --

                    using (sql_Connection = new SqlConnection(Constants.str_SqlCon))
                    {
                        foreach (DataRow csvRow in dt_CSVData.Rows) / For each record, we're going to do a separate insert.
                        {
                            using (sql_Command = new SqlCommand("Insert Into LPR_PlateHits (" + str_FieldNames + ") VALUES (" + str_ValueNames + ")", sql_Connection))
                            {
                                foreach (DataColumn csvColumn in dt_CSVData.Columns) / For each available column, add the parameter.
                                {
                                    sql_Command.Parameters.AddWithValue("@" + csvColumn.ColumnName, csvRow[csvColumn.ColumnName].ToString());
                                }

                                sql_Connection.Open();
                                Console.WriteLine(sql_Command.CommandText);
                                sql_Command.ExecuteNonQuery();
                                sql_Connection.Close();
                            }
                        }
                    }

I added the write line so I could see what it was attempting to execute...

Within VS the error is - Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll'

I googled a few reasons for this and attempted to make the pk auto increment and this didn't change the behavior...

Any idea what's going on?

Will the downloaded CSV help in this case?

Maybe shoot me some general ideas on where to look?

I am running the 2019 iterations of all the software packages to attempt to make this work...

Thanks for any input

I'm not at my computer to look closely at this code. Make sure you have the newest GitHub release. Default was not set to the newest so I had to accept the changes in order to make my code work.

 

[d.lux]

Ayo!

Re-reading the thread helped add Message Boxes to the error trapping to "see" what the errors really were/are...

At times, I see "Device is not ready" so I may have to kill the msgbox, but it's finally working...

The 2 main fixes I had to do -

Adjust the "MaxDate" Date Time Picker objects "MaxDate" in the properties of that object (StartDate).
Adjust the "companyname" column to an "Int" data type (my company "value" coming from OpenALPR is 41662)

All good!

You should have a "PayPal Donate" link around, I'm sure there would be a few drop a couple bucks in it..

Thanks for the work!

 

[riluxg]

Hi de-lux
i am not very good with visual studio

Adjust the "MaxDate" Date Time Picker objects "MaxDate" in the properties of that object (StartDate).
Adjust the "companyname" column to an "Int" data type (my company "value" coming from OpenALPR is 41662)

could you show where this is done

 

[d.lux]

Hi @riluxg

There is 2 pieces to the adjustments I had to do -

In visual studio adjust the timepicker...Looking at it again, I think it was the "dtp_end" time picker object that was giving me issue (just check them both)

Make sure you are in design mode for the interface, single click on the date picker and then view the max date in the properties of that object




Second is in SSMS

Connect to your sql instance, open Databases/LPR/Tables/dbo.LPR_PlateHits/Columns

Find the company column and right click it then select modify

Drop down the data type combo box and change it to type int




Hope this helps!

Side note -
I am always seeing the LPR downloader "skip" new plate reads, although it appears it is still being captured in the LRP viewer...

I'm taking a wild guess, but maybe when my cloud trial is over (OpenLPR) I will begin to see the downloader "import" new files/plates...

 

[gpower07]

great work...just finish my lpr camera with led spot light. will try that tomorrow.

 

[Robert G.]

Looks like changes at OpenALPR are coming. I fear it is the death of their free two camera license and downloading data with software like has been created in this thread may stop working.

See my posting here and do share any information you have about what OpenALPR is doing.

 

[tech101]

I saw your post .. I hope we find a good alternate quick.. This was really really helpful this openalpr ..

 

[d.lux]

Yep, I experienced this recently, I had to create a "new email account" to get another month...

Now I see my new account (that has been running a few weeks and was stating 6 days left) has 28 more days?

I wonder if one could leverage the API and capture plates this way? (For free)?

 

[prince1566]

anyone find a fix to automate the download, when I upload manually it skips everything and imports nothing but still somehow it shows up in the viewer.

 

[d.lux]

Hi @prince1566

Are you able to "see" anything in the LPR viewer?

My LPR Downloader always skips the import but I am able to see the newly added images, I am assuming this is due to the files "already being there" prior to importing them...

Will be tough to confirm my hypothesis though as the "free tier" appears to be going away...

Hope this helps

 

[DLONG2]

The images are stored by the OpenALPR client software, and within the Agent Configuration there's a setting for how much storage to use for the images. The viewer will reference the image's ID in the web server which the OpenALPR client software creates.

If you know a plate capture's unique ID, you can view it in any browser which has network access to the ALPR web server.

Where red font shows the web server IP, the blue font shows the port number, and the purple font shows the unique photo ID:

"HTTP:/192.168.1.202:8355/img/ooooooo_LONG_STRING_ooooooo.JPG"

 

[prince1566]

Hi @prince1566

Are you able to "see" anything in the LPR viewer?

My LPR Downloader always skips the import but I am able to see the newly added images, I am assuming this is due to the files "already being there" prior to importing them...

Will be tough to confirm my hypothesis though as the "free tier" appears to be going away...

Hope this helps

Yes i can see it in the viewer, it just wont download the cvs automatically and add it to the database,

 

[d.lux]

Hi @prince1566

Just make sure they most recent plate image is fairly new ( < 5 mins) and I would say it is working as it should.

Mine has the same behavior, but new plates keep coming in...

Hope this helps

 

[prince1566]

Hi @prince1566

Just make sure they most recent plate image is fairly new ( < 5 mins) and I would say it is working as it should.

Mine has the same behavior, but new plates keep coming in...

Hope this helps

Thanks, You are correct, It is auto-downloading and adding to the database, for some reason it is just showing 0,0 in the LP Downloader log But it is working.
Thanks to everyone who worked on making this happen.

 

[azicemachine]

Bit the bullet today and signed up for the $5 a month service from OpenALPR. Process was smooth, but the plate downloads to SQL stopped for the LPR Downloader program after I made the change. Downloader would not upload a csv file manually either. No error messages from either the Downloader or the LPR Viewer.

I opened the database with SQL Server Profiler 18 and looked at the activity during a manual upload attempt. I copied the statement asking for the download and executed it in SQL Server Management Studio to see if there were errors.

For some reason, SQL was now showing an integer error overflowing on a 32850 number. I remembered I had to make the int change for the 'company' field during initial configuration, and I was wondering why I was seeing this error again. It turns out that I was getting a new overflow error for the "camera_id" field. Opened the table and modified that column from 'smallint" to "int". Forced another manual upload and new records appeared in SQL and the LPR Viewer.

So my LPR Downloader and Viewer are back up after moving to the paid service in openALPR.

Unfortunately, as others have mentioned, there is no data for the Color, Make, Model, and Body in the new records.

Many thanks to Gymratz, DLong2, and everyone else who contributed time and comments for the LPR Downloader and LPR Viewer. I've had these programs running 24/7 for two months now and other than initial configuration and today's troubleshooting, I've had no issues with either of these.

 

[Fubduck]

Unfortunately, as others have mentioned, there is no data for the Color, Make, Model, and Body in the new records.

Would it be possible for you to provide the data items that are downloaded?