PostgreSQLSelect Is Returning an Error

General
1

So far here is my code:

Dim SqlStmt As String
Dim Rs As RecordSet
Dim strTmp As String
Dim strNmMe As String=“PgBinDir”

strTmp=""
SqlStmt=“SELECT ov_value FROM vw_options_sys_app_all WHERE app_function_nm=’” + strNmMe + “’;”
Rs=mDBS.SQLSelect(SqlStmt)
If mDBS.Error=True Then
strErrMsg=strErrMsg + EndOfLine + “Get " + strNmMe + " ERROR: " + mDBS.ErrorMessage
Else
If Rs<>Nil Then
strTmp=Rs.Field(“ov_value”).Value
Else
strTmp=”"
End If
End If

Return strTmp

when i trace this in the debugger, when it gets to the Rs= line it errors out every time. The debugger says that my database connections is and what error code i am getting:

AppName - “”
DatabaseName - “s*******”
Error - “4”
ErrorCode - “An unknown error has occurred”
Host - “l********”
MultiThread - “True”
Password - “*"
Port - “5434”
SSLAuthority - “Nil”
SSLCertificate - “NIl”
SSLKey - “Nil”
SSLMode - “0”
UserName - "j”

*'s are confidential information

2
  1. use MSGBOX to view SqlStmt to make sure it resolved to exactly what you intended.
  2. remove the “;” from the end… this sometime cause problems (usually not required for single statements)
  3. Why are you submitting the SQL to APP.APPDB but checking for an error in MDBS? Are you sure you are referring to you database connection properly?
  4. Once you do get past this error… I’d suggest “IF rs.recordcount>0 THEN” and not “IF rs<>nil”, you WILL have a record set, it just might not have any records in it
3

Sorry that was me testing. i will change the original message to the mDBS

4

i tried removing the ‘;’ but that still caused an error. i can try doing the recordset check but i need that record set to move forward.

5

Take the contents of SqlStmt and try executing it directly against the database outside of your Xojo application.

  SqlStmt="SELECT ov_value FROM vw_options_sys_app_all WHERE app_function_nm='" + strNmMe + "';"

If there is a problem with the query itself matching the schema of the database that should help identify it for you.

You should also look into using prepared statements to keep things safe.

Dim SqlStmt As PostgreSQLPreparedSQLStatement = mDBS.Prepare("SELECT ov_value FROM vw_options_sys_app_all WHERE app_function_nm = $1") SqlStmt.Bind(0, strNmMe) Rs = SqlStmt.SQLSelect

6

I seem to remember seeing this particular error. Issuing queries against a database I wasn’t connected to was the what was wrong at the time. Xojo could do a better job at reporting this…The postgres error codes (http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html) don’t make it into the db.errorcode property, which is a pity.

7

How should the Xojo framework return an error code from the database when it is not connected to it?

8

Ah, Eli you are so smart. Yes, it can’t report this particular error from the server, but it still could complain about not being connected instead of just saying “unknown error”.
Will try to formulate my posts with your watchful eye in mind next time ;-).

9

Yes, I know. Sadly I’m usually only “so smart” once a year. So that was it for 2015…

Well, you need to call Connect() first and check if it returns True.

AFAIK you can call Connect() every time before you query the server. If internally the connection state of the database instance is True, it will ping the server and return early with True if it is still connected. Only if the internal connection state is False or ping returns False, the database class instance will try to connect or re-connect, which then is much more time-consuming than just a ping. The MySQL database class works like that, you’ll find the source code in /Xojo 2015 Release 2.2/Extras/Database Plugin Resources/MySQL/source/source, file: MySQLDatabase.cpp, function MySQLDatabaseConnectHelper. I assume the other database classes do the same.