I’m creating a web app and find myself running into something strange with a VIEW in MariaDB.
I defined a view in my database consisting of 2 tables connected by a join statement. The view definitely has records. Selecting the rows of the view works perfectly fine in debug mode with my local DB. The database on my server is exactly the same as my local DB, but selecting rows from the view on the server does not return a single row.
I use Xojo 19.2
my app is deployed as standalone on a Linux Server
I do no complicated SQL stuff just (SELECT * FROM myview)
Actually it is my first view with Xojo and MariaDB and I hope someone can give me a hint, what I’m doing wrong. Thank you all very much in advance.
SELECT *
FROM vertrag
LEFT JOIN patient
ON vertrag.VER_patientId = patient.PAT_Id
ORDER BY PAT_name ASC, PAT_vorname ASC, PAT_id ASC, VER_id
I only store the patientId in my vertrag table. The aim is to get the name and address information of patient in my view without storing it in the vertrag table. I also tried to simplify the SQL statement by reducing it to “SELECT * from viewvertraege” (without where clause) or defined my view simply as “SELECT * FROM vertrag” (without JOIN). But this did not help. All SQL statements I used so far worked on real tables in the same DB.
The relative source code snippet is:
[code]Var resultSet As RowSet
Var sqlSelect As String
Var sqlFrom As String
Var sqlWhere As String
Var sqlOrderBy As String
//
// SQL Statement
//
sqlSelect = "SELECT * "
sqlFrom = "FROM viewvertraege "
sqlWhere = "WHERE PAT_zentrumNummer = ? "
sqlOrderBy = “ORDER BY PAT_name ASC, PAT_vorname ASC, PAT_plz ASC, PAT_id ASC ;”
//
//
Var psZentrum As PreparedSQLStatement = Session.sessionDB.Prepare(sqlSelect + " " + sqlFrom + " " + sqlWhere + " " + sqlOrderBy)
’
psZentrum.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_LONG)
psZentrum.Bind(0, zentrumNummer)
If Session.sessionDatabaseIsConnected Then
Try
resultSet = psZentrum.SelectSQL
Return resultSet
Catch error As DatabaseException
MessageBox("Datenbankfehler: " + error.Message)
Return Nil
End Try
End If[/code]
As this works when debugging the code can’t be wrong. Either sessionDatabaseIsConnected is not true or the view on the production server is not exactly the same.
Thank you for your kind support. I couldn’t figure out what the difference really is about. But since everything works fine with real tables on the production server I decided not to insist on using views and adjusted my sql statement as follows: