PostgreSQL Error on insert into

  1. 3 months ago

    Alexis C

    Jan 10 Pre-Release Testers Puerto Rico, USA

    ERROR: current transaction is aborted, commands ignored until end of transaction block

    sqlInsert = " INSERT INTO journal (amount, socionum, lastbalance, userid, debito, credito, transtype, paytype, payinfo, fdate, numrecibo, stationnum, refid) " _
    + " VALUES (" + format(v_amount,"###,#0.00") + ", '" + v_socionum + "'," +Str( lastbalance) + ",'" + v_userid + "'," _
    + Format(v_debito,"###,#0.00") + "," + Format(v_credito,"###,#0.00") + ",'" + v_transtype + "','" + v_paytype + "','" + v_payinfo + "', '" + fdate.SQLDate + "','" + v_numrecibo + "'," + v_stationnum + "," + v_RefID +" )"

    any idea what is the problem.

    it run perfect on the pgadmin query but no on XOJO

  2. Joost R

    Jan 10 Pre-Release Testers, Xojo Pro The Netherlands

    so if you copy the content of generated sqlinsert string-variable into PG-admin it works ?

  3. Richard D

    Jan 10 Pre-Release Testers, Xojo Pro Europe (UK, London)

    would you mind show us the string for the sql statement after all the values is included?

  4. Alexis C

    Jan 10 Pre-Release Testers Puerto Rico, USA

    Yes joost Rongen

  5. Richard W

    Jan 10 Pre-Release Testers, Xojo Pro

    I think the commas in the number formats may be the problem.

  6. Alexis C

    Jan 10 Pre-Release Testers Puerto Rico, USA

    INSERT INTO journal (amount, socionum, lastbalance, userid, debito, credito, transtype, paytype, payinfo, fdate, numrecibo, stationnum, refid) VALUES (21.45, '000638',-56.11,'Admin',0.00,21.45,'C','Cash','', '2019-01-10','23',916,0 )

    ERROR: current transaction is aborted, commands ignored until end of transaction block

  7. Tobias B

    Jan 10 Pre-Release Testers, Xojo Pro Bern, Switzerland
    Edited 3 months ago

    @Alexis Cnbsp;Lugo ERROR: current transaction is aborted, commands ignored until end of transaction block

    if the transaction is already aborted, all following commands (correct or not) are ignored and you get the error mentioned. So if you are looking for the problem, you have to look to the error that happened before the mentioned one, with a query earlier in the same transaction. To recover, you can only ROLLBACK or ROLLBACK TO SAVEPOINT if such exists.

    Demo:

    $ psql -X
    psql (10.5)
    Type "help" for help.
    
    user=# begin;
    BEGIN
    user=# select 'ok';
     ?column? 
    ----------
     ok
    (1 row)
    
    user=# select error;
    ERROR:  column "error" does not exist
    LINE 1: select error;
                   ^
    user=# select 'ok';
    ERROR:  current transaction is aborted, commands ignored until end of transaction block
    user=# select error;
    ERROR:  current transaction is aborted, commands ignored until end of transaction block
    user=# rollback;
    ROLLBACK
    user=# begin;
    BEGIN
    user=# select 'ok';
     ?column? 
    ----------
     ok
    (1 row)
    
    user=# savepoint sp;
    SAVEPOINT
    user=# select error;
    ERROR:  column "error" does not exist
    LINE 1: select error;
                   ^
    user=# select 'ok';
    ERROR:  current transaction is aborted, commands ignored until end of transaction block
    user=# rollback to savepoint sp;
    ROLLBACK
    user=# select 'ok';
     ?column? 
    ----------
     ok
    (1 row)
    
    user=# commit;
    COMMIT
  8. Bob K

    Jan 10 Pre-Release Testers, Xojo Pro Kansas City

    After the DB.SQLExecute check the db.error property and if true see what the db.ErrorMessage is. It will almost always tell you exactly what the error is from the database perspective.

  9. Alexis C

    Jan 10 Pre-Release Testers Puerto Rico, USA

    error code 3

  10. nicolás c

    Jan 10 Pre-Release Testers, Xojo Pro argentina
    Edited 3 months ago

    hola alexis.. la consulta es parte de una transaccion? en caso de que si... podrias ejecutar la consulta de forma individual (no como parte de una transaccion) desde xojo e indicarme que mensaje de error aparece exactamente?

  11. Bob K

    Jan 10 Pre-Release Testers, Xojo Pro Kansas City

    db.ErrorMessage. I don't care what the error code is as that tells me nothing.

  12. Alexis C

    Jan 10 Pre-Release Testers Puerto Rico, USA

    thanks to all help, i found the error
    the problem was data type on postgres i send value 0 and have to be false

  13. Maximilian T

    Jan 10 Pre-Release Testers, Xojo Pro Europe, Germany, Berlin

    Well, the errormessage is "ERROR: current transaction is aborted, commands ignored until end of transaction block". That's exactly what the server returns in the scenario that Tobias showed.

or Sign Up to reply!