I am using Postgres. Is this maybe not supported for that DB? No error, the expected columns are there, just no rows. And yes I am certain I’m using the correct table name; it is copied / pasted from a working query elsewhere, and includes the schema. In any case if the name were wrong it should throw an error.
Here’s the method code, where tableName is the sole string argument. I’m just trying to get a list of field names into a tool tip for a textbox that I type SQL fragments into.
If App.DBConnection.Connect() = False Then
Return “”
End If
var rs As RowSet = App.DBConnection.TableColumns(tableName)
var tipText As String = "Table: " + tableName
While rs.AfterLastRow = False
tipText = tipText + text.EndOfLine + rs.Column(“ColumnName”).StringValue
rs.MoveToNextRow()
Wend
rs.Close()
App.DBConnection.Close()
Return tipText
I did find a workaround in the meantime, but I am just wondering if this feature is broken, broken for Postgres, or I’m Just Not Doing it Right.
The workaround (only works if you have sufficient admin rights, I suppose, but this is an internal app, and I do have that access):
If App.DBConnection.Connect() = False Then
Return “”
End If
var tipText As String = “Table: " + tableName
var schemaName As String = “”
var dot As Integer = tableName.IndexOf(”.", ComparisonOptions.CaseSensitive)
If dot > -1 Then
schemaName = tableName.Left(dot)
tableName = tableName.Middle(dot + 1)
End If
var rs As RowSet = App.DBConnection.SelectSQL(“SELECT column_name FROM information_schema.columns WHERE table_schema = '” + schemaName + “’ AND table_name = '” + tableName + “'”)
While rs.AfterLastRow = False
tipText = tipText + text.EndOfLine + rs.Column(“column_name”).StringValue
rs.MoveToNextRow()
Wend
rs.Close()
App.DBConnection.Close()
Return tipText
Starting with the obvious are you using the same username and password to access the database in this system as the other. In case the use doesn’t have the right privileges on the database?
Have you tried putting the schema name in as follows:
App.DBConnection.DatabaseName = "schema"
and trying the TableColumns without the schema.
Yes it is the same connection I’m using for the rest of the app, and the same one I have success with directly querying information_schema.columns in my 2nd sample, so it should not be a permission / rights issue or the wrong DB. There is just one DB used by the whole app.
If it is necessary to specify a DB name of “schema” then the docs do not bother to say so. The provided example simply assumes a “valid open DB connection”, presumably to the DB containing the table you want info on. And as I said in my 2nd message, the direct query works fine.
I find my workaround entirely acceptable except that if the built-in feature worked it would presumably work for any DB, not just Postgres. But then again, if I wanted to go down that road everywhere in the app, I would probably use the MBS DB abstraction layer, or build my own.
I will just chalk this up to “if you want something done right …”
THere is code @ SQLite entry in the Documentation to get the Table List of an SQLite Data base:
SQLite Data Base “Tables”
and this works as advertised.
Yes, but according to the documentation this is supposed to work. I can see issues with MySQL and Bob with Postgres.
@Bob_Grommes I would file an issue about it. Work arounds are useful, and helpful, but if we don’t file then it will not get fixed.
1 Like