Trouble writing to PostgreSQL 15

Louis_D · August 24, 2023, 2:08pm

I recently upgraded the database underlying one of my applications to PostgreSQL 15 (from 12, where things were working fine). Using Xojo 2023 R2, I don’t seem to be able to write to the database. Reading from the database is OK. I use functions in postgreSQL. From Xojo I simply call the function and it either updates the existing record, or creates a new one. I verified whether it could be related to usine the public schema. It does not seems so, but I could be wrong.

I am running Xojo 20223 R2 on Win 11 fully patched. The postgreSQL server is Debian Linux 12.1
Running the below code in Xojo fails silently. No exception, no error, no result either…
Running the functions in the database from Navicat, copying the data from the debug pane in Xojo, performs correctly, as expected.

Here is an example Xojo method (inside a class)

If AdrsDirty = True Then     'start saving process
  
  Try
    
    ' SQLStr = "Select 'cust_manage_address_data'"
    If Me.adrnr < 1 Then
      SQLStr = SQLStr + "'', "
    Else
      SQLStr = SQLStr + "'" + Me.adrnr.tostring + "', "                                'p0
    End If
    SQLStr = SQLStr + "( cast('" + Me.nom + "' as character varying), "                'p1
    SQLStr = SQLStr + "cast('" + Me.prenom + "' as character varying), "               'p2
    SQLStr = SQLStr + "cast('" + Me.nom1 + "' as character varying), "                 'p3
    SQLStr = SQLStr + "cast('" + Me.nom2 + "' as character varying), "                 'p4
    SQLStr = SQLStr + "cast('" + Me.nom3 + "' as character varying), "                 'p5
    SQLStr = SQLStr + "cast('" + Me.nom4 + "' as character varying), "                 'p6
    SQLStr = SQLStr + "cast('" + Me.numciv + "' as character varying), "               'p7
    SQLStr = SQLStr + "cast('" + Me.adresse1 + "' as character varying), "             'p8
    SQLStr = SQLStr + "cast('" + Me.adresse2 + "' as character varying), "             'p9
    SQLStr = SQLStr + "cast('" + Me.adresse3 + "' as character varying), "             'p10
    SQLStr = SQLStr + "cast('" + Me.adresse4 + "' as character varying), "             'p11
    SQLStr = SQLStr + "cast('" + Me.ville + "' as character varying), "                'p12
    SQLStr = SQLStr + "cast('" + Me.ville2 + "' as character varying), "               'p13
    SQLStr = SQLStr + "cast('" + Me.region + "' as character varying), "               'p14
    SQLStr = SQLStr + "cast('" + Me.pays + "' as character(2)), "                      'p15
    SQLStr = SQLStr + "cast('" + Me.codepostal + "' as character varying), "           'p16
    SQLStr = SQLStr + "cast('" + Me.nomrue + "' as character varying), "               'p17
    SQLStr = SQLStr + "cast('" + Me.FormattedAddress + "' as character varying), "     'p18
    SQLStr = SQLStr + "cast('" + Me.NumAppt + "' as character varying));"              'p19  
    
    SQLStr = DefineEncoding(SQLStr,encodings.UTF8)
    
    Session.con.ExecuteSQL(SQLStr)
      
   Catch e As DatabaseException
    
    System.debuglog "Save Sales Partner Address data: " + e.message
    TransactionOK = False
    
  End Try
  
End If       'adrsDirty

And here is the correspondng plsql function:

CREATE OR REPLACE FUNCTION "public"."cust_manage_address_data"(INOUT "p0" int8, IN "p1" varchar, IN "p2" varchar, IN "p3" varchar, IN "p4" varchar, IN "p5" varchar, IN "p6" varchar, IN "p7" varchar, IN "p8" varchar, IN "p9" varchar, IN "p10" varchar, IN "p11" varchar, IN "p12" varchar, IN "p13" varchar, IN "p14" varchar, IN "p15" varchar, IN "p16" varchar, IN "p17" varchar, IN "p18" varchar, IN "p19" varchar)
  RETURNS "pg_catalog"."int8" AS $BODY$
	BEGIN
    LOOP
        -- first try to update the key
        UPDATE dadrs SET nom = p1, prenom = p2, nom1 = p3, nom2 = p4, nom3 = p5, nom4 = p6, numciv = p7, adresse1 = p8, adresse2 = p9, adresse3 = p10, adresse4 = p11, ville = p12, ville2 = p13, region = p14, pays = p15, codepostal = p16, nomrue = p17, formattedaddress = p18, numappt = p19 WHERE adrnr = P0;
        IF found THEN
            RETURN ;
        END IF;
        
        BEGIN
            INSERT INTO dadrs(adrnr, nom, prenom, nom1, nom2, nom3, nom4, numciv, adresse1, adresse2, adresse3, adresse4, ville, ville2, region, pays, codepostal, nomrue, formattedaddress, numappt)   VALUES (P0, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12, P13, P14, P15, P16, P17, P18, P19) returning P0;
            RETURN ;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing : loop to try the UPDATE again (concurrent insert of the same key)
        END;
    END LOOP;

END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

Please note: the issue is not limited to this specific function, I am providing an example of what does not work.

Has anyone else encountered a similar issue? Or did I just stumble upon an issue with Xojo and PostgreSQL 15? Or am I just missing something obvious?

Thom_McGrath · August 24, 2023, 2:45pm

Ok, wow… this is the most over-engineered solution to an insert that I’ve ever seen. My guess is the commented-out beginning of SQLStr, but I also see no parameterization happening, so expect a visit from Mr. Bobby Tables.

Here’s how I’d do it:

Var Values(19) As Variant
Values(0) = If(Me.adrnr < 1, "", Me.adrnr.ToString)
Values(1) = Me.nom
Values(2) = Me.prenom
Values(3) = Me.nom1
Values(4) = Me.nom2
Values(5) = Me.nom3
Values(6) = Me.nom4
Values(7) = Me.numciv
Values(8) = Me.adresse1
Values(9) = Me.adresse2
Values(10) = Me.adresse3
Values(11) = Me.adresse4
Values(12) = Me.ville
Values(13) = Me.ville2
Values(14) = Me.region
Values(15) = Me.pays
Values(16) = Me.codepostal
Values(17) = Me.nomrule
Values(18) = Me.FormattedAddress
Values(19) = Me.NumAppt

Session.con.ExecuteSQL("BEGIN TRANSACTION;")
Var Results As RowSet = Session.con.SelectSQL("INSERT INTO dadrs (adrnr, nom, prenom, nom1, nom2, nom3, nom4, numciv, adresse1, adresse2, adresse3, adresse4, ville, ville2, region, pays, codepostal, nomrue, formattedaddress, numappt) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20) ON CONFLICT (adrnr) DO UPDATE SET nom = $2, prenom = $3, nom1 = $4, nom2 = $5, nom3 = $6, nom4 = $7, numciv = $8, adresse1 = $9, adresse2 = $10, adresse3 = $11, adresse4 = $12, ville = $13, ville2 = $14, region = $15, pays = $16, codepostal = $17, nomrue = $18, formattedaddress = $19, numappt = $20 RETURNING adrnr;", Values)
Session.con.ExecuteSQL("COMMIT;")

You can adapt the error handling, of course. The comment in the exception handler suggests defense against some kind of race condition, but I don’t have enough information to know what that is. It’s not the primary key, so I’m guessing you have other unique constraints on the table. I’m not sure how trying the command again is supposed to help though.

If you really want to fix your function, I’m at a loss. Some digging through the pg logs might lend a clue.

Louis_D · August 24, 2023, 2:57pm

Thank you Thom;

I used to have a very similar method as you are suggesting. And another similar method to update existing records. They are still available as commented out code in my customer class. I did a test where I reactivated the old methods , and called them instead of the new one. I encountered the same issues.

I suppose that sifting through the PG logs is, as you suggest, my next step.

I like the way that you declare the variables. Your way is much cleaner than mine. I am adopting this approach from now on. Thank you for that too!

regards,

LD

Louis_D · August 24, 2023, 3:06pm

Thom, You made me realize that in a number of methoda, I kept the old vatiable declaration, such that the resulting SQL string is incorrect. And I did do a similar error in thelast iteration of the old methods, too… I was indeed missing the obvious. Thank you again.

Note to self: do one change at a time. It is easier to figure out which of the changes id the issue.

Louis_D · August 24, 2023, 4:34pm

In order to provide insights to anyone trying to do something, similar, here is the solution that works. The PostgreSQL function remains unchanged. I did have enough coffee when I wrote that one.

My Xojo method had been so often modified that it did not make any sense anymore.
Worse, I bastardized 4 other methods in the same way. Focus Louis, Focus…

So, here is code that actually works and actually uses the PostgreSQL function!

If AdrsDirty = True Then     'start saving process
  
  Try
    Dim P(19) As Variant
    
    ' do not use p(0), use property instead - unknown issue using P(0)  == to be investigated
    P(1) = Me.nom                    'p1
    P(2) = Me.prenom                 'p2
    P(3) = Me.nom1                   'p3
    P(4) = Me.nom2                   'p4
    P(5) = Me.nom3                   'p5
    P(6) = Me.nom4                   'p6
    P(7) = Me.numciv                 'p7
    P(8) = Me.adresse1               'p8
    P(9) = Me.adresse2               'p9
    P(0) = Me.adresse3               'p10
    P(11) = Me.adresse4              'p11
    P(12) = Me.ville                 'p12
    P(13) = Me.ville2                'p13
    P(14) = Me.region                'p14
    P(15) = Me.pays                  'p15
    P(16) = Me.codepostal            'p16
    P(17) = Me.nomrue                'p17
    P(18) = Me.FormattedAddress      'p18
    P(19) = Me.NumAppt               'p19  
    
    SQLStr = "Select cust_manage_address_data (" + Me.adrnr.tostring + ",'" + P(1) + "','" + P(2) + "','" + P(3) + "','" + P(4) + "','" + P(5) + "','" + P(6) + "','" + P(7) + "','" + P(8) + "','" + P(9) + "','" + P(10) + "','" + P(11) + "','" + P(12) + "','" + P(13) + "','" + P(14) + "','" + P(15) + "','" + P(16) + "','" + P(17) + "','" + P(18) + "','" + P(19) + "');"
   
 Session.con.ExecuteSQL(SQLStr)
       
  Catch e As DatabaseException
    
    System.debuglog "Save Sales Partner Address data: " + e.message
    TransactionOK = False
    
  End Try
  
End If   'adrsDirty

Thom_McGrath · August 24, 2023, 4:49pm

You’re still not handling sanitization correctly though. Pass the P array as a second parameter to ExecuteSQL, and replace each function parameter with $1, $2, and so on. Your P(0) will be $1.

Louis_D · August 24, 2023, 6:51pm

I suppose that you have something like this in mind.

Dim adrsPS As PostgreSQLPreparedStatement


Dim P(19) As Variant
P(0) = Me.AdrNr.tostring     'p0          
P(1) = Me.nom                    'p1
P(2) = Me.prenom                 'p2
P(3) = Me.nom1                   'p3
P(4) = Me.nom2                   'p4
P(5) = Me.nom3                   'p5
P(6) = Me.nom4                   'p6
P(7) = Me.numciv                 'p7
P(8) = Me.adresse1               'p8
P(9) = Me.adresse2               'p9
P(0) = Me.adresse3               'p10
P(11) = Me.adresse4              'p11
P(12) = Me.ville                 'p12
P(13) = Me.ville2                'p13
P(14) = Me.region                'p14
P(15) = Me.pays                  'p15
P(16) = Me.codepostal            'p16
P(17) = Me.nomrue                'p17
P(18) = Me.FormattedAddress      'p18
P(19) = Me.NumAppt               'p19  

AdrsPS = Session.con.Prepare("Select cust_manage_address_data($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20);")
AdrsPS.bind P
AdrsPS.ExecuteSQL

So far, I cannot run this without raising a database exception. Here, either P(0) = me.adrnr is null and causes an exception, or P(0) = me.AdrNr.ToString causes another database exception (ERROR: invalid input syntax for type bigint: “”
CONTEXT: unnamed portal parameter $1 = ‘’
)
… and to be clear, me.adrnr is 1348 in the debug pane. I am a bit puzzled with p(0) being null

all these exceptions are why I resorted to

 SQLStr = "Select cust_manage_address_data (" + Me.adrnr.tostring + ",'" + P(1) + "','" + P(2) + "','" + P(3) + "','" + P(4) + "','" + P(5) + "','" + P(6) + "','" + P(7) + "','" + P(8) + "','" + P(9) + "','" + P(10) + "','" + P(11) + "','" + P(12) + "','" + P(13) + "','" + P(14) + "','" + P(15) + "','" + P(16) + "','" + P(17) + "','" + P(18) + "','" + P(19) + "');"
   
 Session.con.ExecuteSQL(SQLStr)

Am I missing something again?

Thom_McGrath · August 24, 2023, 7:07pm

First, you don’t need the Prepare step. Just do ExecuteSQL(sql, p) instead. Pass the whole array as the second parameter.

If Me.AdrNr is an integer, do not convert it. Use P(0) = Me.AdrNr and that’s it. If Me.AdrNr is something nullable, you’ll want to make sure it’s not null before assigning it to P(0). When using P(0) = Me.AdrNr there’s no way P(0) could be null if Me.AdNr is defined as an integer.

Louis_D · August 24, 2023, 7:23pm

Yes, I wish it worked! However…

In the test, me.adrnr = 1348, and yes it is defined as an integer
P(0) = me.Adrnr yields P(0) = null. - Perhaps a new Xojo issue?
passing just P results in a database error because P(0) is null

So I am trying to work around the issue.

This does work. It is the long way around, but it works. It is an improvement over my previous solution.

AdrsPS = Session.con.Prepare("Select cust_manage_address_data($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20);")
AdrsPS.bind(0, Me.AdrNr)
Dim I As Integer
For I = 1 To 19
  AdrsPS.bind( I, P(I-1) )
Next
AdrsPS.ExecuteSQL

Thom_McGrath · August 24, 2023, 7:29pm

Check your assignments between P(9) and P(11).

Louis_D · August 24, 2023, 7:49pm

not sure what yiou see, but it all seems to be fine here.

correct data is passed to the db. ( I erased every field except for the address number in the database record, then ran the test again. the record is correctly updated)

Thom_McGrath · August 24, 2023, 8:00pm

You are assigning P(0) twice instead of P(10).

Louis_D · August 24, 2023, 10:17pm

indeed. I fixed that after posting it here. Forgot about it.

Thank you!

Thom_McGrath · August 24, 2023, 10:28pm

And just confirming that now that you have the correct values, it’s working as I said it will?

Louis_D · August 24, 2023, 10:49pm

No. the p(0) assignment is not working. I still need my workaround. But I understand that your way would work fine if it were not for that issue.

For some reason, the assignment of the integer property to the variant array element fails, leaving it null. I have a few other similar cases to fix, I will see whether this is a unique case (in which case I missed something somewhere…) or a common theme. If I see the same behavior, I will prepare an example project and submit a new issue. (did not already find one)

The reason that I work with a stored function is that I can just save the data set and not bother with updating or inserting. One call to rule them all, so to speak.

Thank you once again for your help. I just could not see the forest for all the trees.

Thom_McGrath · August 24, 2023, 11:10pm

I promise, you must have a logic problem in your code somewhere. It is impossible for a variant to be null after assigning an integer to it. Put a breakpoint after the assignment to prove it.

In one of your tests, you would assign P(0) correctly, then replace it with Me.adresse3 instead of assigning Me.adresse3 to P(10). So P(0) would have an incorrect value (thus the casting error) and P(10) would be null.

If you can get the array correct, it will work. I do this all the time. Another option is to skip the array entirely and just do ExecuteSQL(sql, Me.AdrNr, Me.nom, Me.prenom, etc) which will probably be a little more error-resistant for you.

Louis_D · August 25, 2023, 11:21am

Well Thom, after some sleep and a suitable amount of coffee, I reviewed everything. There was a lot of leftover code in the method from previous fixes and additions (this is an old-ish development that used to work fine and needed updates. I broke everything in the process…) I deleted everything and started again from scratch. I also made a decision to drop the stored procedures in PostgreSQL. To go back to basics and keep it simple. The end result is a solution that is very similar to your first post. And everything does work, just like you said.

In any case, your help was key to resolving the issues once and for all, to see the mistakes that were staring me in the face all along. Thank you! This method is part of a much larger process. I will be redesigning the rest of the process to abandon the stored procedures in all of the relevant methods.

Thom_McGrath · August 25, 2023, 1:01pm

Sounds like a solid plan. And you’re welcome.