Macros BASIC Checks CONSTRAINT

[lonk]

Create table (Firebird):
create table "AlternativeCode" ("ID" bigint not null, "AlternativeCode" varchar(100) not null, "Code1" varchar(2) not null, "Code2" varchar(2) not null, "Code3" varchar(2) not null, "Code4" varchar(2) not null, "Code5" varchar(2) not null, "Code6" varchar(2) not null, constraint "ConstraintID" primary key("ID"), constraint "ConstraintAlternativeCode1To6" unique("AlternativeCode", "Code1", "Code2", "Code3", "Code4", "Code5", "Code6"))

Existing data:

How can Macros BASIC get the event of error from trying to append repeated rows?
insert into "AlternativeCode" ("AlternativeCode", "Code1", "Code2", "Code3", "Code4", "Code5", "Code6") values ('S-0003', '02', '02', '02', '02', '02', '02')

If violating constraint then : Do something : End If

Or is there only one way, select and count the rowset ?

Learning_Firebird_CONSTRAINT.odb (3.4 KB)

[Ratslinger]

Hello,

First your table does not have the primary key set as auto increment and it is not in the Insert statement. Either add as auto-increment (see:

How to create an auto-increment ID column in a Firebird embedded database table?

for creation SQL) or you need to add data in the Insert SQL.

For the macro, just use a query before the update to see if the item exists already and note error if it does:

Option Explicit
Sub Main
    Dim oStatement As Variant
    Dim sSelectedItem As Variant
    Dim result As Variant
    Dim CursorTest As Variant
    Dim sSQL As String
    if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
       Thisdatabasedocument.CurrentController.connect
    endif
REM Create for SQL
    oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
Rem Data for test
    sSelectedItem = "S-0004"
	sSQL = "SELECT ""AlternativeCode"" FROM ""AlternativeCode"" WHERE ""AlternativeCode"" = '" & sSelectedItem & "'"
	result = oStatement.executeQuery(sSQL)
    CursorTest = result.first
REM Check if selection already exists
    If CursorTest = "False" Then
REM Name does not exist - add to table
        sSQL = "insert into ""AlternativeCode"" (ID, ""AlternativeCode"", ""Code1"", ""Code2"", ""Code3"", ""Code4"", ""Code5"", ""Code6"")" & _
                        " values ('" & "6" & "','" & "S-0004" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "')"
		oStatement.executeUpdate(sSQL)
    else
        Print "Error - violating constraint"
    End If
End Sub

This will insert the record the first time but not the second. Second time also would be a problem for this ID but even with a valid ID the error occurs because of the constraint.

[Ratslinger]

This line:

sSQL = "SELECT ""AlternativeCode"" FROM ""AlternativeCode"" WHERE ""AlternativeCode"" = '" & sSelectedItem & "'"

should include other checks such as:

    sSQL = "SELECT ""AlternativeCode"" FROM ""AlternativeCode"" WHERE ""AlternativeCode"" = '" & sSelectedItem & "'" & _
           " AND ""Code1"" = '" & x1 & "' AND ""Code2"" = '" & x2 & "' AND ""Code3"" = '" & x3 & "'" & _
           " AND ""Code4"" = '" & x4 & "' AND ""Code5"" = '" & x5 & "' AND ""Code6"" = '" & x6 & "'"

Reference this post → How to have a concatenation of string columns as unique constraint in embedded Firebird

[lonk]

@Ratslinger

sSQL = "SELECT ""AlternativeCode"" FROM ""AlternativeCode"" WHERE ""AlternativeCode"" = '" & sSelectedItem & "'" & _
           " AND ""Code1"" = '" & x1 & "' AND ""Code2"" = '" & x2 & "' AND ""Code3"" = '" & x3 & "'" & _
           " AND ""Code4"" = '" & x4 & "' AND ""Code5"" = '" & x5 & "' AND ""Code6"" = '" & x6 & "'"

Thank you so much

[Villeroy]

A form has a form error event. For some reason it is called twice. Uncomment the MRI calls and find out by yourself.

Sub FormError(e)
	'mri = createUnoService( "mytools.Mri" )
	frm = e.Source
	if frm.ImplementationName <> "org.openoffice.comp.svx.FormController" then exit sub
	'mri.inspect(frm)
	er = e.Reason
	ern = er.NextException
	s1 = er.Message
	s2 = ern.Message
	Msgbox s1 &" | "& s2, 16, "Macro: SQLError"
	n = com.sun.star.form.runtime.FormFeature.UndoRecordChanges '= 9
	frm.FormOperations.execute(n)
End Sub