Getting the SQL string which defines a view using C++ QSql

PMime

I am just using QSql with C++ to work with a MS Access database. This works so far.

The problem is that i can not get the original SQL string when I have created a view/query. The Access table MSysQueries contains some information but is somehow cryptic.

Code snippets are welcome, wether QSql C++ or pure SQL.

Thank you in advance!

artwaw

@PMime How do you construct the queries? Please show your code.

PMime

@artwaw Here ist an (experimetal) code snippet:

QSqlQuery oQuery(m_oQtDb);
bool bSuccess = oQuery.exec("DROP TABLE FreeSQLView");
if (!bSuccess)
	qDebug() << oQuery.lastError();
bSuccess = oQuery.exec("CREATE VIEW FreeSQLView AS SELECT * FROM User");
if (!bSuccess)
	qDebug() << oQuery.lastError();
qDebug() << m_oQtDb.tables(QSql::Views);

artwaw

@PMime So in order to get last query from QSqlQuery one should use QSqlQuery::lastQuery() - what does that return for you?

PMime

@artwaw When I use lastQuery() in my code snippet, the output of this function is:

DROP TABLE FreeSQLView
CREATE VIEW FreeSQLView AS SELECT * FROM User

artwaw

@PMime Isn't this what you were asking for? Or have I misunderstood your problem?

PMime

@artwaw I basically want to get the sql string for every view in a database. This database does not have necessarily to be from myself.

artwaw

@PMime That, I believe, should be accessible via driver. You mentioned documentation in the first post?

PMime

@artwaw There is the system table MSysQueries in MS Access. Unfortunately it is difficult to get the sql string from this table.

The following link explains the structure of mSysQueries: [https://stackoverflow.com/questions/17329223/what-does-the-data-in-msysqueries-mean]

artwaw

@PMime Under that link (and links that follow) it is quite well described how to handle queries towards MSysQueriers - you should be able to do that with QSqlQuery without a problem.

Also, I believe MSysQueries is considered system table so call to QSqlDatabase::tables() should take a parameter of QSql::SystemTables().

Out of curiosity: does qDebug() << m_oQtDb.tables(QSql::Views); return anything useful for you? I don't have access db to try out myself here.

PMime

@artwaw I use qDebug() << m_oQtDb.tables(QSql::Views); because I want to test if the list of views is complete. By views I mean a virtual table based on the result set of an SQL statement.

It is that SQL statement I want to obtain.

Other database systems like Oracle have system tables too. But they present the SQL statement much more easier, see SYS.VIEWS: https://docs.oracle.com/database/timesten-18.1/TTSYS/systemtables.htm#TTSYS390

In MSysQueries you have to build the sql string by yourself which is complicated, for example when UNION is involved.

ChrisW67

I think you are expecting to obtain the equivalent of:

create view blah as
select columns...
from table1  a
join table2 b
on b.a_pk = a.pk
where conditions...
group by r
having stuff
order by x, y, z

for each view with a single query returning the text. That is not going to happen with Access (based on the information accessible through the the pages you linked).

It looks like you need to query the two system tables for the view of interest, ordering by attribute number and "order", and iterate this set building a query as you go. You are correct, this will be difficult to get right.

Access does this for you in its UI. Have you considered automating Access to extract the information? It may be possible... I cannot say, having consigned Access to the pits of hell from whence it came.

jsulm

@PMime Sounds like "describe" statement is what you need, see https://www.geeksforgeeks.org/sql-describe-statement/