SetDatabaseDate()

[RichAlgeni]

This ability looks to be missing from the Database library.

Using PostgreSql, you are not able to use SetDatabaseString() to update dates.

SetDatabaseTime() might be included as well.

[mk-soft]

Every database system have a different syntax of date time.

With string no problem..

https://www.postgresql.org/docs/9.1/sta ... etime.html

[RichAlgeni]

With string: problem!

Code: Select all

EnableExplicit

OpenConsole()

UsePostgreSQLDatabase()

Define result.i
Define psqlUpdate.s
Define dataBaseNumber.i
Define updateDtTm.s     = "2017-12-31 00:00:01"

dataBaseNumber = OpenDatabase(#PB_Any, "host=andromeda port=5432 dbname=postgres", "postgres", "Th1s1sucks")

If dataBaseNumber = 0
    PrintN("cannot open PostgreSql datbase, " + DatabaseError())
Else
    PrintN("Opened PostgreSql datbase to " + Str(dataBaseNumber))

    psqlUpdate = "DROP TABLE IF EXISTS test_table;"
    result     = DatabaseUpdate(dataBaseNumber, psqlUpdate)
    If result > 0
        PrintN(psqlUpdate)
        PrintN("        successful")
        PrintN("")
    Else
        PrintN("Error: " + psqlUpdate + ", error: " + DatabaseError())
        PrintN("")
    EndIf

    psqlUpdate = "CREATE TABLE test_table (test_table_key SERIAL PRIMARY KEY,last_update TIMESTAMP);"
    result     = DatabaseUpdate(dataBaseNumber, psqlUpdate)
    If result > 0
        PrintN(psqlUpdate)
        PrintN("        successful")
        PrintN("")
    Else
        PrintN("Error: " + psqlUpdate + ", error: " + DatabaseError())
        PrintN("")
    EndIf

    If result > 0
        PrintN("Attempt at insert using 'SetDatabaseString'")

        SetDatabaseString(dataBaseNumber, 0, updateDtTm)

        psqlUpdate = "INSERT INTO test_table (last_update) VALUES($1);"
        result     = DatabaseUpdate(dataBaseNumber, psqlUpdate)
        If result > 0
            PrintN(psqlUpdate)
            PrintN("        successful")
            PrintN("")
        Else
            PrintN("Error: " + psqlUpdate + ", error: " + DatabaseError())
            PrintN("")
        EndIf

        PrintN("Attempt at insert using the variable")

        psqlUpdate = "INSERT INTO test_table (last_update) VALUES('" + updateDtTm + "');"
        result     = DatabaseUpdate(dataBaseNumber, psqlUpdate)
        If result > 0
            PrintN(psqlUpdate)
            PrintN("        successful")
            PrintN("")
        Else
            PrintN("Error: " + psqlUpdate + ", error: " + DatabaseError())
            PrintN("")
        EndIf
    EndIf

    psqlUpdate = "DROP TABLE IF EXISTS test_table;"
    result     = DatabaseUpdate(dataBaseNumber, psqlUpdate)
    If result > 0
        PrintN(psqlUpdate)
        PrintN("        successful")
        PrintN("")
    Else
        PrintN("Error: " + psqlUpdate + ", error: " + DatabaseError())
        PrintN("")
    EndIf

    CloseDatabase(dataBaseNumber)
EndIf

Input()

CloseConsole()

End

Code: Select all

Opened PostgreSql datbase to 36440384
NOTICE:  table "test_table" does not exist, skipping
DROP TABLE IF EXISTS test_table;
        successful

CREATE TABLE test_table (test_table_key SERIAL PRIMARY KEY,last_update TIMESTAMP);
        successful

Attempt at insert using 'SetDatabaseString'
Error: INSERT INTO test_table (last_update) VALUES($1);, error: ERROR:  column "last_update" is of type timestamp without time zone but expression is of type text
LINE 1: INSERT INTO test_table (last_update) VALUES($1);
                                                    ^
HINT:  You will need to rewrite or cast the expression.


Attempt at insert using the variable
INSERT INTO test_table (last_update) VALUES('2017-12-31 00:00:01');
        successful

DROP TABLE IF EXISTS test_table;
        successful

[spikey]

If you just want to keep track of a modification timestamp you could use a trigger instead...

This should do it though, although I can't test it at the moment because my Postgres VM has gone into a sulk today for some reason and is refusing to talk over the network :-/

Code: Select all

psqlUpdate = "INSERT INTO test_table (last_update) VALUES(to_timestamp($1, 'YYYY-MM-DD HH:MI:SS'));"

In case I've made a mistake the documentation for the built in casts is at https://www.postgresql.org/docs/current ... tting.html

[Marc56us]

If you just want to keep track of a modification timestamp you could use a trigger instead...

This should do it though, although I can't test it at the moment because my Postgres VM has gone into a sulk today for some reason and is refusing to talk over the network :-/

Code: Select all

psqlUpdate = "INSERT INTO test_table (last_update) VALUES(to_timestamp($1, 'YYYY-MM-DD HH:MI:SS'));"

(I also searched (not found) then I tested your version)

As is, little error (sorry, my Posgtres is in french)

Code: Select all

Attempt at insert using 'SetDatabaseString'
Error: INSERT INTO test_table (last_update) VALUES(to_timestamp($1, 'YYYY-MM-DD HH:MI:SS'));
Error: ERREUR:  l'heure « 0 » est invalide pour une horloge sur 12 heures
HINT:  Utilisez une horloge sur 24 heures ou donnez une heure entre 1 et 12.

(~Hour format is invalid, use 24 hour clock or provide hour 1 to 12)

So I after read PostgresSQL doc:
https://www.postgresql.org/docs/9.6/sta ... tting.html
I correct with HH24:MI:SS

Code: Select all

psqlUpdate = "INSERT INTO test_table (last_update) VALUES(to_timestamp($1, 'YYYY-MM-DD HH24:MI:SS'));"

And this is OK

Code: Select all

Attempt at insert using 'SetDatabaseString'
INSERT INTO test_table (last_update) VALUES(to_timestamp($1, 'YYYY-MM-DD HH24:MI:SS'));
        successful

Good find, to add to the PB help (as a remark), I think

[RichAlgeni]

I appreciate your responses, but they miss the point.

The point is to bind variables to the literal '$' position with Postgres. I use triggers all the time, and they are great for a last update field. I should have used a different variable name to illustrate my point: Setting a date to a literal for a column.

I love this method for security purposes, and for ease of coding. In regards to security, we probably only need to bind strings, as numerics would cause an error if used for an injection attempt. The same would be true with a date. That's why I don't consider this to be a bug, but an enhancement request.