Moving to postgreSQL

Having decided to migrate my projects to PostgreSQL, I took baby steps: I selected a small project and migrated the database, except for stored procedures. That is still on the todo list. For simpler selections, I am planning to manage the SQL right from the application. I can connect to the database (at least connect tells me so). My first attempt to a simple select returns nothing at all. (of course, the table is populated)

In this snippet, me.Con is the database property at session level of the web application. This snippet is in a session method. It is called at the beginning of a new session when the user selected the environment (DEV, QAS, PRD) and language (ENG, FRA). The connexion is made previously with the selected environment. Now, it is time to read the session data: texts, messages, validation lists, etc.

edit: updated the code with append.

[code]// Nouvelle slection avec PostgreSQL (Xojo Plugin)

’ application texts
'Dim I As Int16
Dim RS1 As RecordSet
'Dim SQL As String
Me.Langue = Langue
If Me.con.connect Then
Try

'SQL = "Select * FROM CTXT WHERE Langue = 'FRA' ORDER BY TxtId Asc"
'SQL = "Select * from CTXT ;"
'RS1 = Me.con.SQLSelect(SQL)
Dim ps As PostgreSQLPreparedStatement = Me.con.Prepare("Select * FROM CTXT WHERE Langue = $1 ORDER BY TxtId Asc")
ps.bind(0, Langue)
RS1 = ps.SQLSelect

Catch e As RuntimeException

MsgBox e.Message
Return False

End Try

If rs1 <> Nil Then
Redim Me.Apptexts(-1)
Me.Apptexts.Append(langue)
'rs1.movefirst
While Not RS1.EOF
If rs1.field(“Texte”).stringvalue <> “” Then
Me.Apptexts.Append(DefineEncoding(rs1.field(“Texte”).stringvalue, encodings.UTF8))
End If
rs1.movenext
Wend
End If
End If
rs1 = Nil
[/code]

What is wrong with my code?

Check the Error and ErrorMessage after you execute the Prepared Statement. What do they say?

I am not catching any error.
edit: oh. the db error. I am indeed not checking that.

I added this code right after the execution of the prepared statement:

If Me.Con.Error Then MsgBox("DB Error: " + Me.Con.ErrorMessage) End If

I am not receiving an error.

Without access to your environment, my best guess is that you are not connecting to what you think you’re connecting to or Langue isn’t what you think it is.

Note that PSQL is case-sensitive in its queries. Could that be it?

This, btw, is true only if you explicitly asked PG to be case-sensitive. So if you do
Create Table MyTaBle(…), Select * from mytable will work. But if you go Create Table “MyTaBle”(…), as table creation wizards of some admin tools do behind your back you’re asking PG to be serious about your capitalization, so here Select * from mytable will actually fail. And if that was the case Louis would have received an error anyway.

I’d try to execute the SQL directly without a prepared statement. Next I’d examine the PG log file. You might want to tweak your postgresql.conf file so that it logs every query (see sql - How to log PostgreSQL queries? - Stack Overflow).

Here’s a blog post about case sensitivity that explains exactly what happens.

For the problem we’re dealing with here, I’d start by just opening the recordset without a prepared statement and to remove any other things, I’d probably start with the following sql query just to see it I get an answer from the database
SELECT version();

To be clear, I was talking about case sensitivity in text values. select * from table where a = 'hello' will not return records whose a is ‘HELLO’.

Thank you all for your answers. Before posting, I tried some of your suggestions already.

  • I first tried using straight SQL select with simple SQL strings, the remnants can be seen as commented lines.
  • The table is really called CTXT in the database
  • Language strings are capital letters only. Langue is “FRA” in my test and it is either “FRA” or “ENG” in the table. Always capital letters. I am using

Here is the definition of CTXT. Is there anything I did wrong here?

[code]CREATE TABLE public.“CTXT”
(
“TxtId” integer NOT NULL,
“Langue” character varying(3) COLLATE pg_catalog.“default” NOT NULL,
“Texte” character varying(500) COLLATE pg_catalog.“default”,
“Area” character varying(50) COLLATE pg_catalog.“default”,
CONSTRAINT “CTXT_pkey” PRIMARY KEY (“Langue”, “TxtId”)
)
WITH (
OIDS = TRUE
)
TABLESPACE pg_default;

ALTER TABLE public.“CTXT”
OWNER to postgres;[/code]

I will make changes to logging as per @Maximilian Tyrtania suggestion. So far, the log does not say much. I have not yet tried @Dirk Cleenwerck suggestion to query the version. Will do later today. I am away from my development system right now and while I can connect through VPN, I must work on something else right now.

Thank you again everyone. I will review again all the posts to make sure I don’t forget to check any of your suggestions.

you mean select * from table where a ILIKE 'hello'?

Using the collate clause like you did is usually useful only if you are unhappy with your values of LC_COLLATE and LC_CTYPE. Setting them to COLLATE pg_catalog.“default” is just noise because that’s the default collation anyway. I also strongly prefer the text datatype to avoid any compatibility problems. That shouldn’t be the problem here though.

I’d fire this SQL from PGAdmin or psql to your database and look at the result. Is it what you expect?

Oh, and I notice that you actually did quote the table name and the field names. In your shoes I’d rename the table and the fieldnames to non-quoted values because now with the quotes you’d always have to issue your SQL-Statements like this:

Select * FROM “CTXT” WHERE “Langue” = $1 ORDER BY “TxtId” Asc

@Maximilian Tyrtania: You found the problem. I connected to my development computer and changed my prepared statement as follows and it worked. As a bonus, I have other DB errors, but I think that the first roadblock is now cleared. Thank you so much!

Dim ps As PostgreSQLPreparedStatement = Me.con.Prepare("Select * FROM ""CTXT"" WHERE ""Langue"" = $1 ORDER BY ""TxtId"" Asc")

Edit: Now, I need to change the way I define tables and fields in PostgreSQL in order to avoid this mess.

I do not use PostgresSQLPreparedStatement
i use DIm SqlStr As String
SqlStr=“Select * From table Where tableName=‘jajaja’”

[quote=395125:@Alexis Colon Lugo]I do not use PostgresSQLPreparedStatement
i use DIm SqlStr As String
SqlStr=“Select * From table Where tableName=‘jajaja’”[/quote]
WHere’s the “dislike” button :wink:

Well, I see where you are coming from, but I’m not sure putting it like that will help Alexis understand the problem prepared statements solve.
@Alexis Colon Lugo You need to use prepared statements when handling user entered data to defend against so called injection attacks and quoting issues. A well written explanation can be found here.

And I do see them overused in this forum too (in situations where no user entered data is involved for example). The time saved by pre-planning the query is usually negligible except for the most complex queries.

sorry
but i use replace look for ( ; ) in al my query