2

I want to use a sqlite3 database at my ESP8266 NodeMCU microproessor. The firmware is:

NodeMCU custom build by frightanic.com
branch: master
commit: 67027c0d05f7e8d1b97104e05a3715f6ebc8d07f
SSL: false
modules: bit,bme280,cron,dht,file,gpio,i2c,net,node,rfswitch,rtctime,sntp,sqlite3,tmr,uart,wifi
build created on 2018-04-16 16:06
powered by Lua 5.1.4 on SDK 2.2.1(cfd48f3)

The (main part) of the code I use is: (The complete code follows later at this post)

-- Wenn Tabelle nicht exisiert, ohne Daten anlegen
sql = "CREATE TABLE IF NOT EXISTS '"..tabelle.."' ('"..lokal.."' number,'"..sntp.."' number)"
status = db:exec(sql)
print(sql)
print("Status Anlage '"..tabelle.."': ",status)

The log is:

> -- drop_sntptab()
> existenzsicherung_sntptab()
E:M 4272
CREATE TABLE IF NOT EXISTS 'abgleich' ('lsync' number,'ssync' number)
Status Anlage 'abgleich':   7
SELECT count(*) FROM 'abgleich'
no such table: abgleich

You see, the table is not created althougt the SQL-Statement seems to be right. The opening command is:

db = sqlite3.open(database)

Find following the complete Script:

database = 'sntp.db'
tabelle = 'abgleich'
lokal = 'lsync'
sntp= 'ssync'
l0 = 1514764800  -- Epoche-Time 01.01.2018 00:00:00
s0 = 1514764800  -- Epoche-Time 01.01.2018 00:00:00



function open_db()
  -- Datenbankdatei allokieren
  db = sqlite3.open(database)
end


function close_db()
  status = db:close() 
  print("Status DB-schliessen: ", status)
end


function delete_sntp_database()
  -- Datenbankdatei wird geloescht
  file.remove(database)
end


function drop_sntptab()
  --- Tabelle loeschen
  sql = "DROP TABLE '"..tabelle.."';"
  status = db:exec(sql)
  print(sql)
  print("Status DROP "..tabelle..": ",status)
end


function existenzsicherung_sntptab()
  -- Wenn Tabelle nicht exisiert, ohne Daten anlegen
  sql = "CREATE TABLE IF NOT EXISTS '"..tabelle.."' ('"..lokal.."' number,'"..sntp.."' number)"
  status = db:exec(sql)
  print(sql)
  print("Status Anlage '"..tabelle.."': ",status)

  -- Anzahl der Datenaetze ermitteln
  sql = "SELECT count(*) FROM '"..tabelle.."'"
  print(sql)
  for tmp in db:urows(sql) 
    -- Anzahl Datensätze
  do 
    anzahl = tmp
  end
  print('Anzahl Datensaetze: ',anzahl)
  -- Wenn Anzahl der Datensätze 0 ist, wird eine Datenzeile eingefügt
  if anzahl == 0
  then
    sql = "INSERT INTO '"..tabelle.."' ('"..lokal.."', '"..sntp.."') values ("..l0..","..s0..")"
    status = db:exec(sql)
    print(sql)
    print("Status Eingabe Feldwerte: ",status)
  end
  -- Ausgabe des Tabelleninhalts
  -- urows ermöglicht die Zeilenweise Ausgabe des Select - Ergebnis
  sql = "SELECT * FROM '"..tabelle.."'"
  for lokal,sntp in db:urows(sql)
  do 
    print(lokal,sntp) 
  end
end


function ermittel_lastlokal()
  sql = "SELECT * FROM '"..tabelle.."' WHERE _ROWID_ = 1;"
  for v1,v2 in db:urows(sql)
  do 
    print("Ergebniszeile(n): ",v1,v2) 
    lastlokal = v1
    lastsntp = v2
  end
end

-- delete_sntp_database()
open_db()
-- drop_sntptab()
existenzsicherung_sntptab()
-- ermittel_lastlokal()
close_db()

Can anyone see my mistake?

Thank you very much in advance

Regards Kleinlaut

Kleinlaut
  • 21
  • 2
  • `CREATE IF NOT EXISTS` doesn't happen to be one of the many SQLite features that was stripped for this port? -> https://nodemcu.readthedocs.io/en/latest/en/modules/sqlite3/ – Marcel Stör Apr 24 '18 at 11:59
  • Hello, thank you for your comment. This seems not to be the problem. I deleted the databasefile and restartet without the IF EXISTS clause. It still does not work. – Kleinlaut Apr 24 '18 at 17:33
  • Well, then I guess you should start trimming your code down to a Minimal, Complete, and Verifiable example (shortest possible code to demonstrate error). – Marcel Stör Apr 24 '18 at 17:55
  • Thank you, you're right. Since there seems not to be a clear and easy way to find the error, you point out the right way. I'll do next day's. – Kleinlaut Apr 26 '18 at 11:06

0 Answers0