Announcement

REXYGEN forum has been moved. This one is closed.
Head over to the new REXYGEN Community Forum at https://forum.rexygen.com.

Looking forward to meeting you there!
 

#1 2017-09-29 07:17:41

mcarroll
Member
From: Australia
Registered: 2017-02-14
Posts: 18

Help with DbDrv error - connecting to database

Hi all, I have 2 Raspberry Pi's, one is connecting to my database fine, the other throws the following errors continuously:

2017-09-29;15:21:39.362;Error;DBDRV: SQL error detail (code=1065     state='S1000'     string='[MySQL][ODBC 5.1 Driver][mysqld-5.5.52-MariaDB]Query was empty');
2017-09-29;15:21:39.382;Error;DBDRV: SQL request failed (idx=3     result=-1     sql='');
2017-09-29;15:21:39.382;Error;DBDRV: SQL error detail (code=1065     state='S1000'     string='[MySQL][ODBC 5.1 Driver][mysqld-5.5.52-MariaDB]Query was empty');
2017-09-29;15:21:39.522;Error;DBDRV: SQL request failed (idx=0     result=-1     sql='');
2017-09-29;15:21:39.522;Error;DBDRV: SQL error detail (code=1065     state='S1000'     string='[MySQL][ODBC 5.1 Driver][mysqld-5.5.52-MariaDB]Query was empty');
2017-09-29;15:21:39.542;Error;DBDRV: SQL request failed (idx=1     result=-1     sql='');

I have swapped the config from the working unit to the other one, with the same result.  Did I forget to do something on the second unit, when I configured the database connector?

Thanks in advance
Michael

Last edited by mcarroll (2017-09-29 07:18:12)

Offline

#2 2017-09-29 13:39:26

jaroslav_sobota
Administrator
Registered: 2015-10-27
Posts: 535

Re: Help with DbDrv error - connecting to database

Hi Michael,
sorry to hear about your troubles. Something might be wrong with the ODBC connector configuration. What version of REX are you running on your Raspberry Pi?

RexCore -v

Can you please check the /etc/odbcinst.ini on both devices? Are they the same?

cat /etc/odbcinst.ini

Thanks,
Jaroslav

Offline

#3 2017-09-30 23:15:19

mcarroll
Member
From: Australia
Registered: 2017-02-14
Posts: 18

Re: Help with DbDrv error - connecting to database

Thanks for the quick reply Jaroslav, I'm away for the weekend, but, I'll get back to you ASAP when I'm back with my Pi's!

Offline

#4 2017-10-04 02:12:31

mcarroll
Member
From: Australia
Registered: 2017-02-14
Posts: 18

Re: Help with DbDrv error - connecting to database

Ok so working PI version:
RexCore version: 2.50.1 revision 7567
and /etc/odbcinst.ini:

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[MySQL]
Description=MySQL Driver
Driver=libmyodbc.so
Setup=libodbcmyS.so
UsageCount=1

[PostgreSQL]
Description=PostgreSQL Driver
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
UsageCount=1

[MSSQL]
Description=Microsoft SQL (FreeTDS) Driver
Driver=libtdsodbc.so
Setup=libtdsS.so
UsageCount=1

The Pi thats not working:
RexCore version: 2.50.5 revision 8614
and /etc/odbcinst.ini:

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[MySQL]
Description=MySQL Driver
Driver=libmyodbc.so
Setup=libodbcmyS.so
UsageCount=1

[PostgreSQL]
Description=PostgreSQL Driver
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
UsageCount=1

[MSSQL]
Description=Microsoft SQL (FreeTDS) Driver
Driver=libtdsodbc.so
Setup=libtdsS.so
UsageCount=1

Nothing is jumping out at me, other than the slight difference in versions of RexCore?

Anyway, thanks again in advance!

Offline

#5 2017-10-04 08:10:23

jaroslav_sobota
Administrator
Registered: 2015-10-27
Posts: 535

Re: Help with DbDrv error - connecting to database

Hi Michael,
thanks for the information. For a complete record, what version of REX development tools do you have on your PC? In RexDraw, go to Help->About RexDraw. I should have asked for this in my previous post, sorry for that.

How critical is this issue for you? How fast do you need it working?

Thanks,
Jaroslav

Offline

#6 2017-10-05 13:18:12

tomáš čechura
Administrator
Registered: 2016-02-22
Posts: 371

Re: Help with DbDrv error - connecting to database

Hi Michael,

please, can you send all your project files (including DB configuration file *.rio) so I can have a look at it? Post it here or send it to support(at)rexcontrols.com.

Thank you, Tomas

Offline

#7 2017-10-11 03:56:29

mcarroll
Member
From: Australia
Registered: 2017-02-14
Posts: 18

Re: Help with DbDrv error - connecting to database

Thanks Tomas and Jaroslav, I've emailed the files in a 7zip to the support email address.

I forgot to add my version of Rex on my PC is 2.50.1

Ideally I'd like to have a solution within the next week or so.  I realise the main holdup has been me wink

Michael

Offline

#8 2017-10-11 23:38:17

mcarroll
Member
From: Australia
Registered: 2017-02-14
Posts: 18

Re: Help with DbDrv error - connecting to database

Hmmm, I notice that the most recent version is 2.50.5.  What I might do (since I do have another fresh Pi handy) is uninstall and reinstall the latest version of the windows programmer and upload the config to the fresh Pi and see what happens.

Offline

#9 2017-10-12 05:08:01

mcarroll
Member
From: Australia
Registered: 2017-02-14
Posts: 18

Re: Help with DbDrv error - connecting to database

OK still no go, fresh install of stretch, fresh windows install and Rexcore v2.50.5 produces the following error:

DBDRV: connect to 'DRIVER=MySQL;SERVER=***.**.*.**;PORT=3306;DATABASE=n**;UID=root;PWD=**********;;' failed!!!! ([01000] [unixODBC][Driver Manager]Can't open lib 'MySQL' : file not found)

*I blanked out the actual IP and database name, as well as password as I copied this straight from RexView

EDIT:Since it looks like a MySQL error, I'll install that and make sure I can connect to the database from the command line, which I was able to do before, even with the errors in Rex.

Last edited by mcarroll (2017-10-12 05:09:44)

Offline

#10 2017-10-12 05:57:26

mcarroll
Member
From: Australia
Registered: 2017-02-14
Posts: 18

Re: Help with DbDrv error - connecting to database

I'm wondering if this is a Raspbian Stretch issue.  I'm going to load Jessie and see if I have the same issues.

EDIT:Just confirmed that the 2 previous Pis' (both the working and non-working one) were using Jessie, so I probably chased my tail a bit on that one, anyway I'll try a fresh install of Jessie on the fresh Pi and see how we go.

Last edited by mcarroll (2017-10-12 06:17:13)

Offline

#11 2017-10-13 05:58:10

mcarroll
Member
From: Australia
Registered: 2017-02-14
Posts: 18

Re: Help with DbDrv error - connecting to database

OK, so using a fresh install of Jessie seems to fix most of the problems.  I'm just having the following error, which is obviously complaining about the syntax in the RIO file, which I'm not why.

DBDRV: SQL error detail (code=1064, state='37000', string='[MySQL][ODBC 5.1 Driver][mysqld-5.5.52-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'siteId, sensorId, temp) VALUES (11816, 'top#50', 1)' at line 1')

I'll have an ask of my database-ing friend, but, I've posted it here, in case someone has an insight to what the cause may be.

Thanks
Michael

Offline

#12 2017-10-16 11:16:21

tomáš čechura
Administrator
Registered: 2016-02-22
Posts: 371

Re: Help with DbDrv error - connecting to database

Hi Michael,

it seems that you are missing first opening bracket in your SQL query. It should look like:

INSERT INTO rainfall (siteId, sensorId, temp) VALUES (11816, 'top#50', ?1);

Please, can you specify which combination of Raspbian image and REX Control System worked/did'n work for you? Just to see if the issue is on our site.

Thank you for cooperation.

Regards, Tomas

Offline

#13 2017-10-26 04:23:04

mcarroll
Member
From: Australia
Registered: 2017-02-14
Posts: 18

Re: Help with DbDrv error - connecting to database

Thanks for that spot Tomas, I've modified that file so much, I'm deleting things that I shouldn't sad

Anyway I've mostly got everything sorted with your help.  The only odd thing that is happening, is the system log is showing duplicate entry errors.  I looks like it's trying to send 2 entries with the exact same datetime stamp.  I've 'fixed' it by changing the line to

INSERT IGNORE

as the values are appearing in the database normally.  Also, when a 0 value is sent, the value the database shows a blank line after the entry, which seems a bit weird.

I'm going to smother the 0 value send anyway, as I'm not interested in a 0 value rainfall, only the actual rainfall values.  I'll check back in when I have this sorted out.

Michael.

Last edited by mcarroll (2017-10-26 04:24:03)

Offline

Board footer

Powered by FluxBB