www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - SQLite

reply Alfred Newman <alfredonewman gmail.com> writes:
Hello,

I am trying to handle a SQLite3 table with D. During my 
researchs, I discovered the lib 
https://dlang.org/phobos/etc_c_sqlite3.html.

However, for any reason, there is no code snippets or sample 
codes available there. So, I am stucked.

I have the following sample structure table:
    sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
          "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
          "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
          "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
          "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
          "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
          "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
          "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

Can you pls provide a code snippet or some hints to the following 
job:
- Create a table with the layout above
- Iterate through the records given a basic SELECT WHERE Query

Thanks in advance, AN
Oct 19 2016
next sibling parent bachmeier <no spam.net> writes:
On Wednesday, 19 October 2016 at 16:01:37 UTC, Alfred Newman 
wrote:
 Hello,

 I am trying to handle a SQLite3 table with D. During my 
 researchs, I discovered the lib 
 https://dlang.org/phobos/etc_c_sqlite3.html.

 [...]
I've never used SQLite from D, but Adam Ruppe has an interface with an example here: https://github.com/adamdruppe/arsd/blob/master/sqlite.d
Oct 19 2016
prev sibling next sibling parent WebFreak001 <janju007 web.de> writes:
On Wednesday, 19 October 2016 at 16:01:37 UTC, Alfred Newman 
wrote:
 Hello,

 I am trying to handle a SQLite3 table with D. During my 
 researchs, I discovered the lib 
 https://dlang.org/phobos/etc_c_sqlite3.html.

 However, for any reason, there is no code snippets or sample 
 codes available there. So, I am stucked.
etc.c.sqlite3 is just a C wrapper, take a look at C examples for sqlite3. The D docs don't usually include documentation for simple C wrappers. If you want a proper D library for sqlite3, try d2sqlite3 from dub. IMO it's really easy to use and works really well. Your example using d2sqlite3: auto stmt = db.prepare("INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (:id, :name, :age, :address, :salary)"); stmt.inject(1, "Paul", 32, "California", 20000); stmt.inject(2, "Allen", 25, "Texas", 15000); stmt.inject(3, "Teddy", 23, "Norway", 20000); stmt.inject(4, "Mark", 25, "Rich-Mond", 65000);
Oct 19 2016
prev sibling parent reply Vadim Lopatin <coolreader.org gmail.com> writes:
On Wednesday, 19 October 2016 at 16:01:37 UTC, Alfred Newman 
wrote:
 Hello,

 I am trying to handle a SQLite3 table with D. During my 
 researchs, I discovered the lib 
 https://dlang.org/phobos/etc_c_sqlite3.html.

 However, for any reason, there is no code snippets or sample 
 codes available there. So, I am stucked.

 I have the following sample structure table:
    sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
          "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
          "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
          "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
          "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
          "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
          "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
          "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

 Can you pls provide a code snippet or some hints to the 
 following job:
 - Create a table with the layout above
 - Iterate through the records given a basic SELECT WHERE Query

 Thanks in advance, AN
Snippet how to do it using DDBC library https://github.com/buggins/ddbc import ddbc; string url = "sqlite:testdb.sqlite"; // creating Connection auto conn = createConnection(url); scope(exit) conn.close(); // creating Statement auto stmt = conn.createStatement(); scope(exit) stmt.close(); // execute simple queries to create and fill table stmt.executeUpdate("CREATE TABLE COMPANY (ID int, NAME varchar, AGE int,ADDRESS varchar, SALARY double)"); string[] statements = [ "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )", "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 )", "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )" ]; foreach(sql; statements) stmt.executeUpdate(sql);
Oct 21 2016
parent Alfred Newman <alfredonewman gmail.com> writes:
On Friday, 21 October 2016 at 10:50:30 UTC, Vadim Lopatin wrote:
 On Wednesday, 19 October 2016 at 16:01:37 UTC, Alfred Newman 
 wrote:
 Hello,

 I am trying to handle a SQLite3 table with D. During my 
 researchs, I discovered the lib 
 https://dlang.org/phobos/etc_c_sqlite3.html.

 However, for any reason, there is no code snippets or sample 
 codes available there. So, I am stucked.

 I have the following sample structure table:
    sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
          "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
          "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
          "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
          "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
          "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
          "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
          "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

 Can you pls provide a code snippet or some hints to the 
 following job:
 - Create a table with the layout above
 - Iterate through the records given a basic SELECT WHERE Query

 Thanks in advance, AN
Snippet how to do it using DDBC library https://github.com/buggins/ddbc import ddbc; string url = "sqlite:testdb.sqlite"; // creating Connection auto conn = createConnection(url); scope(exit) conn.close(); // creating Statement auto stmt = conn.createStatement(); scope(exit) stmt.close(); // execute simple queries to create and fill table stmt.executeUpdate("CREATE TABLE COMPANY (ID int, NAME varchar, AGE int,ADDRESS varchar, SALARY double)"); string[] statements = [ "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )", "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 )", "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )" ]; foreach(sql; statements) stmt.executeUpdate(sql);
Hello Vadim, I used dub to register DDBC. After that, I tried to do some tests, but I received the following error: Fetching ddbc ~master... Placing ddbc ~master to C:\Users\Alfred\AppData\Roaming\dub\packages\... Performing "release" build using dmd for x86. ddbc ~master: building configuration "full"... Copying files for ddbc... compiling C:\...\Projects\stingray.d OPTLINK (R) for Win32 Release 8.00.17 Copyright (C) Digital Mars 1989-2013 All rights reserved. http://www.digitalmars.com/ctg/optlink.html sqlite3.lib Warning 2: File Not Found sqlite3.lib C:\Users\Alfred\AppData\Roaming\dub\packages\ddbc-master\ddbc\lib\ddbc.lib(mysqlddbc) Error 42: Symbol Undefined _D5mysql10connection12__ModuleInfoZ C:\Users\Alfred\AppData\Roaming\dub\packages\ddbc-master\ddbc\lib\ddbc.lib(pgsqlddbc) Error 42: Symbol Undefined _D8derelict2pq2pq12__ModuleInfoZ C:\Users\Alfred\AppData\Roaming\dub\packages\ddbc-master\ddbc\lib\ddbc.lib(sqliteddbc) Error 42: Symbol Undefined _sqlite3_data_count ... _D5mysql8protocol8commands7Command11__xopEqualsFKxS5mysql8protocol8commands7CommandKxS5mysql8protocol8commands7CommandZb --- errorlevel 67 error: the process (dmd) has returned the signal 67 C:\...\Projects\stingray.d has not been compiled Notice that sqlite3.lib is located at my folder C:\Users\Alfred\AppData\Roaming\dub\packages\ddbc-master\ddbc\libs\win64 What am I doing wrong ? Cheers
Nov 01 2016