IDE error running postgreSQL

[monteiro]

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

[grayghost4]

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

[monteiro]

With mGlobal
  .rs=.db.exec("SELECT numero, data_venda, cliente, setor, prazo,(data_venda + (prazo * INTERVAL '1 day') - INTERVAL '2 days') AS final_calculated_date From vendas Where prazo IS NOT NULL And setor = 'Laudos'  And faturado IS FALSE  AND ((data_venda + (prazo * INTERVAL '1 day') - INTERVAL '2 days') = CURRENT_DATE + INTERVAL '2 days');") ' <-- this line produces the error message
 ' more code
End With

[grayghost4]

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 :

' Gambas module file

'' Module file to handle SQLite file 
'' SQLite  file handeling subroutines 
'' writen by  GrayGhost4  Marvin Clavey  June 2022   modified  through  2025

Private RS As Result
Private sadata As String[]
Private $con As New Connection

Public Sub GetFileData(sfilename As String, scolumnName As String) As String[]
   
   With $con
      Try .Close()             ' Close the connection. The try allows it to fail without error
      .Type = "sqlite3"        ' Defines the type of connection
      .Host = Application.path ' Host will be the path where the sqlite db file is
      .Name = "data.SQL"  '  database name is the name of the database file"
      Try .Open()                  ' We activate and open the connection, the try is to allow an error
      If Error Then Print "Cannot Open Database. Error = "; Error.Text
      RS = .Exec("Select * From " & sfilename) 
   End With 
   
Return RS.All(scolumnName)   ' return all the data from one column 
   
End

Public Sub GetRecordData(sFileName As String, sFileColumn As String, sFileIndex As String) As String[]
   
   Dim saInfo As New String[]    ' must have "NEW" or you will get a null error with .add 
   Dim obj As ResultField
   
   RS = $con.Exec("Select * From" & Quote(sFileName) & "where" & Quote(sFileColumn) & "like" & Quote(sFileIndex))


   For Each obj In RS.Fields
      sainfo.Add(RS[obj.Name])
   Next 
   Return saInfo
   
End 

Public Sub UpdateFileData(sFileName As String, sFileIndex As String, saInfo As String[])
   
   Dim saCommand As String   
   Dim obj As ResultField
   
   saInfo = saInfo.Reverse()
   For Each obj In RS.Fields                'assemble the update command "column" = "data"
      saCommand &= Quote(obj.Name) & " = " & Quote(saInfo.Pop()) & ","
   Next 
   saCommand = Left(saCommand, -1)        ' remove the last ","  from saCommand string
   $con.Exec("update" & Quote(sFileName) & "set" & saCommand & "where accname = " & Quote(sFileIndex))
   
End 
'

Public Sub UpdateFileData(sFileName As String, sFileIndex As String, saInfo As String[])
   
   Dim saCommand As String   
   Dim obj As ResultField
   
   saInfo = saInfo.Reverse()
   For Each obj In RS.Fields                'assemble the update command "column" = "data"
      saCommand &= Quote(obj.Name) & " = " & Quote(saInfo.Pop()) & ","
   Next 
   saCommand = Left(saCommand, -1)        ' remove the last ","  from saCommand string
   $con.Exec("update" & Quote(sFileName) & "set" & saCommand & "where accname = " & Quote(sFileIndex))
   
End 

Public Sub InsertNewRecord(sFileName As String)
   
   Dim sNewRecord As String
   
   For Each obj As Object In RS.Fields    ' This assenbles the File column names it to A string
      sNewRecord &= Quote(obj.Name) & "," ' to insert into a blank record to be edited later 
   Next 
   sNewRecord = Left(sNewRecord, -1)         'remove the last ","  from the string 
   sNewRecord = Replace(sNewRecord, "accname", "newAcc" & Str(Rand(20, 100)))  ' replace first field (random) for index
   $con.Exec("INSERT INTO " & Quote(sFileName) & " VALUES (" & sNewRecord & ")")
   
End

Public Sub deleatOneRecord(sFileName As String, sFileField As String, sFileIndex As String)

      $con.Exec(DB.Subst("delete from [&1] where  '&2'  =  &3 ", sFileName, sFileField, sFileIndex))

End

Public Sub InsertNewRecord(sFileName As String)
   
   Dim sNewRecord As String
   
   For Each obj As Object In RS.Fields    ' This assenbles the File column names it to A string
      sNewRecord &= Quote(obj.Name) & "," ' to insert into a blank record to be edited later 
   Next 
   sNewRecord = Left(sNewRecord, -1)         'remove the last ","  from the string 
   sNewRecord = Replace(sNewRecord, "accname", "newAcc" & Str(Rand(20, 100)))  ' replace first field (random) for index
   $con.Exec("INSERT INTO " & Quote(sFileName) & " VALUES (" & sNewRecord & ")")
   
End

Public Sub deleatOneRecord(sFileName As String, sFileField As String, sFileIndex As String)

      $con.Exec(DB.Subst("delete from [&1] where  '&2'  =  &3 ", sFileName, sFileField, sFileIndex))

End

[grayghost4]

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

      $con.Exec(DB.Subst("delete from [&1] where  '&2'  =  &3 ", sFileName, sFileField, sFileIndex))

[grayghost4]

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

[monteiro]

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?

[grayghost4]

Several other routines for accessing the PostgreSQL database already exist.

So why not use those routines ?

works in the console

what is the console ??

[thatbruce]

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

[sholzy]

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

Code: Select all

db.Debug = True

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