Database Resource doesn't output to a mapped field

Post any questions you have about using the Verj.io Studio, including client and server-side programming with Javascript or FPL, and integration with databases, web services etc.

Moderators: Jon, Steve, Ian, Dave

mkushwaha
Ebase User
Posts: 29
Joined: Mon Nov 02, 2015 7:19 pm

Database Resource doesn't output to a mapped field

#1

Postby mkushwaha » Wed Dec 16, 2015 8:53 pm

I have two date fields and a button on a field.
On a button, I run a script which looks like following:

Code: Select all

if(fields.END_DATE.value == null){
	resources.REHAB_WKST_NOT_DATEMAX.fetch();
	log("SYSTEM " + system.variables.$FETCH_COUNT.value);
	log("MAX DATE: "+ fields.MAX_DATE.value);
	fields.END_DATE.value = fields.MAX_DATE.value;
	}
I have database resource REHAB_WKST_NOT_DATEMAX mapped to correct business view and in the form mapped to MAX_DATE. Although when I fetch the resource, the query runs but doesn't map it to MAX_DATE.

below is the output from Execution Log:

15:55:28.003 Debug: Debug for database resource REHAB_WKST_NOT_DATEMAX - SQL statement:
15:55:28.003 Debug: SELECT MAX (REFERRAL_DATE) REF_DATE FROM REFERRALS
15:55:28.003 Debug: End execution of command - fetch : 15:55:28.003
15:55:28.003 Info : SYSTEM 1
15:55:28.003 Info : MAX DATE: null
Database query runs fine in oracle and gives a valid output and is read only type. REF_DATE is the field name mapped to MAX_DATE. Below is the query.

Code: Select all

SELECT MAX (REFERRAL_DATE)  REF_DATE FROM REFERRALS 
Could you help diagnose this problem ? Can't think of any solutions yet. Also is there appropriate functions/methods to diagnose this problem better. Thanks.
0 x

kotinkarwak
Ebase User
Posts: 109
Joined: Mon Sep 21, 2015 9:55 pm

#2

Postby kotinkarwak » Wed Dec 16, 2015 9:38 pm

Have this working

Code: Select all

select email, max(createdate) as createdate
from testdb.user
are you missing "AS" in your SQL?
In my case, I force createdate to be reused from the list of fields populated automatically when creating the resource else could also have one added by me here that will "hold" that value for max(createdate).
0 x
ebasetech v5

Skype: mateso08
Location: Kenya

Jon
Moderator
Moderator
Posts: 1342
Joined: Wed Sep 12, 2007 12:49 pm

#3

Postby Jon » Thu Dec 17, 2015 9:45 am

All looks fine to me. Have you tried running the query outside of Ebase?
0 x

mkushwaha
Ebase User
Posts: 29
Joined: Mon Nov 02, 2015 7:19 pm

#4

Postby mkushwaha » Fri Dec 18, 2015 3:59 pm

@Jon Yes I have tried running the query outside ebase. It runs fine and has a valid output.

@kotinkarwak Yes I tried putting as and holding variable but same result.

Please advice
0 x

kotinkarwak
Ebase User
Posts: 109
Joined: Mon Sep 21, 2015 9:55 pm

#5

Postby kotinkarwak » Fri Dec 18, 2015 7:42 pm

You able to show us the setup?
via a video or something?
0 x
ebasetech v5

Skype: mateso08
Location: Kenya

Jon
Moderator
Moderator
Posts: 1342
Joined: Wed Sep 12, 2007 12:49 pm

#6

Postby Jon » Mon Dec 21, 2015 10:35 am

Make sure the table actually contains data, your query will return 1 row with a max date of null if the underlying query returns no data. Other than that, check the mappings again - but it looks OK.
0 x

edwinayin@qq.com
Ebase User
Posts: 24
Joined: Fri Nov 04, 2016 7:41 pm

Re: Database Resource doesn't output to a mapped field

#7

Postby edwinayin@qq.com » Thu Feb 08, 2018 6:45 pm

I am encountering the same problem as the original poster, anyone knows how to fix this?

I have a simple select resource to fetch a LONGVARCHAR to my form field:

Code: Select all

select COMMENTS_TEXT, COMMENTS_ID
from COMMENTS
where EVENT_ID = &&QRY_EVENT_ID
20% of the time, I will have it successfully fetched, but most of the time I just get a null. I tried running the same query in Oracle and always get the record.

Someone please help.

thanks,
Edwina
0 x

Jon
Moderator
Moderator
Posts: 1342
Joined: Wed Sep 12, 2007 12:49 pm

Re: Database Resource doesn't output to a mapped field

#8

Postby Jon » Fri Feb 09, 2018 9:51 am

What is the Oracle column type?
What is the type in the Database Resource? (Probably LONGVARCHAR?)
Do you have Use Prepared Statements checked in the Database Resource?
Which Ebase version are you using?
0 x

edwinayin@qq.com
Ebase User
Posts: 24
Joined: Fri Nov 04, 2016 7:41 pm

Re: Database Resource doesn't output to a mapped field

#9

Postby edwinayin@qq.com » Tue Feb 13, 2018 4:13 pm

Hello Jon,

Thanks for your reply, the issue was solved after I changed my Oracle database column type to VARCHAR2, I was using LONG as my database column type, and LONGVARCHAR as my resource type in Ebase.

I was using Ebase 5.2 and didn't use a prepared statement.

regards,
Edwina
0 x

Jon
Moderator
Moderator
Posts: 1342
Joined: Wed Sep 12, 2007 12:49 pm

Re: Database Resource doesn't output to a mapped field

#10

Postby Jon » Tue Feb 13, 2018 4:50 pm

In general with Oracle it's best to use CLOB type in both the database and the Database Resource - this requires that the Use Prepared Statements checkbox is ticked. Other databases seem to allow you to handle long character strings defined as varchar or text without too much problem, but Oracle can be very picky.
0 x


Who is online

Users browsing this forum: No registered users and 6 guests