B4J Question Got Error while save date into postgres database from b4j

[Pravee7094]

Hi all,
I got error while saving date into postgres database from b4j.
Sample coding here:

    Dim ls_billdate As String
    ls_billdate = is_billdate.GetVal.Value
    Log("Bill Date : " & ls_billdate) 'returns 2021-06-18'
    
    sql1.BeginTransaction
    sql1.ExecNonQuery2("insert into table_name (salespk, billdate customername) values (?,?,?) ", _
                         Array As Object(ls_salesheaderpk_create, ls_billdate, is_customername.GetVal.Value))
    sql1.TransactionSuccessful

Error I got:

Error occurred on line: 321 (AddInvoicesInformation)
org.postgresql.util.PSQLException: ERROR: column "billdate" is of type date but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 114
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:138)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:67)
at anywheresoftware.b4j.objects.SQL.ExecNonQuery2(SQL.java:206)
at b4j.example.addinvoicesinformation._btnsave_click(addinvoicesinformation.java:361)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at jdk.internal.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:108)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:95)
at anywheresoftware.b4j.object.WebSocketModule$Adapter$1.run(WebSocketModule.java:126)
at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:47)
at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:43)

I think the error is about data type in the b4j for date format. How to save to postgresql with the correct format?

Any Suggestion or Help?

Thanks

 

[Andrew (Digitwell)]

I'm a MYSQL person but a quick google brought up these:

PostgreSQL TO_DATE() Function: Convert String to Date

This tutorial shows you how to use the PostgreSQL to_date function to convert a string literal to a date value based on a specified pattern.

www.postgresqltutorial.com

or

Postgres String to Date and Timestamp | Blendo

The to_date function in PostgreSQL is used to converting strings into dates. Its syntax is to_date(text, text) and the return type is date.

www.blendo.co

 

[JoseAlfredo]

a comma is missing in the sentence


sql1.BeginTransaction
sql1.ExecNonQuery2("insert into table_name (salespk, billdate, customername) values (?,?,?) ", _
Array As Object(ls_salesheaderpk_create, ls_billdate, is_customername.GetVal.Value))
sql1.TransactionSuccessful

 

[Pravee7094]

Solved. I just changed my insert query to this

        sql1.BeginTransaction
        Dim ls_sql As String
        ls_sql = "insert into in_sales_header "
        ls_sql = ls_sql & "(salesheaderpk, vouchernumber, voucherdate, vouchertype, customername) "
        ls_sql = ls_sql & " values ( "
        ls_sql = ls_sql & " '" & ls_salesheaderpk_create & "', '" & ls_billnumber & "', '" & ls_billdate & "', "
        ls_sql = ls_sql & "'" & is_vouchertype.GetVal.Value & "', '" & is_customername.GetVal.Value & "' ) "
        
        sql1.ExecNonQuery(ls_sql)
               
        sql1.TransactionSuccessful

It worked for me. Thanks for your suggestion @Andrew (Digitwell) @JoseAlfredo (Nice catch Man)