I want to do something like:
var query = "SELECT * FROM table WHERE row ="+datefield;
I have played with all kinds of formats including a timestamp like: “yyyy-MM-dd HH:mm:ss.ttt” but I don’t get this done..
Who can help me?
Assuming Row is a date field, you could try the following:
var query = “SELECT * FROM table WHERE to_char(row,‘yyyyMMdd’) =”+datefield;
Basically, you format both the data that you want to comapre the same way
Paul
Paul thanks for the tip but…
Sorry, it doesn’t work (yet). The result is an ‘Error loading primary key date, null’ message with and without formatting the datefield itself…
Will try a little more tomorrow…
Marcel, I forgot one important part… comma’s…
statement should be:
var query = “SELECT * FROM table WHERE to_char(row,‘yyyyMMdd’) =”‘“”+datefield+"’";
The to_char function makes a string of the date i the DB, so on the other side of the equation you also need the string.
Further, here’s some code to get the results of a query into HTML format, which you can then put into a HTML type field and view the result. Might tell you some more about the reason why it’s not working:
var Input = …
var HTML = ‘
var ColumnCount = Input.getMaxColumnIndex()
//get header info
for( var i = 1 ; i <= ColumnCount ; i++ )
{
var ColumnHeader = Input.getColumnName(i)
HTML += ‘’
}
HTML += ‘’
//Get Record info
for( var i2 = 1 ; i2 <= Input.getMaxRowIndex() ; i2++ )
{
Input.rowIndex = i2;
HTML += ‘’;
for( var i = 1 ; i <= ColumnCount ; i++ )
{
var ColumnDetail = Input
- HTML += ‘
}
HTML += ‘
| ’+ColumnHeader+‘ |
|---|
| ’+ColumnDetail+‘ |
Whatever the result of your query, name it “Input” and the result of this code is var HTML…
Paul
Paul, the part of the hanging comma’s I knew so don’t be ashamed…
But, it doesn’t do the trick… Have you used the code since I don’t get either of the three examples below to work:
var query = "SELECT calendarid FROM calendar WHERE to_char(startdatetime,'yyyyMMdd')="+globals.Date;
or
var query = "SELECT calendarid FROM calendar WHERE to_char(startdatetime,'yyyyMMdd')='"+globals.Date+"'";
or
var query = "SELECT calendarid FROM calendar WHERE to_char(startdatetime,'yyyyMMdd')='"+utils.dateFormat(globals.Date, "yyyyMMdd")+"'";
Can this be since the date is really a timestamp…
Marcel, Assuming you work with the standard FireBird DB, I tried the to_char part directly onto the DB. didn’t work…
So, now I’m clueless as well. did a quick search in the internet, but didn’t find any comments on dates in firebird sql…
sorry,
Paul
No sweat. I have the ‘standard’ use of controller.find() but don’t like that. This is the alternative but I think I have to work with a timestamp kind of thing although this didn’t give me the correct result in hardcode either. Otherwise I would have written mij own it2be_tools.stampFormat() function
use if possible
databaseManager.getDataSetByQuery('example_data''SELECT * FROM table WHERE dateColumn = ?',new Array(startdatetime),200)
to still have database independend code (‘to_char’ is NOT availeble in all dbs)
Jan,
This is what I do:
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),'SELECT calendarid FROM calendar WHERE startdatetime = ?', new Array(globals.newDateTime), 200)
controller.loadRecords(dataset);
The result is: Error loading primary key data, null
Where do I go wrong?
BTW is this giving me a query on only the date part of it all?
It looks like sybase accepts this in all cases:
query += " AND startdatetime>='"+utils.dateFormat(globals.datetime, "yyyy-MM-dd 00:00:00.000")+"' AND startdatetime<='"+utils.dateFormat(globals.datetime, "yyyy-MM-dd 23:59:59:999")+"'";
And it works fine!
What did the .log.txt tells you when the query failed?
And the winning answer is:
queryForRelatedFoundSet: 24580 1 13198774 useCache: true
used sql select recurrances, organizationid, audioalarmaudiocontent, transparancy, mailalarmruntime, creator, alarminterval, mailalarmnote, displayalarmsnoozetime, displayalarmrepeatcount, procedurealarmrepeatcount, lettersid, procedurealarmprocedurename, duedatetime, audioalarmsnoozetime, globalpositioning, procedurealarmruntime, resources, procedurealarmsnoozetime, calendarid, startdatetime, priority, mailalarmsnoozetime, classification, projectsid, calculationsid, exceptionrule, activity, completed, enddatetime, status, recurrancerule, daylight, audioalarmrepeatcount, documentopen, documentsid, invoicesid, modifier, displayalarmdisplaystring, note, recurrancedatetimes, creation, contactid, modification, summary, alarmintervaltype, calendartype, sequence, mailalarmrepeatcount, displayalarmruntime, timezone, categories, ordersid, attendee, location, offersid, relatedto, exceptiondatetime, self, audioalarmruntime, mailalarmemailvalue, subject, statusdate, statushost, statususer, username from calendar where calendar.self = ? order by calendar.calendarid
questiondata[0]= 1
-----------Data query for Cache not needed… (query done for pk)
used sql SELECT calendarid FROM calendar WHERE calendartype<>‘uren’ AND (classification=‘public’ OR (classification=‘private’ AND username=‘TrapM’)) AND startdatetime>=‘2004-04-12 00:00:00.000’ AND startdatetime<=‘2004-04-19 23:59:59:999’ ORDER BY status ASC, startdatetime ASC
used sql SELECT calendarid FROM calendar WHERE startdatetime = ?
questiondata[0]= Mon Apr 12 00:00:00 CEST 2004
questiondata[1]= Mon Apr 19 00:00:00 CEST 2004
java.sql.SQLException: JZ0SB: Parameter index out of range: 2.
java.sql.SQLException: JZ0SB: Parameter index out of range: 2.
at com.sybase.jdbc2.jdbc.ErrorMessage.raiseError(ErrorMessage.java:487)
at com.sybase.jdbc2.jdbc.ParamManager.checkIndex(ParamManager.java:262)
at com.sybase.jdbc2.jdbc.ParamManager.setParam(ParamManager.java:312)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.setParam(SybPreparedStatement.java:981)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.setParam(SybPreparedStatement.java:974)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.setObject(SybPreparedStatement.java:570)
at sun.reflect.GeneratedMethodAccessor147.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.servoy.j2db.persistence.datasource.n.invoke(Unknown Source)
at $Proxy1.setObject(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:229)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:229)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:229)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.performCustomQuery(Unknown Source)
at com.servoy.j2db.dataprocessing.ak.a(Unknown Source)
at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_getDataSetByQuery(Unknown Source)
at sun.reflect.GeneratedMethodAccessor193.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:289)
at org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1232)
at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1940)
at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:94)
at com.servoy.j2db.scripting.e.call(Unknown Source)
at com.servoy.j2db.develop.debugger.l.a(Unknown Source)
at com.servoy.j2db.develop.debugger.l.access$14(Unknown Source)
at com.servoy.j2db.develop.debugger.l$3.run(Unknown Source)
at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:178)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:454)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:234)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:184)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:178)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:170)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)
com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: JZ0SB: Parameter index out of range: 2. JZ0SB: Parameter index out of range: 2.
com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: JZ0SB: Parameter index out of range: 2. JZ0SB: Parameter index out of range: 2.
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.performCustomQuery(Unknown Source)
at com.servoy.j2db.dataprocessing.ak.a(Unknown Source)
at com.servoy.j2db.dataprocessing.JSDatabaseManager.js_getDataSetByQuery(Unknown Source)
at sun.reflect.GeneratedMethodAccessor193.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:289)
at org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1232)
at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1940)
at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:94)
at com.servoy.j2db.scripting.e.call(Unknown Source)
at com.servoy.j2db.develop.debugger.l.a(Unknown Source)
at com.servoy.j2db.develop.debugger.l.access$14(Unknown Source)
at com.servoy.j2db.develop.debugger.l$3.run(Unknown Source)
at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:178)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:454)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:234)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:184)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:178)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:170)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)
java.lang.NullPointerException
java.lang.NullPointerException
at com.servoy.j2db.dataprocessing.FoundSet.loadExternalPKList(Unknown Source)
at com.servoy.j2db.FormPanel.a(Unknown Source)
at com.servoy.j2db.FormPanel$JSForm.js_loadRecords(Unknown Source)
at inv5.invoke()
at org.mozilla.javascript.FunctionObject.doInvoke(FunctionObject.java:537)
at org.mozilla.javascript.FunctionObject.call(FunctionObject.java:453)
at org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1232)
at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:1940)
at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:94)
at com.servoy.j2db.scripting.e.call(Unknown Source)
at com.servoy.j2db.develop.debugger.l.a(Unknown Source)
at com.servoy.j2db.develop.debugger.l.access$14(Unknown Source)
at com.servoy.j2db.develop.debugger.l$3.run(Unknown Source)
at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:178)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:454)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:234)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:184)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:178)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:170)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)
size of dialog32 java.awt.Point[x=419,y=430]
size of dialog32 java.awt.Point[x=419,y=430]
used sql SELECT calendarid FROM calendar WHERE startdatetime = ?
questiondata[0]= Mon Apr 12 00:00:00 CEST 2004
questiondata[1]= Mon Apr 19 00:00:00 CEST 2004
java.sql.SQLException: JZ0SB: Parameter index out of range: 2.
It seems to me you are passing an array with 2 arguments where you only have one ‘?’ in your SQL, which is a developer error.
Some enhances are made to handle this error nicer, will be availeble in Servoy 2.0 rc12
Jan, you are right about that one but also passing just one date creates an error.
I just sent a test solution to Johan (per his request)…
A problem that was already there is 2004 and I still don’t get this work.
I do exactly what is written in this post, but still getting errors
FATAL: Setting dataprovider with name ‘invoice_date’, type ‘DATETIME’ with value of wrong type 01/01/1992 00:00:00…21/10/2009 01:00:00 |dd/MM/yyyy HH:mm:ss
It looks like that this problem occurs when using the utils.dateFormat(field, format) where the field is a calender field
if (globals.search_date_01 && globals.search_date_02)
invoice_date = utils.dateFormat(globals.search_date_01, 'dd/MM/yyyy HH:mm:ss') + '...' + utils.dateFormat(globals.search_date_02, 'dd/MM/yyyy HH:mm:ss') + '|dd/MM/yyyy HH:mm:ss'
globals.search_date_01 was filled manually with 01/01/1992
globals.search_date_02 was filled using calender popup and today was selected
I really don’t see what is wrong, but as far as I’ve checked it has something to do with the time.
These dates are giving me really lot of troubles. It is not the first time I have troubles with dates
Martin
Are you using the code as shown in find/search ?
Hi Martin,
I somehow recall that when you are using the formatting criteria (|dd/MM/yyyy etc.) you need to prefix the whole text with a hash.
Like so:
if (globals.search_date_01 && globals.search_date_02)
invoice_date = "#" + utils.dateFormat(globals.search_date_01, 'dd/MM/yyyy HH:mm:ss') + '...' + utils.dateFormat(globals.search_date_02, 'dd/MM/yyyy HH:mm:ss') + '|dd/MM/yyyy HH:mm:ss'
Hope this helps.
Jan Blok:
Are you using the code as shown in find/search ?
Yes of course
It is between the find (which results true) and the search
Well you are definitly not in find…this error is only possible to get, in normal edit
try the code as:
if (controller.find() && globals.search_date_01 && globals.search_date_02)
{
invoice_date = “#” + utils.dateFormat(globals.search_date_01, ‘dd/MM/yyyy HH:mm:ss’) + ‘…’ + utils.dateFormat(globals.search_date_02, ‘dd/MM/yyyy HH:mm:ss’) + ‘|dd/MM/yyyy HH:mm:ss’
controller.search()
}
You are completely right Jan.
I was in findmode, but on a certain foundset and not on the controller
For hours I was looking for a problem that was triggered by some other problem.
My original coding was:
_ok = _foundset.find()
if (! _ok)
return;
invoice_date = utils.dateFormat(globals.search_date_01, 'dd/MM/yyyy HH:mm:ss') + '...' + utils.dateFormat(globals.search_date_02, 'dd/MM/yyyy HH:mm:ss') + '|dd/MM/yyyy HH:mm:ss'
_foundset.search(false, true)
And it should have been
_ok = _foundset.find()
if (! _ok)
return;
_foundset.invoice_date = utils.dateFormat(globals.search_date_01, 'dd/MM/yyyy HH:mm:ss') + '...' + utils.dateFormat(globals.search_date_02, 'dd/MM/yyyy HH:mm:ss') + '|dd/MM/yyyy HH:mm:ss'
_foundset.search(false, true)
I was completely looking somewhere else. It is because you mentioned controller.find() that I found what was wrong.
Looks like time for vacation again!
I need some rest.