Unique Constraint Failed...

Databases
1

I am attempting to import data into an SQLite database I created.

Here is the table DDL

CREATE TABLE MASTER ( 
   ID_ADDRESS    TEXT NOT NULL,
   firstNAME_1   TEXT COLLATE NOCASE DEFAULT '',
   lastNAME_1    TEXT COLLATE NOCASE DEFAULT '',
   firstNAME_2   TEXT COLLATE NOCASE DEFAULT '',
   lastNAME_2    TEXT COLLATE NOCASE DEFAULT '',
   streetADDRESS TEXT COLLATE NOCASE DEFAULT '',
   City          TEXT COLLATE NOCASE DEFAULT '',
   State         TEXT COLLATE NOCASE DEFAULT '',
   ZipCode       TEXT COLLATE NOCASE DEFAULT '',
   Country       TEXT COLLATE NOCASE DEFAULT '',
   Phone         TEXT COLLATE NOCASE DEFAULT '',
   Email         TEXT COLLATE NOCASE DEFAULT '',
   SELECTED      INTEGER NOT NULL DEFAULT 1,
   SUSPENDED     INTEGER NOT NULL DEFAULT 0,
   PRIMARY KEY ( ID_ADDRESS) )

here is the routine that does an INSERT or UPDATE [right now I am only attempting INSERTS]

Public Sub addMaster_Record(key as string, address_input() as string)
  // Add or Update the Master Record 
  Dim i As Integer
  Dim SQL As String
  Dim rs As RecordSet
  Dim ps As SQLitePreparedStatement
  //
  If SQL_MASTER_INSERT="" Then create_MASTER_SQL  // creates template SQL statement, since it never changes
  //
  If key="*" Then key=GenerateUUID
  SQL= _
  "SELECT count(*) as CNT "+_
  "  FROM "+tableMASTER+_
  " WHERE ID_ADDRESS='"+key+"'"
  rs=db.SQLSelect(SQL)
  
  If rs.field("cnt").integervalue=0 Then 
    //
    // new record
    //
    SQL=SQL_MASTER_INSERT
    //
    ps = DB.Prepare(SQL)
    //
    ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
    ps.Bind(0,key)
    debug "Insert with Key "+key
    For i=0 To address_INPUT.Ubound
      ps.BindType(i+1, SQLitePreparedStatement.SQLITE_TEXT)
      ps.Bind(i+1, address_INPUT(i))
// address_INPUT(0) maps to Bind[1] hence the +1
    Next i
  Else
    // Update record [not yet implemented]
  End If
  //
  Call ps.SQLExecute(sql)
  debug db.ErrorMessage
End Sub

SQL_MASTER_INSERT statement is this

INSERT INTO MASTER(ID_ADDRESS,firstNAME_1,lastNAME_1,firstNAME_2,lastNAME_2,streetADDRESS,City,State,ZipCode,Country,Phone,Email) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)

the error I get is UNIQUE constraint failed: MASTER.ID_ADDRESS
which is the PK, and I understand it must be unique… the problem is that it IS unique

I have in the past been accused of not supplying enough information, so forgive me if I went overboard this time

2

WHOA… I just looked in the database… and somehow it is inserting the TEXT of the SQL statement as the First Argument (which is why the constriant falied, it would have been fine if it used the UUID like it should)

but I don’t see how that is happening :frowning:

3 
  Call ps.SQLExecute(sql)

has no params following it for the prepared statement to use for the positional params
it should

4

as I was eating Dinner I realized that… but whats the point of BIND then? Especially when there could be cases where the number of params is dynamic?

5

Ah… instead of

 Call ps.SQLExecute(sql)

it should be

 Call ps.SQLExecute