🚀 go-pugleaf

N wrote:
> I have been unable to find a single example on how to utilize the USER() function under SQL Builder, I have an easier way to run my query:
> SELECT ORADBA.PS_PROJECT_INFO.PROJECT_ID,ORADBA.PS_PROJECT_INFO.PROJECT_NAME,ORADBA.PS_PROJECT_INFO.PROJECT_DESC
> FROM ORADBA.PS_PROJECT_INFO
> WHERE ORADBA.PS_PROJECT_INFO.PROJECT_ID = (SELECT ORADBA.PS_PROJECT_EMPLOYEES.PROJECT_ID 
>                                     FROM ORADBA.PS_PROJECT_EMPLOYEES
>                                     WHERE ORADBA.PS_PROJECT_EMPLOYEES.EMPLOYEE_ID = (SELECT ORADBA.PS_EMPLOYEE_LOOKUP.EMPLOYEE_ID FROM ORADBA.PS_EMPLOYEE_LOOKUP WHERE ORADBA.PS_EMPLoYEE_LOOKUP.USERNAME = (SELECT USER from dual)));
> 
> 
> But for some reason it will not accept the this query ( i already ran it as a script via command line and it works)... So now I am trying to use the USER() function... Is there any documentation about these functions and how to use them. What I found in the help section was useless?
> Thank you for your help in advance..
> N

Generally it has to do with the driver, and what it supports.  I get the 
feeling you are using Oracle..<G>  So you need to get on their forums 
for their JDBC driver and go from there..


John..



-- 
=============================================
TeamB are volunteer helpers.  Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
http://www.microps.com/mps/paperFAQ.html
====================================================

Oh, and as an additional note..

If the driver supports it.. there is a DataSource property "user".. 
Consult any JDBC 2.0 book on Datasource properties..

John..

-- 
=============================================
TeamB are volunteer helpers.  Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
http://www.microps.com/mps/paperFAQ.html
====================================================

"John Moore (TeamB)" <jbm@microps.com> wrote:
>Oh, and as an additional note..
>
>If the driver supports it.. there is a DataSource property "user".. 
>Consult any JDBC 2.0 book on Datasource properties..
>
>John..
>
>-- 
>=============================================
>TeamB are volunteer helpers.  Please DO NOT REPLY VIA EMAIL!
>Post all questions and replies to this newsgroup ONLY
>For papers on DataExpress, Applets, JSP, and Web Development go to:
>http://www.microps.com/mps/paperFAQ.html
>====================================================

I have determined that the issue is the nested querys,
ex:

Select USERNAME
FROM USER_TABLE
WHERE USERNAME = (SELECT SYS_CONTEXT('USERENV','CURRENT_USER' FROM DUAL)

Using JBuilders SQL Builder, I am simply typing in the query that I wish to use in the SQL Tab and then trying to test it by clicking the TEST Tab, at which point the following "ALERT" pops up:

"Error parsing SQL: [line 4, col44] Invalid start of expression, found near SELECT. You must enter valid SQL in order to proceed."

Is this alert being returned from the server, or is this before it even leaves JBuilder?


I know that my SQL is valid:
SELECT PS_PROJECT_INFO.PROJECT_ID, PS_PROJECT_INFO.PROJECT_NAME,
PS_PROJECT_INFO.PROJECT_DESC
FROM PS_PROJECT_INFO
WHERE PS_PROJECT_INFO.PROJECT_ID = (SELECT ORADBA.PS_PROJECT_EMPLOYEES.PROJECT_ID 
                                           FROM ORADBA.PS_PROJECT_EMPLOYEES
                                           WHERE ORADBA.PS_PROJECT_EMPLOYEES.EMPLOYEE_ID = 8)


I just wanted to give more specifics to help me in my search for a solution.  How does JBuilder determine if its valid SQL, does it use the JDBC driver?  I didn't find anything relevant in the Oracle forums, so I figured I would give the exact error message that came up.

I am using JBuilder 2006 Enterprise 12.0.203.0
           JDBC Driver Classes12.jar 
           Oracle 8.1.7
           SDK 1.5

thanks for any info,
N

N wrote:
> "John Moore (TeamB)" <jbm@microps.com> wrote:
>> Oh, and as an additional note..
>>
>> If the driver supports it.. there is a DataSource property "user".. 
>> Consult any JDBC 2.0 book on Datasource properties..
>>
>> John..
>>
>> -- 
>> =============================================
>> TeamB are volunteer helpers.  Please DO NOT REPLY VIA EMAIL!
>> Post all questions and replies to this newsgroup ONLY
>> For papers on DataExpress, Applets, JSP, and Web Development go to:
>> http://www.microps.com/mps/paperFAQ.html
>> ====================================================
> 
> I have determined that the issue is the nested querys,
> ex:
> 
> Select USERNAME
> FROM USER_TABLE
> WHERE USERNAME = (SELECT SYS_CONTEXT('USERENV','CURRENT_USER' FROM DUAL)
> 
> Using JBuilders SQL Builder, I am simply typing in the query that I wish to use in the SQL Tab and then trying to test it by clicking the TEST Tab, at which point the following "ALERT" pops up:
> 
> "Error parsing SQL: [line 4, col44] Invalid start of expression, found near SELECT. You must enter valid SQL in order to proceed."
> 
> Is this alert being returned from the server, or is this before it even leaves JBuilder?
> 
> 
> I know that my SQL is valid:
> SELECT PS_PROJECT_INFO.PROJECT_ID, PS_PROJECT_INFO.PROJECT_NAME,
> PS_PROJECT_INFO.PROJECT_DESC
> FROM PS_PROJECT_INFO
> WHERE PS_PROJECT_INFO.PROJECT_ID = (SELECT ORADBA.PS_PROJECT_EMPLOYEES.PROJECT_ID 
>                                            FROM ORADBA.PS_PROJECT_EMPLOYEES
>                                            WHERE ORADBA.PS_PROJECT_EMPLOYEES.EMPLOYEE_ID = 8)
> 
> 
> I just wanted to give more specifics to help me in my search for a solution.  How does JBuilder determine if its valid SQL, does it use the JDBC driver?  I didn't find anything relevant in the Oracle forums, so I figured I would give the exact error message that came up.
> 
> I am using JBuilder 2006 Enterprise 12.0.203.0
>            JDBC Driver Classes12.jar 
>            Oracle 8.1.7
>            SDK 1.5
> 
> thanks for any info,
> N
> 
> 

Ok, that's the relevent information we needed.  classes12.jar is very 
old, and was intended for JDK 1.3 and earlier.  You need a more recent 
JDBC driver jar.

According to the Oracle FAQ, you can use the ojdbc14.jar file from 
Oracle 10 with Oracle 8.1.7.

http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm

-- 

Regards,

Lori Olson [TeamB]

------------

Save yourself, and everyone else, some time and search the
newsgroups and the FAQ-O-Matic before posting your next
question.

Google Advanced Newsgroup Search
     http://www.google.ca/advanced_group_search
Other Newsgroup Searches:
     http://www.borland.com/newsgroups/ngsearch.html
Joi Ellis's FAQ-O-Matic:
     http://www.visi.com/~gyles19/fom-serve/cache/1.html

Just an update...
I tried using different JDBC drivers and had no luck in using subqueries with QueryDataSet in JBuilder 2006.  I had to rewrite the Subqueries as Joins instead and this seems to work. I am still not sure if it truelly is a JDBC driver issue or a JBuilder issue.  I was unable to find any information regarding my particular problem (running subqueries, 3 levels, wouldn't even work for 2 levels). To any one else with similar issues, good luck, and write Joins instead.

Nick




"N" <nmperez@water.ca.gov> wrote:
>
>I have been unable to find a single example on how to utilize the USER() function under SQL Builder, I have an easier way to run my query:
>SELECT ORADBA.PS_PROJECT_INFO.PROJECT_ID,ORADBA.PS_PROJECT_INFO.PROJECT_NAME,ORADBA.PS_PROJECT_INFO.PROJECT_DESC
>FROM ORADBA.PS_PROJECT_INFO
>WHERE ORADBA.PS_PROJECT_INFO.PROJECT_ID = (SELECT ORADBA.PS_PROJECT_EMPLOYEES.PROJECT_ID 
>                                    FROM ORADBA.PS_PROJECT_EMPLOYEES
>                                    WHERE ORADBA.PS_PROJECT_EMPLOYEES.EMPLOYEE_ID = (SELECT ORADBA.PS_EMPLOYEE_LOOKUP.EMPLOYEE_ID FROM ORADBA.PS_EMPLOYEE_LOOKUP WHERE ORADBA.PS_EMPLoYEE_LOOKUP.USERNAME = (SELECT USER from dual)));
>
>
>But for some reason it will not accept the this query ( i already ran it as a script via command line and it works)... So now I am trying to use the USER() function... Is there any documentation about these functions and how to use them. What I found in the help section was useless?
>Thank you for your help in advance..
>N

Nick wrote:
> Just an update...
> I tried using different JDBC drivers and had no luck in using subqueries with 
QueryDataSet in JBuilder 2006.  I had to rewrite the Subqueries as Joins 
instead
and this seems to work. I am still not sure if it truelly is a JDBC 
driver issue
or a JBuilder issue.  I was unable to find any information regarding my 
particular
problem (running subqueries, 3 levels, wouldn't even work for 2 levels). 
To any
one else with similar issues, good luck, and write Joins instead.
> 

I don't know if you entirely understand the relationship between 
JBuilder and JDBC..

Basically there is none.. sort of..

The SQL Builder (which I never have used..) and DX are probably the only 
"interfaces" between JBuilder and JDBC..

I would 'ASSUME' that SQL builder sticks to a specific level of the SQL 
standard. So.. if you are including a database specific function(s), it 
will probably complain..

My experience (where I tend to do most of my work) with DX is that it 
does no validation and just passes the SQL through.  (There can be 
escaping issues..)

That leaves JDBC and that is the rub..  JDBC is ONLY an interface API to 
specific implementations by the various database vendors.

(Purchase a good JDBC book, that will do more to helping you on the 
"java/JBuilder" end than anything..)

Therefore, with the possible exception of the SQL Builder (which I 
never, ever use.. did I say that already..<G>) any issue with SQL and 
your database is ALWAYS a driver issue.  JBuilder could care less..

John..


-- 
=============================================
TeamB are volunteer helpers.  Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
http://www.microps.com/mps/paperFAQ.html
====================================================

I appreciate everyones help on this topic. Will do on the JDBC book, I am crash course learning JBuilder, so I appreciate the explanations.

thanks again,
Nick

Hi,
I've just experimented with this. I got JB 2006 Ent, the same as Nick. I 
used classes12.jar against a 10G Oracle database.

The following SQL (which should work on virtually any Oracle database) runs 
happily in PL/SQL :-
select * from dual where dummy in (select dummy from dual)

The same statement also works fine in JB 2006's Database Pilot.

If you create a new datamodule and use the SQL builder to create the 
following SQL statement it works.
SELECT * FROM DUAL

Once you've created your QueryDataSet, go into design mode and select your 
QueryDataSet from the structure pane.
From the properties window, click on the 3 ellipses next to the Query 
property.
Now type in the original SQL with the nested subquery :-
select * from dual where dummy in (select dummy from dual)

Click the test query button and you should get "SUCCESS".

Now click the SQL Builder button and use the same SQL in the builder, this 
time you get an error parsing SQL. I think this probably suggests a problem 
with the builder rather than JDBC.

I think the moral is that as John says he avoids using the SQL Builder to 
construct the SQL. You could test it in the Database Pilot and paste it in 
from there.

Hope this helps.
Arth






"Nick" <nmperez@water.ca.gov> wrote in message 
news:45dc7fd2$1@newsgroups.borland.com...
>
>
> I appreciate everyones help on this topic. Will do on the JDBC book, I am 
> crash course learning JBuilder, so I appreciate the explanations.
>
> thanks again,
> Nick