Insert query in base macro

[jimlo9]

I have this:

Sub backup_checks
DIM oDatabaseContext
DIM oDatasource
DIM oConnection
oDatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDatasource = oDatabaseContext.getByName("checksjdbc")
oConnection = oDatasource.GetConnection("","")
oStatement = oConnection.CreateStatement()

DIM oForm as Object
Dim mynum As Integer

stSql = "INSERT INTO `checkbacks` (`checkid`, `transdate`, `transdescribe`, `widthdraw`, `deposit`, `isclr`, `runsum`)
	SELECT `checkid`, `transdate`, `transdescribe`, `widthdraw`,
	 `deposit`, `isclr`, `runsum` FROM `checks`
	 WHERE `isclr` = 1"


oStatement.executeUpdate(stSql)
oConnection.close
End Sub

Keep getting this error:

BASIC syntax error.
Expected: ".

It’s mysql jdbc connection. Any ideas what’s wrong?

[Ratslinger]

Hello,
Unlike some other languages, Star Basic does not know your statement is continued on the next line unless you specify it. Also each line must begin and end with quotes. The ampersand it to add more to the string and the underscore indicates a continuation. So add &_ to the end of each of the first three lines of your SQL and each of the four lines must begin and end with quotes. SQL needed:

stSql = "INSERT INTO `checkbacks` (`checkid`, `transdate`, `transdescribe`, `widthdraw`, `deposit`, `isclr`, `runsum`) " &_
	"SELECT `checkid`, `transdate`, `transdescribe`, `widthdraw`, " &_
	 "`deposit`, `isclr`, `runsum` FROM `checks` " &_
	 "WHERE `isclr` = 1"

[jimlo9]

@ Ratslinger thanks, I changed to:

Sub backupchecks

DIM oDatabaseContext
DIM oDatasource
DIM oConnection
oDatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDatasource = oDatabaseContext.getByName("checksjdbc")
oConnection = oDatasource.GetConnection("root","zzzzzz")
oStatement = oConnection.CreateStatement()




stSql = "INSERT INTO `checkbook`.`checkbacks` (`checkid`, `transdate`, `transdescribe`, `widthdraw`, `deposit`, `isclr`, `runsum`) SELECT `checks`.`checkid`, `checks`.`transdate`, `checks`.`transdescribe`, `checks`.`widthdraw`, `checks`.`deposit`, `checks`.`isclr`, `checks`.`runsum` FROM `checkbook`.`checks` WHERE `checks`.`isclr` = ?"
mynum = 1
	 
oStatement = oConnection.prepareStatement(stSql)
oStatement.setInt(1, mynum)


oStatement.executeUpdate
oConnection.close

End Sub

Now works perfect.