database metadata

Databases
1

Please. Who can give me an example of a Xojo code that shows the tables of a database (mysql, sqlserver, oracle, postgres) and information of each relationship: pk table name, pk name, fk table name, fk name.
Thanks.

2

dont know about one … had to do this job myself for postgres and sqlite, it was quite huge work.
to be complete you also need the triggers informations, and the uniqueness of indexes
that does not appear in xojo tableschema or fieldschema methods.

3

Database.TableSchema returns a RecordSet with the names of the tables in the database.
http://documentation.xojo.com/index.php/Database.TableSchema

Database.FieldSchema returns information about a specific table.
http://documentation.xojo.com/index.php/Database.FieldSchema

If those don’t have what you need, then you’ll need to do DB-specific queries to get additional information. For example, MS SQL Server has Information Schema views with lots of information that you can query.

4

I Use a class for use many database type and use xml for config.
See this
https://forum.xojo.com/38620-use-4-database-type-in-only-one-class

for read data :

dim sql as string
dim rsitem as RecordSet
				
dim db as new DBKoala(procedure.dbdatabasetype)
db.DatabaseName = procedure.dbname
db.Host = procedure.dbhost // vediamo per ODBC
db.Port = procedure.dbport
db.Username = procedure.dbusername
db.Password = procedure.dbpassword
				
If Not db.Connect() then
	msgbox(KoalaErp.msg_errore_aperturadatabase + ": " + db.errormessage)
else
	if procedure.dbdatabasetype = "MySQL" then db.SQLExecute("SET NAMES utf8 COLLATE utf8_general_ci; SET CHARACTER SET utf8")
	sql = "SELECT * from valute where rowid = " + idrecord
	rsitem = db.SQLSelect(sql)
	if db.error then
		msgbox(KoalaErp.msg_errore_aperturatabella + db.errorMessage)
	else
		' compilo i dati della form
		txtcodice.Text = rsitem.field("codice").utfValue
		txtcodice.ReadOnly = true
		txtdescrizione.Text = rsitem.field("descrizione").utfValue
		txtsimbolo.Text = rsitem.field("segno").utfValue
		txtdecimali.text = rsitem.field("decimali").utfValue
		lbrowid.Text = rsitem.Field("rowid").utfValue
		self.idrecord = rsitem.Field("rowid").IntegerValue							
	end if
end if

for update record or insert a new one:

dim db as new DBKoala(procedure.dbdatabasetype)
db.DatabaseName = procedure.dbname
db.Host = procedure.dbhost // vediamo per ODBC
db.Port = procedure.dbport
db.Username = procedure.dbusername
db.Password = procedure.dbpassword
		
If Not db.Connect() then
	msgbox(KoalaErp.msg_errore_aperturadatabase + ": " + db.errormessage)
else
	if procedure.dbdatabasetype = "MySQL" then db.SQLExecute("SET NAMES utf8 COLLATE utf8_general_ci; SET CHARACTER SET utf8")
	Dim sql As String
        sql = "SELECT * from report WHERE (azienda = '" + procedure.codiceazienda + "' OR azienda = '9999')"
	dim rsitem as RecordSet
	rsitem = db.SQLSelect(sql)
	if db.error then
		msgbox("Errore durante la lettura della tabella : " + db.errorMessage)
	else
rsitem.edit
	rsitem.Field("azienda").UTF8Value = self.azienda
	rsitem.field("nomestampa").UTF8Value = txtnomestampa.testo
	rsitem.field("descrizione").UTF8Value = txtdescrizione.testo
	rsitem.field("sqlselect").UTF8Value = txtselect.text.nosmartquotes
	rsitem.Field("nomeutentemodifica").UTF8Value = procedure.utente
	rsitem.Field("datamodifica").DateValue = now()
	rsitem.Update
	if db.error then
		msgbox("errore durante la modifica del record nella tabella. Controllare i dati inseriti. Codice errore : " + db.errorMessage )
		exit sub
	end if
	db.Commit()
	        if db.error then
		         msgbox("errore durante la modifica del record nella tabella. Codice errore : " + db.errorMessage )
		         exit sub
		else
		          boxalert.messaggio("dati aggiornati nella tabella")
		end if
end if

or for write a record :

dim row as DatabaseRecord
row = new DatabaseRecord
row.column("nomestampa") = txtnomestampa.testo.UTF8return
row.Column("azienda") = procedure.codiceazienda.UTF8return
row.Column("descrizione") = txtdescrizione.testo.UTF8return
row.Column("sqlselect") = txtselect.text.nosmartquotes.UTF8return
db.InsertRecord "report", row
if db.error then
        msgbox("errore nell'inserimento del nuovo record nella tabella. Codice errore : " + db.errorMessage )
	exit sub
else
	db.Commit()
	boxalert.messaggio("dati inseriti nella tabella")
end if

for delete:

dim db as new DBKoala(procedure.dbdatabasetype) // MY DB CLASS
db.DatabaseName = procedure.dbname
db.Host = procedure.dbhost // vediamo per ODBC
db.Port = procedure.dbport
db.Username = procedure.dbusername
db.Password = procedure.dbpassword
db.SQLExecute ("delete from valute where rowid = " + str(self.idrecord))
If db.error then
      MsgBox db.errorMessage
else
      db.Commit //save deletion
      MsgBox("Valuta " + str(self.idrecord) + " eliminato.")
end if