ERROR: Invalid input syntax for type numeric "" inserting into PostgreSQL

Guys, I’m having a hard time inserting the first record in postgresql, I’ve already tested ANSI and UNICODE. I activated psqlodbc’s DEBUG, and from what I’ve verified, it doesn’t send any data, when it doesn’t have any records, I don’t use auto-increment in the tables, does anyone have any ideas? tip?

If it already has at least one record in the table, it inserts normally… even if it’s an all blank record

Is your Driver Trace switched on? What does it show?

Yes, it’s like it inserts based on what it already has in the database, and when I clear the table it doesn’t send any parameters.
And if I set auto increment in clarion, it gives the same error…

When using PG, do not set autonumber in Clarion DCT. Instead, PG should be set to use a sequence.

Yes, but I just gave an example.
It doesn’t send anything to PG, it’s like it sees that the table is empty, and it can’t send the data.

I set up a table with 2 columns, and even so, it always gives the error, I have a primary key in both PG and DCT. I’m already freaking out. No sequential fields, just a numeric, and a string.

I think it may be some configuration of the table in the DCT, I only use /TURBOSQL = TRUE.

However, if I already have a field, even if null, it sends the data normally.

Like @totalhip said, you need to adjust the auto numbering
server_side_auto_incrementing_in_clarion_6_3_sql_file_drivers.htm [Clarion Community Help]

But that’s not the problem, the problem is that even in a simple table, without any auto-increment, it gives me this error!
@RichClaCode

Can you supply some code?
The clarion and sql for the file would help, along with a quick example.

What port do you have the pg db listening on?

Postgres is 5433 by default, could that or the firewall be the issue?
how to change postgresql listening port in windows? - Stack Overflow

Can you access the pg db from a terminal window? Have you tried using another app to connect to pg?

Perhaps this Powershell module might be of use Test-AzPostgreSqlFlexibleServerConnect (Az.PostgreSql) | Microsoft Learn

MS SQL server ports are TCP 1433, 4022, 135, 1434, UDP 1434 and ODBC uses 1433, so I’m guessing you are using the std clarion ODBC driver to connect to the pg which is why the ports might be the issue. You might be trying to access the wrong port. What about your firewall software, can you not trace where the connection is going?

Sidenote:I have to remark at the technical ability of the MS SQL Server virus. Worm:W32/Slammer Description | F-Secure Labs only 376 bytes in size! A lesson in securing’s one’s ports from the outside world. :grinning:

My guess is that the turbosql switch and/or using prop:SQL is what is causing your problem. Adding /turbosql for a “normal” table, i.e. one that you want to update and change rather than just use as a scratch table to receive a temporary result set is asking for trouble.

When you declare a table as /TURBOSQL clarion does not do its normal check to make sure that all of the columns you have declared in your clarion dictionary actually exist in the table on the server. As part of that check the driver is able to match up the columns on the server with the ones declared in your dictionary, so that if your table on the server is declared as
create table mytable
id integer,
descrip varchar)

and on the clarion side it is declared with descrip as the first field and id as the second field, if clarion has done its checking up front it will send an insert like:
insert into mytable values (myt:id,myt:descrip);

If it hasn’t done that check, because you have used the /turbosql flag, then Clarion has to assume the fields in the table on the server are in the same order as declared in the clarion dictionary, and will send:
insert into mytable values (myt:descrip,myt:id). If myt:descrip is “Hello World” postgres will have trouble turning that into a number., and you’ll get that invalid syntax for numeric error.

@anon23294430, Yes, I tested it with other programs, I have another C# program that accesses the same table, and it works fine.

@JonW , Thanks for the tip, but it didn’t work, I don’t know what could be happening. I’m losing my mind…

image
@JonW, I removed everything that is parameter, which would you indicate me?
Would there be any configuration?
Or, maybe even a tutorial?
I use C9…

Hello.
With TURBOSQL I had problems writing tables, I had to disable it.

Do you have any other instructions?
Or use the Template itself?
Have a Sample?

I don’t have any other instructions or samples, I’m using Clarion’s standard, without TURBOSQL.

You are from Brazil?

Yes, my english is too bad. haha.
Are you also from Brazil?

I’m Brazilian from Paraná, my english is also bad, google translate helps me.

Are you using PostgreSQL on Clarion today?
Would you help me?