PS prepared statement not working

Databases
1

Hi!

today I’m getting a strange behaviour.
I would like to read a csv file and, after checking if the record is present on a Sql table, i would like to insert it in another table.
The strange behaviour is that the prepared statements in this method does not work at all!
It cannot delete the record if present (first statement) and it not create it if not (second statement)

If I launch the Sql commands in query editor they works, what could be???

PS tried also

    ps=db.Prepare("delete from Inventario_X3 where ubicazione='M05'")

without success

Dim db As New MSSQLServerDatabase

db.Host =Icam_inventario.host // or just the IP if using the default instance
db.DatabaseName = Icam_inventario.dbnm
db.UserName = Icam_inventario.user  // or "Domain\UserID for trusted domain accounts
db.Password = Icam_inventario.pass

If db.Connect Then
  'msgbox("connesso")
  Dim f As FolderItem
  Dim textInput As TextInputStream
  
  Dim nomefile,rowFromFile As String
  dim rowid,articolo,lotto,qtaril,descrizione_estesa,um,sessioneriga,esiste as String
  
  Dim stringa_sql as string
  Dim ps As MSSQLServerPreparedStatement
  Dim rs As RecordSet 
  dim prog as integer
  
  Dim tab(-1) As String //= ";" 'ChrB(9)
  
  nomefile = Rilevazione_Dati.SpUbicazione.text.Trim+".csv"
  f = GetFolderItem(nomefile) 
  
  If f <> Nil And f.Exists Then
    //elimino quanto presente
    ps=db.Prepare("delete from Inventario_X3 where ubicazione=?")
    'ps=db.Prepare("delete from Inventario_X3 where ubicazione='M05'")
    ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
    ps.Bind(0,SpUbicazione.text)
    ps.SQLExecute
    break
    
    //leggo il file
    textInput = TextInputStream.Open(f)
    textInput.Encoding = Encodings.UTF8 //strings are UTF8
    prog=0
    While Not textInput.EOF
      esiste="N"
      rowFromFile = textInput.ReadLine
      tab=Split(rowFromFile,";")
      articolo = if(tab(0).Trim.Len>0,tab(0),"") 
      lotto =  if(tab(1).Trim.Len>0,tab(1),"") 
      qtaril =  if(tab(2).Trim.Len>0,tab(2),"0")
      articolo = articolo.Trim 
      lotto = lotto.trim
      qtaril = qtaril.trim
      prog=prog+1
      'break
      
      stringa_sql="select [CUNLISDET].ROWID,[CUNLISDET].ITMREF_0,[CUNLISDET].LOT_0,[ITMMASTER].YDESEST_0, "
      stringa_sql=stringa_sql+"[CUNLISDET].PCU_0,[CUNLISDET].CUNLISNUM_0 FROM [x3]."+icam_inventario.dossier+".[CUNLISDET] " 
      stringa_sql=stringa_sql+"inner join [x3]."+icam_inventario.dossier+".[ITMMASTER] on [CUNLISDET].ITMREF_0 = [ITMMASTER].ITMREF_0 "
      stringa_sql=stringa_sql+"where [CUNLISDET].CUNLISSTA_0<3 and [CUNLISDET].LOC_0='"+SpUbicazione.Text+"' "
      stringa_sql=stringa_sql+"and [CUNLISDET].ITMREF_0='"+articolo+"' and [CUNLISDET].LOT_0='"+lotto+"'"
      
      'break
      ps = db.Prepare(stringa_sql)
      rs = ps.SQLSelect
      
      if rs <> Nil Then
        While Not rs.EOF
          esiste="S"
          rowid=rs.IdxField(1).StringValue.defineEncoding(encodings.SystemDefault)
          descrizione_estesa=rs.IdxField(4).StringValue.defineEncoding(encodings.SystemDefault)
          um=rs.IdxField(5).StringValue.defineEncoding(encodings.SystemDefault)
          sessioneriga=rs.IdxField(6).StringValue.defineEncoding(encodings.SystemDefault)
          rs.MoveNext
        Wend
        rs.Close
      end if
      
      //se esiste nella lista mettiamo la giacenza
      If esiste="S" then
        ps=db.Prepare("insert into Inventario_X3 (sessione_testa,sessione_righe,ubicazione,codice_articolo,descrizione_estesa,um_x3,lotto_articolo,qta_rilevata,rowid,valore,tipo_riga) VALUES (?,?,?,?,?,?,?,?,?,?,?);")
        
        ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
        ps.BindType(1, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
        ps.BindType(2, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
        ps.BindType(3, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
        ps.BindType(4, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
        ps.BindType(5, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
        ps.BindType(6, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
        ps.BindType(7, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
        ps.BindType(8, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
        ps.BindType(9, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
        ps.BindType(10, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
        
        ps.Bind(0, LblSessioneTesta.Text)
        ps.Bind(1, sessioneriga)
        ps.Bind(2, SpUbicazione.Text)
        ps.Bind(3, articolo)
        ps.Bind(4, descrizione_estesa)
        ps.Bind(5, um)
        ps.Bind(6, lotto)
        ps.Bind(7, qtaril)
        ps.Bind(8, rowid)
        ps.Bind(9, 0)
        ps.Bind(10, "I")
        'break
        ps.SQLExecute
      else
        //se non esiste creo il nuovo articolo
      end if
      
    Wend
    textInput.Close
    MsgBox("fine import")
    CaricaArticoli
    CaricaNuovi
  else
    MsgBox("File "+nomefile+" inesistente!")
  end if
  db.Close
Else
  MsgBox("Connection error:" + db.ErrorMessage)
End If
2

Your prepared statement needs to use ? in place of the actual value, like this:

Dim ps As MSSQLServerPreparedStatement
Dim rs As RecordSet
ps=db.Prepare("delete from Inventario_X3 where ubicazione=?")
ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.Bind(0,SpUbicazione.text)
rs = ps.SQLExecute

Also make sure the BindType matches the data type (#9 value is 0, so use .MSSQLSERVER_TYPE_INT)

See the sample projects for examples. Consider using API2, using Rowset instead of Recordset. This uses prepared statements automatically (continue to use ? in the SQL, but you don’t need to declare a PreparedStatement or use the Bind or BindType calls.

1 Like
3

hi!

ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)

because is the first parameter ( the first ? in the delete statement) and is a string

tried also to put the string value directly in the sql syntax but does not work!

    ps=db.Prepare("delete from Inventario_X3 where ubicazione='M05'")
4

Your code should check to see if there is a reported error after executing the statements.

If there isn’t, try issuing a Commit at the end.

5

How can I check?

6 
if db.Error then
  MessageBox db.ErrorMessage // .... or something
end if
7

No error at all! :frowning:

but it doesn’t do the operation on the db (delete and insert)…

I tried to put in a separate method only the delete and it does not work

what does it mean?

8

Maybe i found the problem, the system administrator has changed db user rights…

I’ll check after testing and give you feedback

thx

9

In that case, you should have seen an error.

Did you try issuing a Commit?

10

I’m seeing this too.
Commit doesn’t work.
if I submit the query on the db’s method, not the prepared statement, it works fine.
Help…