Correct procedure to connect to a network database

I am making an application with qooxdoo, and I need to connect to a sqlite database. I am not able to.

A few years ago I made another application and I was able to connect, in that case it was mysql, perfectly.

I have a server networking using python and bottle, say at address.es:8080/idCars and I can request sql commands from it without problems. But now I need to connect to qooxdoo and I can't find the way.

What steps do I have to follow, if you can at least give me a working skeleton I would appreciate it.

I am an amateur, not a professional.

I have tried this but the server does not even receive a signal, in playground for test.

var req = new qx.io.request.Jsonp();
 req.setUrl("http://url:8080/getData");
 

req.send();

And run sql server, if go to link via web ok, but from qooxdoo no.

In sqlite server:

from bottle import run, Bottle
from bottle.ext import sqlite
from bottle import template
import json


app = Bottle()
plugin = sqlite.Plugin(dbfile='/home/__/manejo_db/id_Aves.sqlite')
app.install( plugin)

@app.route('/getData')
def show( db):
        salida= []
        row = db.execute('SELECT paxiarin, spacie_code FROM aves_identificadas group by paxiarin order by paxiarin;').fetchall()
        if row:
                for i in row:
                        sql= "SELECT count( spacie_code) FROM aves_identificadas where spacie_code= '%s'" % i[1]
                        conteo= db.execute( sql)
                        res= conteo.fetchone()
                        salida.append( [ i[ 0], res[ 0]])
#               output = template('plantilla', rows=salida)
#               print( json.dumps( salida))
                return json.dumps( { 'mensaje': "bien", 'resultados': salida})
        return HTTPError(404, "Page not found")

run( app, host='blabla.bla', port=8080)

Thanks.

Solution

There is problem with CORS. I think you have a response from the server. The simple client which works on port 8080:

const req = new qx.io.request.Xhr("http://localhost:8081/getData");
req.addListener("success", function(e) {
  const req = e.getTarget();
  console.log(req.getResponse());
}, this);
req.send();

Some fixes to your server:

from bottle import run, request, response, Bottle
from bottle.ext import sqlite
from bottle import template
import json


app = Bottle()
plugin = sqlite.Plugin(dbfile='/home/__/manejo_db/id_Aves.sqlite')
app.install( plugin)

@app.hook('after_request')
def enable_cors():
    """
    You need to add some headers to each request.
    Don't use the wildcard '*' for Access-Control-Allow-Origin in production.
    """
    response.headers['Access-Control-Allow-Origin'] = '*'
    response.headers['Access-Control-Allow-Methods'] = 'PUT, GET, POST, DELETE, OPTIONS'
    response.headers['Access-Control-Allow-Headers'] = 'Origin, Accept, Content-Type, X-Requested-With, X-CSRF-Token'

@app.route('/getData')
def show( db):
        salida= []
        row = db.execute('SELECT paxiarin, spacie_code FROM aves_identificadas group by paxiarin order by paxiarin;').fetchall()
        if row:
                for i in row:
                        sql= "SELECT count( spacie_code) FROM aves_identificadas where spacie_code= '%s'" % i[1]
                        conteo= db.execute( sql)
                        res= conteo.fetchone()
                        salida.append( [ i[ 0], res[ 0]])
#               output = template('plantilla', rows=salida)
#               print( json.dumps( salida))
                return json.dumps( { 'mensaje': "bien", 'resultados': salida})
        return HTTPError(404, "Page not found")

run( app, host='localhost', port=8081)

Notice the server works on a different port than your client which lead to CORS problem. If you have somehow the same origin for server and client there is no problem with CORS. This code works and tested. You may see logs on server side and your client qooxdoo page.