[Done] DatabaseUpdate: Sqlite Vs MySQL

Just starting out? Need help? Post your questions and find answers here.
User avatar
PartTimeCoder
User
User
Posts: 14
Joined: Sat Jun 03, 2017 10:26 am

[Done] DatabaseUpdate: Sqlite Vs MySQL

Post by PartTimeCoder »

I have noticed an issue with DatabaseUpdate in which when used with MySQL it does not fill DatabaseError when it encounters a sql error, compared to Sqlite which behaves as documented.

some example code

Code: Select all

Procedure SQL_Update(db_id.i, db_query.s)
  If IsDatabase(db_id)
    If DatabaseUpdate(db_id,db_query) = 0 
      MessageRequester("SQL Error", DatabaseError(), #PB_MessageRequester_Error)
    EndIf
  EndIf
EndProcedure

Procedure MySQL_Connect(db_host.s, db_port.i, db_name.s, db_user.s, db_pass.s)
  
  db_id = OpenDatabase(#PB_Any, "host="+db_host+" port="+Str(db_port)+" dbname="+db_name, db_user, db_pass , #PB_Database_MySQL) 
  If db_id = 0
   MessageRequester("SQL Error", DatabaseError(), #PB_MessageRequester_Error)
  EndIf
  ProcedureReturn db_id
  
EndProcedure

Procedure SQLLite_Connect(db_name.s, db_user.s, db_pass.s)
  
   If CreateFile(0, db_name)
    CloseFile(0)
  EndIf
  
  db_id = OpenDatabase(#PB_Any, db_name, db_user, db_pass , #PB_Database_SQLite) 
  If db_id = 0
     MessageRequester("SQL Error", DatabaseError(), #PB_MessageRequester_Error)
  EndIf
  ProcedureReturn db_id
  
EndProcedure


#Use_Remote = 1

If #Use_Remote= 0
  UseSQLiteDatabase()
  db_name.s = "test_db"
  db_user.s = ""
  db_pass.s = ""
  db_id=SQLLite_Connect(db_name, db_user, db_pass)
ElseIf #Use_Remote = 1
  UseMySQLDatabase()
  db_host.s  = "127.0.0.1"
  db_port.i = 3306
  db_name.s = "test_db"
  db_user.s = "root"
  db_pass.s = ""
  db_id=MySQL_Connect(db_host, db_port, db_name, db_user, db_pass)
EndIf

SQL_Update(db_id, "DROP TABLE IF EXISTS food")
SQL_Update(db_id, "CREATE TABLE food (name VARCHAR(50), weight INT)")

; error, table already exists
SQL_Update(db_id, "CREATE TABLE food (name VARCHAR(50), weight INT)")
; error, bad sql
SQL_Update(db_id, "CREATEtypo TABLE food (name VARCHAR(50), weight INT)")

Is this a bug?
Fred
Administrator
Administrator
Posts: 16623
Joined: Fri May 17, 2002 4:39 pm
Location: France
Contact:

Re: DatabaseUpdate: Sqlite Vs MySQL

Post by Fred »

Yes it was. Fixed.
User avatar
PartTimeCoder
User
User
Posts: 14
Joined: Sat Jun 03, 2017 10:26 am

Re: [Done] DatabaseUpdate: Sqlite Vs MySQL

Post by PartTimeCoder »

Please see the below thread for possible related bug

https://www.purebasic.fr/english/search ... 3&sr=posts
Post Reply