Syntax of sql.QRY=INSERT(" in LS

HCL Domino Domino Forum
1

I have problems with addings rows into a QSL-table with proper SQL-statements from LotusScript.I believe the problem is caused by the use of constants and variables in the SQL-statement.

Part of code:

pricelist=doc.getitemvalue(“pricelistUK”)

art=doc.getitemvalue(“artcodeUK”)

price=doc.getitemvalue(“priceuk”)

set result.Query=qry

qry.SQL=“INSERT INTO tabel1(pricelist, artcode, quantity) values (|& pricelist &|,|& art &|,|& price &|, ‘1’)”

result.Execute

The error is: ODBC could not complete the requested operation.

I also used:

qry.SQL=“INSERT INTO tabel1(pricelist, artcode, quantity) values ('”& pricelist &“‘,’”& art &“‘,’”& price &“', ‘1’)”

When I replace the variables with constants, like:

qry.SQL=“INSERT INTO tabel1(pricelist, artcode, quantity) values (‘200008’, ‘ZZMAAT’, ‘208’, ‘1’)”

the record is added to the SQL-table.

So, the question is: how can I use variables in this sQL Insert-statement?

2

Subject: RE: Syntax of sql.QRY=INSERT(" in LS

pricelist=doc.getitemvalue(“pricelistUK”)> art=doc.getitemvalue(“artcodeUK”)

price=doc.getitemvalue(“priceuk”)

I think those lines are messy…

You get a table instead of a string type.

Add an Option Explicit in agent option section.

Strong type your variable

Dim pricelist as string

pricelist=doc.getitemvalue(“pricelistUK”)(0)

This will get the first value in the pricelistUK field. Notice the (0).

Does it solve the problem?

3

Subject: RE: Syntax of sql.QRY=INSERT(" in LS

I get a part of the solution.Your last response is due to my incomplete question. I had cut off my code; I know that a fieldvalue is an array and I have to get the 0-index of the array.

Sorry.

But I get the insert-statement working for 80%, on this way:

qry.SQL=“INSERT INTO tabel1(pricelist, artcode, quantity) values ('”+pricelist+“', '”+art+“‘,’”+price+“',‘1’)”.

So + instead of &.

But now I have still one problem: The SQL-table has two fields which are defined as date-time values.

In my LS-code I create also two date-time variables: validfrom and validto. I create them with the following code:

DIM priceyearstring as String, validfromstring As String

DIM validtostring As String

DIM validfrom as NotesDateTime, validto As NotesDateTime

priceyearstring=“2006” '(this becomes a value from a profiledocument)

validfromstring=“1-01-”+priceyearstring

validtostring=“31-12-”+priceyearstring

Set validfrom=s.CreateDateTime(validfromstring)

Set validto=s.CreateDateTime(validtostring)

In debugger I see that the last two values became data/time values.

In my SQL INSERT-statement I use the values of the variables validfromstring and validtostring for the date-time fields in the SQL-table. Then I get the same ODBC-error again.

When I replace in my SQL INSERT-code validfromstring by validfrom I get a Type Mismatch.

So I have to push a LS date/time variable into date/time-fields in the SQL-table.

Thanks for your help.

4

Subject: RE: Syntax of sql.QRY=INSERT(" in LS

Check the format of the datetime. I think string type should be ok to send datetime with ODBC.

Ex : Insert into something(date1) values (‘YYYY-MM-DD 00:00:00’)

ODBC datetime reference document from MS : ODBC Datetime Format | Microsoft Learn

5

Subject: Syntax of sql.QRY=INSERT(" in LS

If this is a query that will be repeatedly executed you’ll save a lot of overhead (and possibly avoid the whole problem in the process) by using a prepared statement. For example:

pricelist = doc.getitemvalue(“pricelistUK”)

art = doc.getitemvalue(“artcodeUK”)

price = doc.getitemvalue(“priceuk”)

set result.Query = qry

qry.SQL=“INSERT INTO tabel1(pricelist, artcode, quantity) VALUES (?pricelst?, ?acode?, ?qty?)”

Call result.SetParameter(1, Cstr(pricelist(0)))

Call result.SetParameter(2, Cstr(art(0)))

Call result.SetParameter(3, Cstr(price(0)))

Call result.Execute()

hth,

dgg

6

Subject: RE: Syntax of sql.QRY=INSERT(" in LS

qry.SQL=“INSERT INTO tabel1(pricelist, artcode, quantity) values (‘|& pricelist &|’,‘|& art &|’,‘|& price &|’, ‘1’)”

Missing single quote?

7

Subject: RE: Syntax of sql.QRY=INSERT(" in LS

Thanks for your reply.

I am afraid your suggestion does not work. I get the same error.

8

Subject: forget the SQL part of the problem

do it in two steps, first put your string together and have a look at it, then submit the query. I don’t think that the problem has anything to do with SQL, just to do with getting the quotes in the right places in the string.

tmp=“INSERT INTO tabel1(pricelist, artcode, quantity) values (|& pricelist &|,|& art &|,|& price &|, ‘1’)”

print tmp

qry.SQL=tmp

I would think that replacing the outer double quotes with vertical bars would be what you want to do, and perhaps extra single quotes round the values e.g.

tmp=|INSERT INTO tabel1(pricelist, artcode, quantity) values (‘|& pricelist &|’,‘|& art &|’,‘|& price &|’, ‘1’)|

9

Subject: RE: Syntax of sql.QRY=INSERT(" in LS

Thanks for your replies.The INSERT-syntax is solved.

But I can’t solve the date-problem.

The format is OKE. Hard-coded a string in the INSERT-qry works, but not when a string-variable is used.

I searched this forum about this issue but I could not found something useful.

I think I will post a new question about the date-problem.

Thanks