Whats the problem with this? if i enter wrong ID key error not showing up

Aquarius171

def searchFunction(self):

    self.query = QSqlQuery()
    self.query.prepare("select * from incoming_mac_records where ID = '%s'"%str(self.search.text()))
    self.query.addBindValue(self.search.text())

    if(not self.query.exec_()):
        print "Database query failed"
        return False
    if(self.query.next()):
        self.projectModel.setQuery("select * from incoming_mac_records where ID = '%s'"%str(self.search.text()),self.db)
        self.projectView = QTableView(self)
        self.projectView.setModel(self.projectModel)
        self.projectView.resizeColumnsToContents()
        self.projectView.setGeometry(250,10,940,680)
        return True

jsulm

@Aquarius171 said in Whats the problem with this? if i enter wrong ID key error not showing up:

self.query.addBindValue(self.search.text())

What error do you mean? If the ID is wrong the query will return an empty table - this is not an error.
Why do you have

self.query.addBindValue(self.search.text())

if you already inserted the ID in the line above?

Aquarius171

@jsulm the
if(not self.query.exec_()):
print "Database query failed"
return False

print statement is not showing up
do i need to remove the addBindValue?

Aquarius171

@jsulm for example ID 79 is not present on my database if i enter 79 i want the error message prints or prompt an error message saying ID not found

jsulm

@Aquarius171 Again: the query does not fail if it does not match anything. In SQL a SELECT statement returns 0..n lines. In your case, if the ID is not found you will get an empty result - there is no reason for the query to fail. If you want to know whether the ID was found use http://doc.qt.io/qt-5/qsqlquery.html#size
Yes, you can remove self.query.addBindValue(self.search.text())

the_

@Aquarius171 said in Whats the problem with this? if i enter wrong ID key error not showing up:

def searchFunction(self):

    self.query = QSqlQuery()
    self.query.prepare("select * from incoming_mac_records where ID = '%s'"%str(self.search.text()))
    self.query.addBindValue(self.search.text())

Thats complete nonsense and perfect for SQL injections
If you have a look at the QSqlQuery documentation you could see that for prepare and addBindValue() you need paceholders.

Aquarius171

@jsulm why the else is not working it is always True even if the ID doesn't exist

self.query = QSqlQuery()
self.query.prepare("select * from incoming_mac_records where ID = '%s'"%str(self.search.text()))

if(self.query.isActive):
print "Active"
else:
print "Not active"

the_

@Aquarius171

Where do you execute the query after preparing it?

Aquarius171

@the_ What do you mean?

the_

@Aquarius171

http://doc.qt.io/qt-5/qsqlquery.html#isActive

As you can see in the docs, a query is only active while executing. So you always will get "Not active" in your code sample.

And: did you have a look at the former link how to correctly use prepared statements?

Aquarius171

@the_

search = self.search.text()

    self.query = QSqlQuery()
    self.query.prepare("select * from incoming_mac_records where ID = '%s'"%str(search))
    self.query.addBindValue(search)

if(not self.query.exec_()):
print "Not active"
if(self.query.next()):
print "Active"

how about this?

the_

@Aquarius171

you did not read/understand how to use prepare correctly?


self.query = QSqlQuery()
self.query.prepare("select * from incoming_mac_records where ID=?")
self.query.addBindValue(self.search.text())
if not self.query.exec():
  print self.query.lastError()
else:
  while self.query.next():
    #do whatever you need to do

Aquarius171

@the_ why this is not showing when i enter wrong input

if not self.query.exec(): #sysntax error so i make it like this self.query.exec_()
print self.query.lastError()

Aquarius171

@the_ and i try to use .isActive() and same happens but not returning error message like print "record not found"

Aquarius171

@the_
search = self.search.text()

    self.query = QSqlQuery()
    self.query.prepare("select * from incoming_mac_records where ID =?")
    self.query.addBindValue(search)
    
    if not self.query.exec_():
        print self.query.lastError()
    else:
        while self.query.next():

if the ID key is active it show the print statement "active" but if not existing the self.query.lastError() not showing up

jsulm

@Aquarius171 I already told you two times that QSqlQuery::exec() does NOT fail if the id is not found! Do you actually read what others write?
exec() only fails if your query is invalid (wrong SQL syntax) or there is no database connection. If the id is not found exec() will not fail (this is now the third and last time I tell this, sorry, but you really should read answers) - it will execute successfully and the result will be an empty table. A SQL SELECT statement NEVER fails if it does not find anything, instead it returns an empty result.

Aquarius171

@jsulm for that thank you! and sorry :) GodBless sir ^_^ Have a nice day

jsulm

@Aquarius171 No problem! It's just that it is sometimes annoying if you repeat the same several times but the questioner does not read or ignore your explanation :-)
One tip: if you only need to know whether the ID is used use
SELECT count(*) FROM incoming_mac_records WHERE ID =?

Aquarius171

@jsulm how do i display count?

Aquarius171

@jsulm how to use isNull( int )?