SQLITE with Octave

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLITE with Octave

FMourad
Hello everybody,

I have browsed all the topics related to the use of sqlite on octave without finding a clear solution to my situlation.
According to the following octave wiki : http://wiki.octave.org/Database_package , two github repositories can be exploited as sqlite wrappers on octave, but my attemps to use these projects were unsuccessful.
These are the links to the repositories :
https://github.com/rmartinjak/mex-sqlite3
https://github.com/Andy1978/octave-sqlite

This is my attempt with the first repository :
- I dowloaded the Sqlite API from the website : https://sqlite.org/2017/sqlite-amalgamation-3180000.zip
- I cloned the mex-sqlite3 master branch last commit
- the sqlite.h file is not included into the github repository, I added it from the dowloaded sqlite source
- I tried to compile the first repository files using mkoctfile option as following :
mkoctfile --mex sqlite.c .
The command finished with an error code because any of the sqlite c api functions was redefined in the sqlite3.c file provided by the github repository (undefined reference to{sqlite3_bind_text;sqlite3_bind_double;sqlite3_bind_int64 ...}

The second repository requires autotools to compile the projects, which are not possible to install in my case since my development environment is strictly limited.

It would be helpful if someone could detail how to exploit the mex-sqlite3 repository or has an alternative solution that can solve the problem.

Thanks,
Mourad FAKHFAKH
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE with Octave

Przemek Klosowski-7
On 05/10/2017 11:50 AM, FMourad wrote:
Hello everybody, 

I have browsed all the topics related to the use of sqlite on octave without
finding a clear solution to my situlation. 
According to the following octave wiki :
http://wiki.octave.org/Database_package , two github repositories can be
exploited as sqlite wrappers on octave, but my attemps to use these projects
were unsuccessful. 
These are the links to the repositories : 
•https://github.com/rmartinjak/mex-sqlite3https://github.com/Andy1978/octave-sqlite
Well, git log shows that the first one was last worked on in 2014 and the second one in 2016. Neither seems to compile at the moment, but the second one seems a better bet, just because it IS a little more recent, and it currently has a silly fail on a test code:

cl_sqlite3_handler.cc: In member function ‘void sqlite3_handler::testme()’:
cl_sqlite3_handler.cc:198:24: error: no matching function for call to ‘Matrix::assign(int, int, int, int)’
   a.assign (5,5,123,666)

I just commented this line out and the code seems to compile and work on its own tests, but crashes on my simple database.

So, two issues:

1) compilation: in the a.assign() code above, how should the assigned params be passed? The most promising match seems to be :

usr/include/octave-4.0.3/octave/../octave/Array.h:545:8: note: candidate: void Array<T>::assign(const idx_vector&, const idx_vector&, const Array<T>&, const T&) [with T = double]
   void assign (const idx_vector& i, const idx_vector& j, const Array<T>& rhs,
        ^~~~~~
/usr/include/octave-4.0.3/octave/../octave/Array.h:545:8: note:   no known conversion for argument 3 from ‘int’ to ‘const Array<double>&’

2) I get a runtime crash on a simple database whose schema is  CREATE TABLE t (time,tempc);

octave:2> a = sqlite3 ("temp.db");
sqlite3_handler::open temp.db create=0
sqlite3_handler C'Tor
sqlite3_handler C'Tor
octave:3> exec_sql (a, "SELECT * from t;")

sqlite3_handler::exec_sql sql=SELECT * from t;
numel per bind param = 0
sqlite3_prepare_v2 returned SQLITE_OK
SQLITE_ROW sqlite3_column_count()=2
SQLITE_ROW sqlite3_data_count()=2
column 0 has type code 3
column 1 has type code 1
terminate called after throwing an instance of 'std::logic_error'
  what():  basic_string::_M_construct null not valid
panic: Aborted -- stopping myself...
attempting to save variables to 'octave-workspace'...
error: octave_base_value::save_binary(): wrong type argument 'sqlite3_handler'
save to 'octave-workspace' complete
Aborted (core dumped)


_______________________________________________
Help-octave mailing list
[hidden email]
https://lists.gnu.org/mailman/listinfo/help-octave
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE with Octave

Andreas Weber-6
Am 10.05.2017 um 20:53 schrieb Przemek Klosowski:

> On 05/10/2017 11:50 AM, FMourad wrote:
>> Hello everybody,
>>
>> I have browsed all the topics related to the use of sqlite on octave without
>> finding a clear solution to my situlation.
>> According to the following octave wiki :
>> http://wiki.octave.org/Database_package , two github repositories can be
>> exploited as sqlite wrappers on octave, but my attemps to use these projects
>> were unsuccessful.
>> These are the links to the repositories :
>> •https://github.com/rmartinjak/mex-sqlite3
>> •https://github.com/Andy1978/octave-sqlite
> Well, git log shows that the first one was last worked on in 2014 and
> the second one in 2016. Neither seems to compile at the moment, but the
> second one seems a better bet, just because it IS a little more recent,
> and it currently has a silly fail on a test code:
>
> cl_sqlite3_handler.cc: In member function ‘void sqlite3_handler::testme()’:
> cl_sqlite3_handler.cc:198:24: error: no matching function for call to
> ‘Matrix::assign(int, int, int, int)’
>    a.assign (5,5,123,666)
>
> I just commented this line out and the code seems to compile and work on
> its own tests, but crashes on my simple database.

The second repo is mine and it's still in a very early stage and not
ment to solve any purpose yet. Tis is the reason there isn't even a
README. Perhaps I should add a big fat warning.

-- Andy

_______________________________________________
Help-octave mailing list
[hidden email]
https://lists.gnu.org/mailman/listinfo/help-octave
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE with Octave

Andreas Weber-6
In reply to this post by FMourad
Am 10.05.2017 um 17:50 schrieb FMourad:
> I have browsed all the topics related to the use of sqlite on octave without
> finding a clear solution to my situlation.
> According to the following octave wiki :
> http://wiki.octave.org/Database_package , two github repositories can be
> exploited as sqlite wrappers on octave, but my attemps to use these projects
> were unsuccessful.
> These are the links to the repositories :
> •https://github.com/rmartinjak/mex-sqlite3
> •https://github.com/Andy1978/octave-sqlite

The second repo linked is mine and is in an very early stage and not
useful at the moment. Perhaps I should add a big fat warning about that.

The main reason that I stopped development was that markuman pointed me
to the mex wrapper (your first linked repo) and I thought reinventing
the weel would be a bad idea.


> - I dowloaded the Sqlite API from the website :
> https://sqlite.org/2017/sqlite-amalgamation-3180000.zip
> - I cloned the mex-sqlite3 master branch last commit
> - the sqlite.h file is not included into the github repository, I added it
> from the dowloaded sqlite source
> - I tried to compile the first repository files using mkoctfile option as
> following :
> mkoctfile --mex sqlite.c .
> The command finished with an error code because any of the sqlite c api
> functions was redefined in the sqlite3.c file provided by the github
> repository (undefined reference
> to{sqlite3_bind_text;sqlite3_bind_double;sqlite3_bind_int64 ...}

What is your OS? You didn't mentioned it so far.
-- Andy

_______________________________________________
Help-octave mailing list
[hidden email]
https://lists.gnu.org/mailman/listinfo/help-octave
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE with Octave

Przemek Klosowski-7
On 05/10/2017 04:02 PM, Andreas Weber wrote:
Am 10.05.2017 um 17:50 schrieb FMourad:
I have browsed all the topics related to the use of sqlite on octave without
finding a clear solution to my situlation. 
According to the following octave wiki :
http://wiki.octave.org/Database_package , two github repositories can be
exploited as sqlite wrappers on octave, but my attemps to use these projects
were unsuccessful. 
These are the links to the repositories : 
•https://github.com/rmartinjak/mex-sqlite3https://github.com/Andy1978/octave-sqlite
The second repo linked is mine and is in an very early stage and not
useful at the moment. Perhaps I should add a big fat warning about that.

The main reason that I stopped development was that markuman pointed me
to the mex wrapper (your first linked repo) and I thought reinventing
the weel would be a bad idea.
I see--and I realized that the problem with the MEX-sqlite3 was very superficial.
The provided Makefile does not link all source files---it leaves out structlist.c.
The following builds an extension that works with my Octave  4.0.3 on Linux:
 
mkoctfile --mex -lsqlite3 -o sqlite3.mex sqlite3.c structlist.c

it not only passes its tests but also works with my small database.

One conceptual problem I have is that it returns the data as an array of structs (one per row); e.g. for my simple temperature data,

a(1:5).time,a(1:5).tempc
ans = 2016-04-08 17:30
ans = 2016-04-08 17:45
ans = 2016-04-08 18:00
ans = 2016-04-08 18:15
ans = 2016-04-08 18:30
ans = 35
ans = 35
ans = 36
ans = 36
ans = 36

What's the easiest way to return the entire column of data, i.e. collate a given struct field from every array member into a vector?



_______________________________________________
Help-octave mailing list
[hidden email]
https://lists.gnu.org/mailman/listinfo/help-octave
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE with Octave

Przemek Klosowski-7
On 05/10/2017 04:44 PM, Przemek Klosowski wrote:
One conceptual problem I have is that it returns the data as an array of structs (one per row); e.g. for my simple temperature data,

a(1:2).time,a(1:2).tempc
ans = 2016-04-08 17:30
ans = 2016-04-08 17:45
ans = 35
ans = 36

What's the easiest way to return the entire column of data, i.e. collate a given struct field from every array member into a vector?

Never mind, it's of course a comma-separated list so the following works:

a=sqlite3("temp.db",'select julianday(time)as d,tempc as t from t')
plot([a.d],[a.t])

One word of caution: I had to rename the columns because the code silently fails for fancy SQL expressions such as julianday(time), probably because it's an invalid struct field name.


_______________________________________________
Help-octave mailing list
[hidden email]
https://lists.gnu.org/mailman/listinfo/help-octave
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE with Octave

FMourad
In reply to this post by Przemek Klosowski-7
Przemek Klosowski-7,
Thank you for your reply, I could compile the mex file with the instruction as stated in your post.
I could successfully create my database and my tables .
However I still couldn't do two actions :
- imposing the foreign key constraints:
Eventhough two of my tables are linked with a foreign key, I could transgress the foreign key constraint.
I tried activating the foreign key constraint with the following command :
sqlite3(database_name,'PRAGMA foreign_keys = ON;') ;
No error message was returned, but it didn't change anything.

- Is there a way to close properly the database with this API ?
I tried something like sqlite3(database_name,'.exit') but the synthax wasn't correct .

Thanks for any help,
Mourad FAKHFAKH