Insert PostgreSQL

Databases
1

My code does not work…
The connection seems to be… i have the msgbox…

but the insert… no…

[code] Dim db As New PostgreSQLDatabase
db.Host = “postgresql-papatch.alwaysdata.net”
db.Port = 5432
db.DatabaseName = “"
db.Username = "”
db.Password = “**”
If db.Connect Then
MsgBox “Connexion OK”
Else
MsgBox(“The connection failed.”)
End If

	db.SQLExecute "BEGIN TRANSACTION"
	sql ="INSERT INTO public.TEST (ID,NOM, PRENOM, AGE, DATE) VALUES ('', 'DUPONT', 'JEAN', '33','');"
	db.SQLExecute (sql)
	db.Commit
	db.SQLExecute "END TRANSACTION"
	
	MsgBox "Envoy"[/code]

I entered all the columns, because if i write only “NOM” , “PRENOM”, “AGE” it’s does not… to…
ID is “serial” and DATE is “timestamp without time zone”

do you have an idea ?

thanks.

julien

2 
	
db.SQLExecute "BEGIN TRANSACTION"
sql ="INSERT INTO public.TEST (ID,NOM, PRENOM, AGE, DATE) VALUES ('', 'DUPONT', 'JEAN', '33','');"
db.SQLExecute (sql)
if db.error then // <<<<<< ALWAYS check for errors !
  msgbox db.errormessage
  db.rollback
else
  db.Commit
end if

MsgBox "Envoy"
3

[quote=333208:@Norman Palardy][code]

db.SQLExecute “BEGIN TRANSACTION”
sql =“INSERT INTO public.TEST (ID,NOM, PRENOM, AGE, DATE) VALUES (’’, ‘DUPONT’, ‘JEAN’, ‘33’,’’);”
db.SQLExecute (sql)
if db.error then // <<<<<< ALWAYS check for errors !
msgbox db.errormessage
db.rollback
else
db.Commit
end if

MsgBox “Envoyé”
[/code][/quote]
ok… if i write like this it’s ok…
but the syntax… is complex , no…?

[code] db.SQLExecute “BEGIN TRANSACTION”
sql =“INSERT INTO ““TEST”” (”“NOM”", ““PRENOM””, ““AGE””) VALUES (‘DUPONT’, ‘JEAN’, ‘33’);"
db.SQLExecute (sql)
if db.error then // <<<<<< ALWAYS check for errors !
msgbox db.errormessage
db.rollback
else
db.Commit
end if

	MsgBox "Envoyé"[/code]

but it’s done !

4

checking for errors is something you need to do because bad sql etc can cause your insert to fail (which is what I expect is happening)
If you never check then you can be surprised when things dont insert like you expect them to
and that was exactly the problem you had

5

[quote=333214:@Norman Palardy]checking for errors is something you need to do because bad sql etc can cause your insert to fail (which is what I expect is happening)
If you never check then you can be surprised when things dont insert like you expect them to
and that was exactly the problem you had[/quote]
yes !! thanks a lot !

6

In Postgresql, if you can, name the fields in lowercase. It makes it a lot more simple. Also make age numeric. Then the instruction becomes.
“INSERT INTO test (nom, prenom, age) VALUES (‘DUPONT’, ‘JEAN’,33);”
Much easier to read.

7

Especially in PostgreSQL, it saves you some troubles if you by default use lowercase.

8

…and there is no need to wrap a simple Insert statement into a transaction. If it fails it fails - no need to rollback.

9

True. Which doesn’t mean transactions are a very powerful mechanism in case you have to update more than one record and you don’t want anything to be changed if anything goes wrong.