First Xojo App Won't Write To PostgreSQL

Databases
1

I’ve been working on this for a while and can’t seem to come up with a solution.

I have an instance of Postgres running on another machine, and I have been completely unsuccessful writing any data to the database using Xojo.

I am using this prepared statement:

[code]db1.SQLExecute ( “BEGIN TRANSACTION” )

Dim ps1 As PostgreSQLPreparedStatement = _
db1.Prepare ( "INSERT INTO users ( name_first, name_last, email, hash1, salt2 ) VALUES ( $1, $2, $3, $4, $5 ); " )

ps1.SQLExecute ( first, last, email, hashstr1, saltstr2 )

db1.Commit

If db1.Error Then
MsgBox ( "DB 1 Error: " + db1.ErrorMessage )
End If
[/code]

I appear to have successfully dimensioned db1 as a PostgreSQLDatabase I’m not sure if I’m missing a key step or what is happening. I’m not sure if I need to include more code in order to be helpful or not.

Thank you all in advance!

2
  1. what is the error message after the SQLExecute? [msgbox db1.errormessage]
  2. are you sure you have an actual DB connection? (again are you checking for error messages?)
3

Also, did you just not include the Commit in your example code?

4

@Dave S I am not getting any error messages on the SQL execute, it acts as if it has written, but when I check my database using Navicat there is nothing written. I actually check for a db1.connect when I connect to the server and it returns as true with no db1.ErrorMessage.

@Kem Tekinay I did seem to omit that from my example, that is the next line I have edited my original comment to show that line.

5

Check for an error right after ps1.SQLExecute, what does that give you?

6

exactly what I had asked.
He says he isn’t getting “any”… but querying “db1.errormessage” should at least give “no error message” as the message

  1. you don’t have a connection (perhaps it dropped?)
  2. you query is in error
  3. it really did work
7

My guess is that there is no error because “Commit” worked and cleared the previous error.

8

@Kem Tekinay @Dave S

Okay, added a trap for an error between the prepared statement and the commit and got an error code 3 : “ERROR: invalid byte sequence for encoding “UTF8”: 0xe3 0x35 0x7a”

Not sure where to go from here, I’m still ridiculously new at this. Didn’t realize a commit would clear out errors.

9

Try using db1.SQLExecute("COMMIT TRANSACTION") instead of db1.Commit

@Bob Keeney was having a similar issue with MS SQL recently and that solution worked.

Edit: Ahh, your post showed up after I submitted mine, so it looks like the commit isn’t the issue.

10

@Jared Feder That didn’t seem to work. It appears there’s a text formatting that Xojo is trying to pass that Postgres doesn’t like. I imagine I brought some of this on myself by trying to make my first application to talk to Postgres write hash data and plain text instead of just plain text.

11

That error is from PostgreSQL server and seems to be related encoding. Perhaps data you are sending does not match the database encoding

https://stackoverflow.com/questions/4867272/invalid-byte-sequence-for-encoding-utf8

12

You could try adding db1.SQLExecute("SET NAMES 'utf8'") after starting the transaction to ensure that all of the communication with the database is occurring with utf8 encoding.

Also, make sure that the data that you are inserting into the database is utf8 encoded.
Are you getting any of the data from an external source, or is it all generated within your Xojo application?
If it’s coming from an external source, you may need to use either DefineEncoding if you know that the string is utf8 or ConvertEncoding if it is not.

13

I bet you forgot to hex-encode (or Base64-encode) either the hash or the salt and are trying to store binary data in a text column.

14

@Kem Tekinay That did it! Here is what I have now, please tell me if there’s a better way to skin this cat.

Where hashData1 is a Xojo.Core.MemoryBlock:

Dim hashmb1 As MemoryBlock = hashData1.Data Dim hashstr1 As String = EncodeBase64 ( hashmb1.StringValue ( 0, hashData1.Size ) , 0 )

15

That should do it.

16

Thank you.

17

BTW, I didn’t guess the binary data thing because I’ve done that many times myself. Nosiree… :wink:

18

Also, wrapping a one line insert statement in a transaction is rather pointless.

19

@Maximilian Tyrtania I’m afraid I don’t understand. Are you saying I don’t need the “Begin transaction” and commit statements?

20

Yep, exactly. SQL commands are “atomic” meaning they either fail or succeed. They won’t fail half way. If they could, wrapping them in a transaction would make sense.