I have found on clarion newsgroup the following:
I have a table in each that includes the usual DateTime definition in
the Clarion dct:
DateTimeSTR STRING(8)
DateTimeGRP GROUP,OVER(DateTimeSTR)
DateField DATE
TimeField TIME
END
I will try if it works in c2j.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Currently timestamp using technique of OVER() is not supported. In my coding, I define separate DATE and TIME postgres types.
I've looked at source code for runtime. I do not think that adding support for the way clarion does this will be difficult. Let me know if you need this and I will take a look.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I would be very gratefull if you could this. This column is defined by specs as datetime. I have to finish this project in about 30 days. So there is no immediate rush. I will though ask the refeence person in the bank to ask if they could soften the specs.
Thanks
Nenad
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I've just added datetime support to c2j, as per how it is done in clarion; using string(8) and OVER() with a group containing a date and time.
Code is in runtime. I also added a test to compiler to ensure that datetime files actually compile and behave as expected when written as clarion code. compiler changes also include something you pointed out to me some time ago. CODE and DATA blocks are now more relaxed about whitespace preceeding them.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
timetamp works as above. I have tried to add a row and the postgre column type and value is OK. When reading back the data is ok.
What is not working is when i add the timestamp column in a key.
For example:
blagajnicke_transakcije FILE,DRIVER('ODBC'),OWNER('wmj'),NAME('blagajnicke_transakcije'),PRE(bt),CREATE,BINDABLE,THREAD
KeyBT_datum_broj KEY(bt:tec_tl_datum_tecajne_liste,bt:tec_tl_broj_tecajne_liste),NOCASE,OPT
Record RECORD,PRE()
serijski_broj STRING(10)
datum_i_vrijeme_transakcije STRING(8)
datum_i_vrijeme_transkacijeG GROUP,OVER(datum_i_vrijeme_transakcije)
datum DATE
vrijeme TIME
END
tec_tl_broj_tecajne_liste LONG
tec_tl_datum_tecajne_liste DATE
END
END
If i add at the end of the key the field datum_i_vrijeme_transakcije c2j compiles but crashes at runtime.
If i add at the end datum_i_vrijeme_transakcijeG or datum_i_vrijeme_transakcijeG.datum,datum_i_vrijeme_transakcijeG.vrijeme then c2j will not compile.
Also if it was possible to add the datum_i_vrijeme_transakcije to the key, for the purpose of PREVIOUS command i have the need to do something as clear(datum_i_vrijeme_transakcije ,1). Would that work? Would work maybe
clear(datum_i_vrijeme_transakcijeG.datum,1)
clear(datum_i_vrijeme_transakcijeG.vrijeme,1)
?
Now this is not strictly a showstopper, since i can duplicate the date,time columns just for the purpose of the key definition. Just wanted to point to the problem.
I will submit one more report in the help forum, which is more important. I feel i am close to the end with bugs or feature requests since I have tried all the functionality I need and I will stick with this solution.
Thanks
Nenad
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Putting a key on the string component (datum_i_vrijeme_transakcije) should work.
Make sure you remove NOCASE. This will confuse c2j and cause SQL operations to not work on date key.
Code in regression test works fine.
program
Franch FILE,DRIVER('ODBC'),OWNER('c8'),NAME('testdate'),PRE(PSI),BINDABLE
PrimaryKey KEY(PSI:ID),NAME('pkey'),PRIMARY
dateKEY KEY(PSI:timeStampField),DUP,NAME('datekey'),OPT
Record RECORD,PRE()
id LONG,NAME('id')
timeStampField string(8),NAME('timeStampField')
timeStampGroup group,over(timeStampField)
date long
time long
.
END
END
code
create(franch)
open(franch)
assert(errorcode()=0,'#1')
psi:id=1
psi:timeStampGroup.date=today()
psi:timeStampGroup.time=clock()
add(franch)
clear(franch)
assert(psi:timeStampGroup.date=0,'#2')
assert(psi:timeStampGroup.time=0,'#3')
set(psi:dateKEY)
next(franch)
assert(psi:timeStampGroup.date=today(),'#4')
assert(abs(psi:timeStampGroup.time-clock())<200,'#5')
next(franch)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have taken off the nocase and now i do not get any more runtime crashes. But it is behaving inconsistently .
With blagajnicke_transakcije if i have the following situations defined then I can not add any row.
Situation 1:Just one primary key with just bt:datum_i_vrijeme_transakcije
Situation 2:Jst one primary key as
KEY(bt:tec_tl_datum_tecajne_liste,bt:tec_tl_broj_tecajne_liste,bt:datum_i_vrijeme_transakcije),DUP,OPT,,PRIMARY
Situation 3: Two keys
KeyBT_datum_broj KEY(bt:tec_tl_datum_tecajne_liste,bt:tec_tl_broj_tecajne_liste),NOCASE,OPT,PRIMARY
KeyBT_datTrans KEY(bt:tec_tl_datum_tecajne_liste,bt:tec_tl_broj_tecajne_liste,bt:datum_i_vrijeme_transakcije),DUP,OPT
Only when i have the following keys I can add rows.
KeyBT_datum_broj KEY(bt:tec_tl_datum_tecajne_liste,bt:tec_tl_broj_tecajne_liste),NOCASE,OPT,PRIMARY
KeyBT_datTrans KEY(bt:datum_i_vrijeme_transakcije),DUP,OPT
May I ask why you have NAME('datekey'), i.e. seems as you are forcing lowcase on keys but not on fields; for example NAME('timeStampField'). Is this necessary?
Thanks
Nenad
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
* postgres is case insensitive for labels. So NAME('datekey') and NAME('dateKey') are the same thing. It is just what I happened to type when writing the code
* Postgres index collation is locale specific, and generally upper and lower cases collate very close together. i.e. in postgres: i.e.
SELECT 'a'<'b' = true
SELECT 'a'<'B' = true
Whereas clarion .DAT files would collate based on ascii code. So the above 'a'<'B' = false for DAT files. The postgres method is far more powerful and useful, (but sometimes annoying if you need to anticipate collation order in application, I recently wrote some code that had to do this). I find that notion of case insensitive keys for c2j + postgres is rarely useful. Suggest remove NOCASE. Only situation where it is required is if you wish to force constraints relating to case sensitivity.
* OPT is currently not implemented in c2j. I think this may case problems. If you have OPT set, but not DUP; you may be trying to manage multiple empty keys. In such a situation, add DUP setting. If you need OPT and DUP to work properly, let me know. When I originally wrote SQL layer for c2j, I was unsure how to build in OPT+NODUP behaviour. But now I know how to go about it. But work involved is fiddly; I might not get to it until next week.
In meantime there is a test you can try: manually modify your postgres index. For example consider an index I have on my system:
"teststock_partkey_1" UNIQUE, btree (franchise, partnum)
franchise = smallint, partnum=varchar
Redefining the index thus will help (to make it non DUP and OPT):
CREATE UNIQUE INDEX teststock_partkey_1 ON teststock (franchise,partnum) WHERE franchise>0 or partnum>'';
Note that with above in place, performance of c2j indexes will be poor with the above until I make necessary changes. To work around this you may need to define two indexes, one that is essentially the constraint, and another which is for browsing/performance.
CREATE UNIQUE INDEX teststock_partkey_constraint ON teststock (franchise,partnum) WHERE franchise>0 or partnum>'';
CREATE INDEX teststock_partkey_constraint ON teststock (franchise,partnum);
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have managed to narrow down. I have the following debug code immediatelly after the "Initialising database" message in the main frame.
! clear(blagajnicke_transakcije)
! bt:tec_tl_datum_tecajne_liste=date(1,2,2014)
! bt:tec_tl_broj_tecajne_liste=1
! clear(bt:datum_i_vrijeme_transkacijeG.datum,1)
! clear(bt:datum_i_vrijeme_transkacijeG.vrijeme,1)
! set(bt:KeyBT_datum_broj_trans,bt:KeyBT_datum_broj_trans)
! loop
! previous(blagajnicke_transakcije)
! if errorcode() or (not bt:tec_tl_datum_tecajne_liste=date(1,2,2014)) or (not bt:tec_tl_broj_tecajne_liste=1)
! break
! end
! if jj=0
! jj=bt:tec_tl_broj_tecajne_liste
! end
! message(format(bt:datum_i_vrijeme_transkacijeG.datum,@d6) & ' ' & format(bt:datum_i_vrijeme_transkacijeG.vrijeme,@t4))
! end
clear(blagajnicke_transakcije)
bt:tec_tl_broj_tecajne_liste=1
bt:tec_tl_datum_tecajne_liste=date(1,2,2014)
bt:datum_i_vrijeme_transkacijeG.datum=today()
bt:datum_i_vrijeme_transkacijeG.vrijeme=clock()
add(blagajnicke_transakcije)
if errorcode()
message('error')
end
At init time the table is empty.
If I put in any of the keys the datum_i_vrijeme_transakcije, and if i uncomment the commented bloc of above (previous(blagajnicke…) then the ADD fails, even if bt:KeyBT_datum_broj_trans does not contain the timestamp column.
But if I comment the code, as i did above then i can put the timestamp column in the main primary key.
Btw, i took away OPT on all keys.
Does this make sense? Note that in your progression test you first add one row and then you get it back with NEXT.
Nenad
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Can you get log output using Debug(). It might be possible that the previous() is failing. Unlikely, but not impossible. I would like to rule it out.
Also - on ADD() good to dump the exact error you are getting. I am expecting either duplicate key error or a transaction already aborted error. If latter, it implies that the previous fetch code is broken in some way. Possibly table/index creation is somehow broken.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This is the file:
blagajnicke_transakcije FILE,DRIVER('ODBC'),OWNER('wmj'),NAME('blagajnicke_transakcije'),PRE(bt),CREATE,BINDABLE,THREAD
KeyBT_datum_broj KEY(bt:tec_tl_datum_tecajne_liste,bt:tec_tl_broj_tecajne_liste,bt:datum_i_vrijeme_transakcije),PRIMARY
Record RECORD,PRE()
serijski_broj STRING(10)
datum_i_vrijeme_transakcije STRING(8)
datum_i_vrijeme_transkacijeG GROUP,OVER(datum_i_vrijeme_transakcije)
datum DATE
vrijeme TIME
END
tec_tl_broj_tecajne_liste LONG
tec_tl_datum_tecajne_liste DATE
END
END
This is the code:
compile('java-end',java)
@java-dependency 'java.util.logging.Level'
@java-dependency 'java.util.logging.Logger'
@java-code 'Logger log = Logger.getLogger(Thiswindow.class.getName());'
@java-code 'log.log(Level.WARNING,"Nenad1",thisWindow);'
java-end
clear(blagajnicke_transakcije)
bt:tec_tl_datum_tecajne_liste=date(1,2,2014)
bt:tec_tl_broj_tecajne_liste=1
clear(bt:datum_i_vrijeme_transkacijeG.datum,1)
clear(bt:datum_i_vrijeme_transkacijeG.vrijeme,1)
set(bt:KeyBT_datum_broj,bt:KeyBT_datum_broj)
loop
previous(blagajnicke_transakcije)
if errorcode() or (not bt:tec_tl_datum_tecajne_liste=date(1,2,2014)) or (not bt:tec_tl_broj_tecajne_liste=1)
break
end
if jj=0
jj=bt:tec_tl_broj_tecajne_liste
end
message(format(bt:datum_i_vrijeme_transkacijeG.datum,@d6) & ' ' & format(bt:datum_i_vrijeme_transkacijeG.vrijeme,@t4))
end
clear(blagajnicke_transakcije)
bt:tec_tl_broj_tecajne_liste=1
bt:tec_tl_datum_tecajne_liste=date(1,2,2014)
bt:datum_i_vrijeme_transkacijeG.datum=today()
bt:datum_i_vrijeme_transkacijeG.vrijeme=clock()
add(blagajnicke_transakcije)
if errorcode()
message(error())
compile('java-end',java)
@java-code 'log.log(Level.WARNING,"Nenad:" + Clarion.newString(CError.error()),thisWindow);'
java-end
end
The code is being called after
SETCURSOR(Cursor:Wait)
OPEN(SQLOpenWindow)
ACCEPT
IF EVENT() = Event:Timer
Relate:apoen.SetOpenRelated()
Relate:apoen.Open ! File blagajnicke_transakcije used by this procedure, so make sure it's RelationManager is open
Relate:blagajnicke_transakcije.Open ! File blagajnicke_transakcije used by this procedure, so make sure it's RelationManager is open
Relate:blagajnicko_mjesto.Open ! File blagajnicke_transakcije used by this procedure, so make sure it's RelationManager is open
Relate:osoba.Open ! File blagajnicke_transakcije used by this procedure, so make sure it's RelationManager is open
Relate:vrsta_transakcije.Open ! File blagajnicke_transakcije used by this procedure, so make sure it's RelationManager is open
Access:tecajna_lista.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:vrsta_mjenjackog_tecaja.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:valuta.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:vrsta_cek.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:gloparam.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:platni_instrumenti.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:kodirani_tecajevi.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:provizija.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:tecajevi.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
SELF.FilesOpened = True
POST(EVENT:CloseWindow)
END
END
CLOSE(SQLOpenWindow)
SETCURSOR()
and before opening the frame.
This is finally the log:
12/20 07:31:18 W Wmj001 - Nenad1
12/20 07:31:18 F ClarionSQLFile - SELECT serijski_broj,datum_i_vrijeme_transakcije,tec_tl_broj_tecajne_liste,tec_tl_datum_tecajne_liste FROM blagajnicke_transakcije WHERE ((tec_tl_datum_tecajne_liste IS NULL OR tec_tl_datum_tecajne_liste<='2014-01-02') AND ((tec_tl_datum_tecajne_liste IS NULL OR tec_tl_datum_tecajne_liste<'2014-01-02') OR (tec_tl_broj_tecajne_liste<=1 AND (tec_tl_broj_tecajne_liste<1 OR (datum_i_vrijeme_transakcije<='1411-07-08 65:13:56'))))) ORDER BY tec_tl_datum_tecajne_liste DESC NULLS LAST,tec_tl_broj_tecajne_liste DESC,datum_i_vrijeme_transakcije DESC NULLS LAST []
12/20 07:31:18 W ClarionSQLFile - SQLException : ERROR: date/time field value out of range: "1411-07-08 65:13:56"
Position: 404 22008
org.postgresql.util.PSQLException: ERROR: date/time field value out of range: "1411-07-08 65:13:56"
Position: 404
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
at org.jclarion.clarion.ClarionSQLFile.iterate(ClarionSQLFile.java:531)
at org.jclarion.clarion.ClarionSQLFile.previous(ClarionSQLFile.java:420)
at clarion.Wmj001.main_SetDef(Wmj001.java:174)
at clarion.Thiswindow.init_SetDef(Thiswindow.java:102)
at clarion.Thiswindow.init(Thiswindow.java:89)
at clarion.Windowmanager.run(Windowmanager.java:489)
at clarion.Wmj001.main(Wmj001.java:50)
at clarion.Main.begin(Main.java:347)
at clarion.Main.main(Main.java:331)
12/20 07:31:18 F ClarionSQLFile - INSERT INTO blagajnicke_transakcije (serijski_broj,datum_i_vrijeme_transakcije,tec_tl_broj_tecajne_liste,tec_tl_datum_tecajne_liste) VALUES ('','2011-12-20 07:31:18',1,'2014-01-02') []
12/20 07:31:18 W ClarionSQLFile - SQLException : ERROR: current transaction is aborted, commands ignored until end of transaction block 25P02
org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:345)
at org.jclarion.clarion.ClarionSQLFile.add(ClarionSQLFile.java:118)
at clarion.Wmj001.main_SetDef(Wmj001.java:188)
at clarion.Thiswindow.init_SetDef(Thiswindow.java:102)
at clarion.Thiswindow.init(Thiswindow.java:89)
at clarion.Windowmanager.run(Windowmanager.java:489)
at clarion.Wmj001.main(Wmj001.java:50)
at clarion.Main.begin(Main.java:347)
at clarion.Main.main(Main.java:331)
12/20 07:31:18 F runtime.CWinImpl - Target is null on thread:Thread
12/20 07:31:24 W Wmj001 - Nenad:ERROR: current transaction is aborted, commands ignored until end of transaction block
12/20 07:31:24 W Wmj001 - Nenad2
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Details of the problem. Relates to clear(1) and set(key,key)
Date field first. clear(1) sets it to 2^31-1 days since 28th Dec 1800. Or 5881411-07-08. Currently c2j trims this to 4 year date or : 1411-07-08. This is a bug, c2j should represent this correctly.
For time: again clear(1) sets it to 2^31-1. Which c2j treats as (2^31-2)/100 seconds since midnight. Or
5965:13:56; again c2j trims numbers; so it becomes 65:13:56.
I will fix these shortly. So that clear() when performed on a DATE or TIME variable, set limits as you would expect.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sorry, again me.
I have the need to use datetime column in database. I think i need timestamp in postgre. How should i define the dictionary?
Nenad
I have found on clarion newsgroup the following:
I have a table in each that includes the usual DateTime definition in
the Clarion dct:
DateTimeSTR STRING(8)
DateTimeGRP GROUP,OVER(DateTimeSTR)
DateField DATE
TimeField TIME
END
I will try if it works in c2j.
Hi
Currently timestamp using technique of OVER() is not supported. In my coding, I define separate DATE and TIME postgres types.
I've looked at source code for runtime. I do not think that adding support for the way clarion does this will be difficult. Let me know if you need this and I will take a look.
I would be very gratefull if you could this. This column is defined by specs as datetime. I have to finish this project in about 30 days. So there is no immediate rush. I will though ask the refeence person in the bank to ask if they could soften the specs.
Thanks
Nenad
I got a quick response from the bank. I can format the colum as yyyymmddhhmmss so there is no need for DATETIME:)
I've just added datetime support to c2j, as per how it is done in clarion; using string(8) and OVER() with a group containing a date and time.
Code is in runtime. I also added a test to compiler to ensure that datetime files actually compile and behave as expected when written as clarion code. compiler changes also include something you pointed out to me some time ago. CODE and DATA blocks are now more relaxed about whitespace preceeding them.
OK, thanks. CODE,DATA thing works.
timetamp works as above. I have tried to add a row and the postgre column type and value is OK. When reading back the data is ok.
What is not working is when i add the timestamp column in a key.
For example:
blagajnicke_transakcije FILE,DRIVER('ODBC'),OWNER('wmj'),NAME('blagajnicke_transakcije'),PRE(bt),CREATE,BINDABLE,THREAD
KeyBT_datum_broj KEY(bt:tec_tl_datum_tecajne_liste,bt:tec_tl_broj_tecajne_liste),NOCASE,OPT
Record RECORD,PRE()
serijski_broj STRING(10)
datum_i_vrijeme_transakcije STRING(8)
datum_i_vrijeme_transkacijeG GROUP,OVER(datum_i_vrijeme_transakcije)
datum DATE
vrijeme TIME
END
tec_tl_broj_tecajne_liste LONG
tec_tl_datum_tecajne_liste DATE
END
END
If i add at the end of the key the field datum_i_vrijeme_transakcije c2j compiles but crashes at runtime.
If i add at the end datum_i_vrijeme_transakcijeG or datum_i_vrijeme_transakcijeG.datum,datum_i_vrijeme_transakcijeG.vrijeme then c2j will not compile.
Also if it was possible to add the datum_i_vrijeme_transakcije to the key, for the purpose of PREVIOUS command i have the need to do something as clear(datum_i_vrijeme_transakcije ,1). Would that work? Would work maybe
clear(datum_i_vrijeme_transakcijeG.datum,1)
clear(datum_i_vrijeme_transakcijeG.vrijeme,1)
?
Now this is not strictly a showstopper, since i can duplicate the date,time columns just for the purpose of the key definition. Just wanted to point to the problem.
I will submit one more report in the help forum, which is more important. I feel i am close to the end with bugs or feature requests since I have tried all the functionality I need and I will stick with this solution.
Thanks
Nenad
Putting a key on the string component (datum_i_vrijeme_transakcije) should work.
Make sure you remove NOCASE. This will confuse c2j and cause SQL operations to not work on date key.
Code in regression test works fine.
program Franch FILE,DRIVER('ODBC'),OWNER('c8'),NAME('testdate'),PRE(PSI),BINDABLE PrimaryKey KEY(PSI:ID),NAME('pkey'),PRIMARY dateKEY KEY(PSI:timeStampField),DUP,NAME('datekey'),OPT Record RECORD,PRE() id LONG,NAME('id') timeStampField string(8),NAME('timeStampField') timeStampGroup group,over(timeStampField) date long time long . END END code create(franch) open(franch) assert(errorcode()=0,'#1') psi:id=1 psi:timeStampGroup.date=today() psi:timeStampGroup.time=clock() add(franch) clear(franch) assert(psi:timeStampGroup.date=0,'#2') assert(psi:timeStampGroup.time=0,'#3') set(psi:dateKEY) next(franch) assert(psi:timeStampGroup.date=today(),'#4') assert(abs(psi:timeStampGroup.time-clock())<200,'#5') next(franch)I have taken off the nocase and now i do not get any more runtime crashes. But it is behaving inconsistently .
With blagajnicke_transakcije if i have the following situations defined then I can not add any row.
Situation 1:Just one primary key with just bt:datum_i_vrijeme_transakcije
Situation 2:Jst one primary key as
KEY(bt:tec_tl_datum_tecajne_liste,bt:tec_tl_broj_tecajne_liste,bt:datum_i_vrijeme_transakcije),DUP,OPT,,PRIMARY
Situation 3: Two keys
KeyBT_datum_broj KEY(bt:tec_tl_datum_tecajne_liste,bt:tec_tl_broj_tecajne_liste),NOCASE,OPT,PRIMARY
KeyBT_datTrans KEY(bt:tec_tl_datum_tecajne_liste,bt:tec_tl_broj_tecajne_liste,bt:datum_i_vrijeme_transakcije),DUP,OPT
Only when i have the following keys I can add rows.
KeyBT_datum_broj KEY(bt:tec_tl_datum_tecajne_liste,bt:tec_tl_broj_tecajne_liste),NOCASE,OPT,PRIMARY
KeyBT_datTrans KEY(bt:datum_i_vrijeme_transakcije),DUP,OPT
May I ask why you have NAME('datekey'), i.e. seems as you are forcing lowcase on keys but not on fields; for example NAME('timeStampField'). Is this necessary?
Thanks
Nenad
A couple of points:
* postgres is case insensitive for labels. So NAME('datekey') and NAME('dateKey') are the same thing. It is just what I happened to type when writing the code
* Postgres index collation is locale specific, and generally upper and lower cases collate very close together. i.e. in postgres: i.e.
SELECT 'a'<'b' = true
SELECT 'a'<'B' = true
Whereas clarion .DAT files would collate based on ascii code. So the above 'a'<'B' = false for DAT files. The postgres method is far more powerful and useful, (but sometimes annoying if you need to anticipate collation order in application, I recently wrote some code that had to do this). I find that notion of case insensitive keys for c2j + postgres is rarely useful. Suggest remove NOCASE. Only situation where it is required is if you wish to force constraints relating to case sensitivity.
* OPT is currently not implemented in c2j. I think this may case problems. If you have OPT set, but not DUP; you may be trying to manage multiple empty keys. In such a situation, add DUP setting. If you need OPT and DUP to work properly, let me know. When I originally wrote SQL layer for c2j, I was unsure how to build in OPT+NODUP behaviour. But now I know how to go about it. But work involved is fiddly; I might not get to it until next week.
In meantime there is a test you can try: manually modify your postgres index. For example consider an index I have on my system:
"teststock_partkey_1" UNIQUE, btree (franchise, partnum)
franchise = smallint, partnum=varchar
Redefining the index thus will help (to make it non DUP and OPT):
CREATE UNIQUE INDEX teststock_partkey_1 ON teststock (franchise,partnum) WHERE franchise>0 or partnum>'';
Note that with above in place, performance of c2j indexes will be poor with the above until I make necessary changes. To work around this you may need to define two indexes, one that is essentially the constraint, and another which is for browsing/performance.
CREATE UNIQUE INDEX teststock_partkey_constraint ON teststock (franchise,partnum) WHERE franchise>0 or partnum>'';
CREATE INDEX teststock_partkey_constraint ON teststock (franchise,partnum);
I have managed to narrow down. I have the following debug code immediatelly after the "Initialising database" message in the main frame.
! clear(blagajnicke_transakcije)
! bt:tec_tl_datum_tecajne_liste=date(1,2,2014)
! bt:tec_tl_broj_tecajne_liste=1
! clear(bt:datum_i_vrijeme_transkacijeG.datum,1)
! clear(bt:datum_i_vrijeme_transkacijeG.vrijeme,1)
! set(bt:KeyBT_datum_broj_trans,bt:KeyBT_datum_broj_trans)
! loop
! previous(blagajnicke_transakcije)
! if errorcode() or (not bt:tec_tl_datum_tecajne_liste=date(1,2,2014)) or (not bt:tec_tl_broj_tecajne_liste=1)
! break
! end
! if jj=0
! jj=bt:tec_tl_broj_tecajne_liste
! end
! message(format(bt:datum_i_vrijeme_transkacijeG.datum,@d6) & ' ' & format(bt:datum_i_vrijeme_transkacijeG.vrijeme,@t4))
! end
clear(blagajnicke_transakcije)
bt:tec_tl_broj_tecajne_liste=1
bt:tec_tl_datum_tecajne_liste=date(1,2,2014)
bt:datum_i_vrijeme_transkacijeG.datum=today()
bt:datum_i_vrijeme_transkacijeG.vrijeme=clock()
add(blagajnicke_transakcije)
if errorcode()
message('error')
end
At init time the table is empty.
If I put in any of the keys the datum_i_vrijeme_transakcije, and if i uncomment the commented bloc of above (previous(blagajnicke…) then the ADD fails, even if bt:KeyBT_datum_broj_trans does not contain the timestamp column.
But if I comment the code, as i did above then i can put the timestamp column in the main primary key.
Btw, i took away OPT on all keys.
Does this make sense? Note that in your progression test you first add one row and then you get it back with NEXT.
Nenad
Can you get log output using Debug(). It might be possible that the previous() is failing. Unlikely, but not impossible. I would like to rule it out.
Also - on ADD() good to dump the exact error you are getting. I am expecting either duplicate key error or a transaction already aborted error. If latter, it implies that the previous fetch code is broken in some way. Possibly table/index creation is somehow broken.
This is the file:
blagajnicke_transakcije FILE,DRIVER('ODBC'),OWNER('wmj'),NAME('blagajnicke_transakcije'),PRE(bt),CREATE,BINDABLE,THREAD
KeyBT_datum_broj KEY(bt:tec_tl_datum_tecajne_liste,bt:tec_tl_broj_tecajne_liste,bt:datum_i_vrijeme_transakcije),PRIMARY
Record RECORD,PRE()
serijski_broj STRING(10)
datum_i_vrijeme_transakcije STRING(8)
datum_i_vrijeme_transkacijeG GROUP,OVER(datum_i_vrijeme_transakcije)
datum DATE
vrijeme TIME
END
tec_tl_broj_tecajne_liste LONG
tec_tl_datum_tecajne_liste DATE
END
END
This is the code:
compile('java-end',java)
@java-dependency 'java.util.logging.Level'
@java-dependency 'java.util.logging.Logger'
@java-code 'Logger log = Logger.getLogger(Thiswindow.class.getName());'
@java-code 'log.log(Level.WARNING,"Nenad1",thisWindow);'
java-end
clear(blagajnicke_transakcije)
bt:tec_tl_datum_tecajne_liste=date(1,2,2014)
bt:tec_tl_broj_tecajne_liste=1
clear(bt:datum_i_vrijeme_transkacijeG.datum,1)
clear(bt:datum_i_vrijeme_transkacijeG.vrijeme,1)
set(bt:KeyBT_datum_broj,bt:KeyBT_datum_broj)
loop
previous(blagajnicke_transakcije)
if errorcode() or (not bt:tec_tl_datum_tecajne_liste=date(1,2,2014)) or (not bt:tec_tl_broj_tecajne_liste=1)
break
end
if jj=0
jj=bt:tec_tl_broj_tecajne_liste
end
message(format(bt:datum_i_vrijeme_transkacijeG.datum,@d6) & ' ' & format(bt:datum_i_vrijeme_transkacijeG.vrijeme,@t4))
end
clear(blagajnicke_transakcije)
bt:tec_tl_broj_tecajne_liste=1
bt:tec_tl_datum_tecajne_liste=date(1,2,2014)
bt:datum_i_vrijeme_transkacijeG.datum=today()
bt:datum_i_vrijeme_transkacijeG.vrijeme=clock()
add(blagajnicke_transakcije)
if errorcode()
message(error())
compile('java-end',java)
@java-code 'log.log(Level.WARNING,"Nenad:" + Clarion.newString(CError.error()),thisWindow);'
java-end
end
compile('java-end',java)
@java-code 'log.log(Level.WARNING,"Nenad2",thisWindow);'
java-end
The code is being called after
SETCURSOR(Cursor:Wait)
OPEN(SQLOpenWindow)
ACCEPT
IF EVENT() = Event:Timer
Relate:apoen.SetOpenRelated()
Relate:apoen.Open ! File blagajnicke_transakcije used by this procedure, so make sure it's RelationManager is open
Relate:blagajnicke_transakcije.Open ! File blagajnicke_transakcije used by this procedure, so make sure it's RelationManager is open
Relate:blagajnicko_mjesto.Open ! File blagajnicke_transakcije used by this procedure, so make sure it's RelationManager is open
Relate:osoba.Open ! File blagajnicke_transakcije used by this procedure, so make sure it's RelationManager is open
Relate:vrsta_transakcije.Open ! File blagajnicke_transakcije used by this procedure, so make sure it's RelationManager is open
Access:tecajna_lista.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:vrsta_mjenjackog_tecaja.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:valuta.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:vrsta_cek.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:gloparam.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:platni_instrumenti.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:kodirani_tecajevi.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:provizija.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
Access:tecajevi.UseFile ! File referenced in 'Other Files' so need to inform it's FileManager
SELF.FilesOpened = True
POST(EVENT:CloseWindow)
END
END
CLOSE(SQLOpenWindow)
SETCURSOR()
and before opening the frame.
This is finally the log:
12/20 07:31:18 W Wmj001 - Nenad1
12/20 07:31:18 F ClarionSQLFile - SELECT serijski_broj,datum_i_vrijeme_transakcije,tec_tl_broj_tecajne_liste,tec_tl_datum_tecajne_liste FROM blagajnicke_transakcije WHERE ((tec_tl_datum_tecajne_liste IS NULL OR tec_tl_datum_tecajne_liste<='2014-01-02') AND ((tec_tl_datum_tecajne_liste IS NULL OR tec_tl_datum_tecajne_liste<'2014-01-02') OR (tec_tl_broj_tecajne_liste<=1 AND (tec_tl_broj_tecajne_liste<1 OR (datum_i_vrijeme_transakcije<='1411-07-08 65:13:56'))))) ORDER BY tec_tl_datum_tecajne_liste DESC NULLS LAST,tec_tl_broj_tecajne_liste DESC,datum_i_vrijeme_transakcije DESC NULLS LAST []
12/20 07:31:18 W ClarionSQLFile - SQLException : ERROR: date/time field value out of range: "1411-07-08 65:13:56"
Position: 404 22008
org.postgresql.util.PSQLException: ERROR: date/time field value out of range: "1411-07-08 65:13:56"
Position: 404
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
at org.jclarion.clarion.ClarionSQLFile.iterate(ClarionSQLFile.java:531)
at org.jclarion.clarion.ClarionSQLFile.previous(ClarionSQLFile.java:420)
at clarion.Wmj001.main_SetDef(Wmj001.java:174)
at clarion.Thiswindow.init_SetDef(Thiswindow.java:102)
at clarion.Thiswindow.init(Thiswindow.java:89)
at clarion.Windowmanager.run(Windowmanager.java:489)
at clarion.Wmj001.main(Wmj001.java:50)
at clarion.Main.begin(Main.java:347)
at clarion.Main.main(Main.java:331)
12/20 07:31:18 F ClarionSQLFile - INSERT INTO blagajnicke_transakcije (serijski_broj,datum_i_vrijeme_transakcije,tec_tl_broj_tecajne_liste,tec_tl_datum_tecajne_liste) VALUES ('','2011-12-20 07:31:18',1,'2014-01-02') []
12/20 07:31:18 W ClarionSQLFile - SQLException : ERROR: current transaction is aborted, commands ignored until end of transaction block 25P02
org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:345)
at org.jclarion.clarion.ClarionSQLFile.add(ClarionSQLFile.java:118)
at clarion.Wmj001.main_SetDef(Wmj001.java:188)
at clarion.Thiswindow.init_SetDef(Thiswindow.java:102)
at clarion.Thiswindow.init(Thiswindow.java:89)
at clarion.Windowmanager.run(Windowmanager.java:489)
at clarion.Wmj001.main(Wmj001.java:50)
at clarion.Main.begin(Main.java:347)
at clarion.Main.main(Main.java:331)
12/20 07:31:18 F runtime.CWinImpl - Target is null on thread:Thread
12/20 07:31:24 W Wmj001 - Nenad:ERROR: current transaction is aborted, commands ignored until end of transaction block
12/20 07:31:24 W Wmj001 - Nenad2
Ah. The following lines are problematic.
The above code sets date and time to their maximum possible value and then scans backwards from there.
Problem is that what clarion code considers maximum allowed date/time, and how it renders required SQL. I need to do some work here.
Suggest following alternative code:
Details of the problem. Relates to clear(1) and set(key,key)
Date field first. clear(1) sets it to 2^31-1 days since 28th Dec 1800. Or 5881411-07-08. Currently c2j trims this to 4 year date or : 1411-07-08. This is a bug, c2j should represent this correctly.
For time: again clear(1) sets it to 2^31-1. Which c2j treats as (2^31-2)/100 seconds since midnight. Or
5965:13:56; again c2j trims numbers; so it becomes 65:13:56.
I will fix these shortly. So that clear() when performed on a DATE or TIME variable, set limits as you would expect.
Committed a fix so that clear(1) on sets correct values. Cross checked these against a compiled clarion 6 program.
It is OK now.
Thanks
Nenad