Good morning.
On Gambas 3.20  project I have the following SQL string:

"SELECT numero, data_venda, cliente, setor, prazo,
       (data_venda + (prazo * INTERVAL '1 day')) AS test_data_sum
FROM vendas
WHERE prazo IS NOT NULL
  AND setor = 'Laudos'
  AND faturado IS FALSE
  AND  (data_venda + (prazo * INTERVAL '1 day')) = CURRENT_DATE;"

1 - None of the fields in the table is Null or the wrong type.
2 - This query runs well, without error if typed in connection console and returns the right results.

But the very same query string, when running in the IDE produces the error message:
"Type mismatch. Expected number, date or string but found Void."

Any tips? I don't know what to do

how are you using that string in your code ... show us some Gambas code using that string ,,,, then someone may be able to help .

this might give you some help … I don't use postgre   I have limited use of SQLite
this is my handleing module … might give you a hint at the syntax  :

using the Subst()  is just a better way of enclosing fields in " " .  this is the suggested way .

You also have to use the proper data base handling module.

<IMG src="/home/mhc/Pictures/Screenshots/Screenshot%20From%202025-07-04%2011-27-27.png"> </IMG>

(Click to enlarge)

Thanks for your answer. But the error is not in the syntax (because it works fine if executed directly) nor in the module used. Several other routines for accessing the PostgreSQL database already exist.

In short, why does the same query works in the console but give this error in the IDE?

 Several other routines for accessing the PostgreSQL database already exist.

So why not use those routines ?

works in the console

what is the console ??

 What is mGlobal.rs and mGlobal.db when that line is executed? IOW has the result and the connection been established? I see no other problems with your SQL statement.
b

Add this to your sub before any other code in that sub


and watch the console for the output. You can comment it out when debugging isn't needed.

thatbruce said

What is mGlobal.rs and mGlobal.db when that line is executed? IOW has the result and the connection been established? I see no other problems with your SQL statement.
b

The connection to PostgreSQL is initially established in a login window.
After successful login, a main window is loaded. And several SQL queries are successfully executed.
This has been working fine for quite some time and is still ok.
I have now added this new SQL query and it is the only one that returns this error that makes no sense to me.
Also, if I execute this same SQL query manually in connection1 within the project, it works without error. The error only occurs when the program is running.

It seems that the structure of THIS SQL query is supported in connection1 but, when the program is running, another control is used and this one does not support the structure of THIS SQL query well.

 Grayghost, The other routines I mentioned are other SQL queries that perform other tasks and are working fine, only THIS SQL query returns this error.

sholzy: I'll try to use debug in this sub. I'll only be able to do this on Monday

Debug did not show anything. Just the same error message appeared.
But this is a bug for sure, because the SQL statement returns the right values with no errors.
I tested it by not doing the math in the query but in the following way:

.rs = .db.Exec("Select numero,data_venda,prazo,cliente from vendas Where faturado IS FALSE And setor ='Laudos'

If Format(DateAdd(.rs!data_venda, gb.Day, (.rs!prazo - 2)), "ddmmyy") = Format(Now, "ddmmyy") Then

And no errors….

But if I put the calculations inside the SQL statement, the recordset returns that same error (a 'void' value).

 Did you look at the values of all the variables … I think debug show them,   are they all properly initialize it this sub?

monteiro said

Debug did not show anything. Just the same error message appeared.
But this is a bug for sure, because the SQL statement returns the right values with no errors.
I tested it by not doing the math in the query but in the following way:

.rs = .db.Exec("Select numero,data_venda,prazo,cliente from vendas Where faturado IS FALSE And setor ='Laudos'

If Format(DateAdd(.rs!data_venda, gb.Day, (.rs!prazo - 2)), "ddmmyy") = Format(Now, "ddmmyy") Then

And no errors….

But if I put the calculations inside the SQL statement, the recordset returns that same error (a 'void' value).

You say you are on 3.20.
1. What exact version of the 3.20 series?
2. Are you using gb.db2 component or gb.db?

There have been bugs in 3.20 in gb.db2 component as discovered in this topic  :arrow: https://forum.gambas.one/viewtopic.php?t=1981
Somewhere past halfway into the topic a gb,db2 bug seemed to be the cause. This was in Gambas 3.20.2.
So, maybe you're still on the buggy 3.20 version?

 My version is 3.20.4
In it gb.db appears as 'deprecated' and so I'm using gb.db2
I'll check out this topic you mentioned, but I think there must be some minor bugs left.

 Only one guess…
the IS NOT NULL in your original post is the only thing I could see which may have been incorrectly substituted.
Did using db.debug=true not print the actual SQL that was used?
b

p.s. This could well be a gb.db2 bug as I have previously reported errors in this area.

 Looks like the error occurs before the variables are filled..
The PostgreSQL server is returning the values correctly.
gb.db2 doesn't seem to be interpreting the data correctly when performing calculations involving dates.
When I perform these calculations within Gambas, they work correctly.

monteiro said

Looks like the error occurs before the variables are filled..
The PostgreSQL server is returning the values correctly.
gb.db2 doesn't seem to be interpreting the data correctly when performing calculations involving dates.
When I perform these calculations within Gambas, they work correctly.

Maybe it is time to report it as a bug on the bugtracker  :arrow: https://gambaswiki.org/bugtracker