Date-format in SQL INSERT

hcl-bot · December 21, 2005, 10:03am

I have to export Notes data to a SQL-table I use LotusScript. Some fields in the SQL-table are defined as datetime (length:8).I use qry.SQL=“INSERT INTO table1(date) values ('”+date+“')”.

The second date is a Lotus Script String-variable with the string-value “2006-31-12 00:00:00”

When I execute the query I get an error: ODBC could not complete the requested operation.

When I use a hard-coded datetime it works:

qry.SQL=“INSERT INTO table1(date) values (‘2006-31-12 00:00:00’)”.

When I use a Notes DateTime variable I get a type mismatch when compiling the script.

I have to use variables because the date is not a constant.

hcl-bot · December 21, 2005, 10:07am

Subject: RE: Date-format in SQL INSERT

Have you tried to split string construction from Execute statement?

’ build my string

reqSQL = “insert blablabla” + date + “blabalbal”

’ check if string is ok

msgbox reqSQL

’ go!

c.Execute(reqSQL)

hcl-bot · December 21, 2005, 10:31am

Subject: RE: Date-format in SQL INSERT

Sorry, No lucky day today!

Thanks

hcl-bot · December 21, 2005, 10:48am

Subject: RE: Date-format in SQL INSERT

What does this mean? What was the result of your test? What is your new source code? “No lucky day” gives no additional information.

There is no problem with assigning the SQL property from an expression that includes variables. You’re calculating the wrong value to assign.

If your variable is really called Date, then I can see what your trouble is; Date is not a legal variable name. It’s not a string variable; it’s a system function. You can tell when you’ve used a reserved word, because the Lotusphere IDE capitalizes them for you and displays them in a different color than your variable names.

The reason you ran into this difficulty, was from failure to use Option Declare. You just used the word Date$ and assumed you were creating a string variable. If you had tried to declare this variable, you would have gotten a syntax error. This is a further clue that you’re using a reserved word.

hcl-bot · December 21, 2005, 12:38pm

Subject: RE: Date-format in SQL INSERT

Lotusphere IDE . . . got Lotusphere on the brain, eh? :)dgg

hcl-bot · December 21, 2005, 4:27pm

Subject: Naturally…

hcl-bot · December 22, 2005, 2:31am

Subject: RE: Date-format in SQL INSERT

My variablenames are called validfromstring and validtostring. These are string-variables. I also have two NotesDateTime variables, called validfrom and validto. In debugger I can see that these are correct assigned values.validtostring and validfromstring are set by validto(DateOnly) and validfrom(dateOnly).

I am using the stringvariables in my SQL-statement.

I tried several suggestions in this thread (the meaning of: no lucky day, means: did not solve the problem).

I don’t believ I have problems with LS-variables for I use LS-debugger to test my code.

When I hard-code the date in the SQL-statement, like

blablabla values(‘2006-12-31 00:00:00’) the record is written to the SQL-table. But when I replace this date/time by a stringvariable it goes wrong.

I added a Messagebox to print the sql-statement and the date is the same as when I hard-coded the date: ‘2006-12-31 00:00:00’. Still it goes wrong.

hcl-bot · December 23, 2005, 9:10am

Subject: RE: Date-format in SQL INSERT

There is no problem with assigning SQL statement from a variable instead of a constant string. The problem is something so obvious that you’re overlooking it. But because you don’t POST YOUR CODE, nobody can help you see this obvious thing.

hcl-bot · January 2, 2006, 3:01am

Subject: Problem setting Date in SQL-table

I agree with you: no problems with assigning variables, except dates as a variable into a SQL-table.

Here is a part of my code.

I use the ODBC-connection with the ODBC-driver “SQL Server”.

This configuration seems to work fine, for when I use AddRow I have no problems. But, as commented in my code, I prefer to use proper SQL-statements.

Sub Initialize

REM declaraties

On Error Goto Errhandler3

Print "Agent new prices started at "+Time$

Dim s As New NotesSession

Dim db As NotesDatabase

Set db=s.CurrentDatabase

Dim profdoc As NotesDocument	'document voorkeuren (BE)

Set profdoc=db.GetProfileDocument("voorkeuren")

Dim validfrom As NotesDateTime, validto As NotesDateTime

Dim validfromstring As String, validtostring As String



Dim con As New ODBCConnection	'Make ODBC Connection

If Not con.ConnectTo("DEVprices","********","********") Then

	Print "Could not connect to DEVprices"

	Exit Sub

End If

Dim qry As New ODBCQuery	'Define ODBC Query

Set qry.Connection = con



Dim result As New ODBCResultSet	'Set Query property of ODBCResultSet

Dim RetCode As Integer

Dim waarde As String, fomsexist As String



result.CacheLimit=DB_NONE

	

Dim prijslijst As String, artcode As String, tariefjaarstr As String

Dim tariefjaar As Integer

REM Set priceyear

tariefjaarstr=profdoc.nieuwtariefjaar(0)

REM tariefjaarstr=“2006”

tariefjaar=Cint(tariefjaarstr)

validfromstring="1-01-"+Cstr(tariefjaar)

validtostring="31-12-"+Cstr(tariefjaar)

Set validfrom=s.CreateDateTime(validfromstring)

Set validto=s.CreateDateTime(validtostring)

REM Add new records

REM ddoc and plijstdoc are set in previous art of the code, but now deleted

REM because this variables has nothing todo with my date-problem

Dim bedrag As String, prijs83 As String

REM the next two lines are to test setting the date in SQL

		validfromstring=Cstr(tariefjaar)+"-01-01 00:00:00"

		validtostring=Cstr(tariefjaar)+"-31-12 00:00:00"

		prijslijst=ddoc.ProjectNr(0)

		artcode=ddoc.GetItemValue("artcode1")(0)

		prijs83=ddoc.GetItemValue("bedr1_1")(0)

		Set result.Query=qry

REM The following line generates an error

		sqlstr="INSERT INTO staffl(prijslijst, artcode, prijs83, bedr1, validfrom, validto,)  values ('"+prijslijst+"', '"+artcode+"','"+prijs83+"','"+prijs83+"', '"+validfromstring+"', '"+validtostring+"')"

REM also tried following options, regarding to the responses in the IBM-forum

REM The following line generates an error

‘sqlstr="INSERT INTO staffl(prijslijst, artcode, prijs83, bedr1, validfrom, validto,) values (’“+prijslijst+”‘, ‘“+artcode+”’,’“+prijs83+”‘,’“+prijs83+”', ‘“+Cstr(validfromstring)+”’, ‘“+Cstr(validtostring)+”’)"

REM The following line is the only which works, but the year-part of the date has the be a variable (the current year)

‘sqlstr="INSERT INTO staffl(prijslijst, artcode, prijs83, bedr1, validfrom, validto,) values (’“+prijslijst+”‘, ‘“+artcode+”’,’“+prijs83+”‘,’“+prijs83+”', ‘2006-01-01 00:00:00’,‘2006-12-31 00:00:00’)"

			Messagebox sqlstr

			qry.SQL=sqlstr

			result.Execute

REM The following code works, but I prefered to use proper SQL-statements.

%REM

			result.AddRow

			With result

				Call .SetValue("prijslijst",prijslijst)

				Call .SetValue("artcode",plijstdoc.GetItemValue("artcode"+Cstr(rij))(0))

				Call .SetValue("validfrom",plijstdoc.validfrom(0))

				Call .SetValue("validto",plijstdoc.validto(0))

			End With

			result.UpdateRow

%END REM

result.Close(DB_CLOSE)

con.Disconnect

Exit Sub

Errhandler3:

Print "An Error " & Error$ & "( " & Err & ") has occurred on line " & Erl & " in ExportAgent voor dossier. "&Cstr(prijslijst)

result.CurrentRow=currow		'na fout terug naar resultset

’ Resume Continue

Exit Sub

End Sub

hcl-bot · January 2, 2006, 3:32am

Subject: RE: Problem setting Date in SQL-table

Try taking a look at the actual values of the strings validfromstring and validtostring. Your working hard-coded example is pumping in a texted date string in the formay “yyyy-mm-dd hh:mm:ss”. Your two constructed strings are in this format: “d-mm-yyyy-dd-mm hh:mm:ss”.

hcl-bot · January 2, 2006, 3:57am

Subject: RE: Problem setting Date in SQL-table

You are right, BUT, some lines later the validfromstring and validtostring are again assigned a value in the format “yyyy-mm- etc”. (The lines are:validfromstring=Cstr(tariefjaar)+“-01-01 00:00:00”

validtostring=Cstr(tariefjaar)+“-31-12 00:00:00”

)

This “double” assigning is because I tested with several possibilities. When the code works, I delete one of the assignments.

hcl-bot · January 2, 2006, 4:33am

Subject: RE: Problem setting Date in SQL-table

Wow, that’s messed up. It’s a wonder you ever get anything working at all. Try commenting out the crap at least – I’d hate to have to debug or maintain your code.

hcl-bot · January 2, 2006, 5:05am

Subject: RE: Problem setting Date in SQL-table

Oke,

Here is the stripped code:

Thanks for giving me attention!

Sub Initialize

REM declaraties

On Error Goto Errhandler3

Dim s As New NotesSession

Dim db As NotesDatabase

Set db=s.CurrentDatabase

Dim profdoc As NotesDocument	'document voorkeuren (BE)

Set profdoc=db.GetProfileDocument("voorkeuren")

Dim validfrom As NotesDateTime, validto As NotesDateTime

Dim validfromstring As String, validtostring As String, tariefjaarstr As String

Dim prijslijst As String, artcode As String

Dim tariefjaar As Integer

Dim bedrag As String, prijs83 As String	





Dim con As New ODBCConnection	'Make ODBC Connection

Dim result As New ODBCResultSet	'Set Query property of ODBCResultSet

If Not con.ConnectTo("DEVprices","********","********") Then

	Print "Could not connect to DEVprices"

	Exit Sub

End If

Dim qry As New ODBCQuery	'Define ODBC Query

Set qry.Connection = con

			

result.CacheLimit=DB_NONE

Set result.Query=qry

REM Set priceyear

tariefjaarstr="2006"	'In real life this value is getted from a field in a Notes profiledocument

tariefjaar=Cint(tariefjaarstr)

validfromstring=Cstr(tariefjaar)+"-01-01 00:00:00"

validtostring=Cstr(tariefjaar)+"-31-12 00:00:00"

Set validfrom=s.CreateDateTime(validfromstring)

Set validto=s.CreateDateTime(validtostring)

REM Add new records

REM ddoc and plijstdoc are set in previous art of the code, but now deleted

REM because this variables has nothing todo with my date-problem

REM the next two lines are to test setting the date in SQL

		prijslijst=ddoc.ProjectNr(0)

		artcode=ddoc.GetItemValue("artcode1")(0)

		prijs83=ddoc.GetItemValue("bedr1_1")(0)

REM The following line generates an error

		sqlstr="INSERT INTO staffl(prijslijst, artcode, prijs83, bedr1, validfrom, validto,)  values ('"+prijslijst+"', '"+artcode+"','"+prijs83+"','"+prijs83+"', '"+validfromstring+"', '"+validtostring+"')"

REM also tried following options, regarding to the responses in the IBM-forum

REM The following line generates an error

‘sqlstr="INSERT INTO staffl(prijslijst, artcode, prijs83, bedr1, validfrom, validto,) values (’“+prijslijst+”‘, ‘“+artcode+”’,’“+prijs83+”‘,’“+prijs83+”', ‘“+Cstr(validfromstring)+”’, ‘“+Cstr(validtostring)+”’)"

REM The following line is the only which works, but the year-part of the date has the be a variable (the current year)

‘sqlstr="INSERT INTO staffl(prijslijst, artcode, prijs83, bedr1, validfrom, validto,) values (’“+prijslijst+”‘, ‘“+artcode+”’,’“+prijs83+”‘,’“+prijs83+”', ‘2006-01-01 00:00:00’,‘2006-12-31 00:00:00’)"

			Messagebox sqlstr

			qry.SQL=sqlstr

			result.Execute

REM The following code works, but I prefered to use proper SQL-statements.

%REM

			result.AddRow

			With result

				Call .SetValue("prijslijst",prijslijst)

				Call .SetValue("artcode",plijstdoc.GetItemValue("artcode1")

				Call .SetValue("validfrom",plijstdoc.validfrom1(0))

				Call .SetValue("validto",plijstdoc.validto1(0))

			End With

			result.UpdateRow

%END REM

result.Close(DB_CLOSE)

con.Disconnect

Exit Sub

Errhandler3:

Print "An Error " & Error$ & "( " & Err & ") has occurred on line " & Erl & " in ExportAgent voor dossier. "&Cstr(prijslijst)

Exit Sub

End Sub

hcl-bot · January 2, 2006, 2:33am

Subject: RE: Date-format in SQL INSERT

Oke,I will post the relevant part of my code.

Be patient, please, and continue to help me!

Thanks.

hcl-bot · December 21, 2005, 10:09am

Subject: RE: Date-format in SQL INSERT

No, I did not try but I will.Thanks

hcl-bot · December 21, 2005, 12:26pm

Subject: Date-format in SQL INSERT

See my post here ->. To insert a date, you can probably just use the “date” function in the SQL statement itself:strSQL$ = “INSERT INTO tabel1”

strSQL$ = strSQL$ & " (pricelist, artcode, quantity, dateadded)"

strSQL$ = strSQL$ & " VALUES (?pricelst?, ?acode?, ?qty?, DATE(?dateadded?))"

qry.SQL= strSQL$

hth,

dgg

hcl-bot · January 2, 2006, 7:32am

Subject: SOLVED !!!

Using dateformat in Lotus Script string-variable in the format mm/dd/yyyy works!So, when using the proper SQL-statement INSERT INTO table etc. I can write into SQL date-fields when the LS-variable is a string in the format mm/dd/yyyy.

The strange thing, however, is: When using LS ODBC classes AddRow and ResultSet.SetValue, I can use string variables in the format dd-mm-yyyy to write into SQL date-fields.

(See - as seperator or /).

Koos van Harmelen

hcl-bot · December 21, 2005, 10:09am

Subject: RE: Date-format in SQL INSERT

Try this…

qry.SQL=“INSERT INTO table1(date) values ('”+ Cstr(date) +“')”

If still cannot, try placing a messagebox right after the above statement

Msgbox date

Cheers,

Teck Lung

hcl-bot · December 21, 2005, 10:17am

Subject: RE: Date-format in SQL INSERT

Sorry, No luck.

Thanks