SQL

[gasomma]

Hi,

How I can solve this problem? USER is the name of a table in an access database. I need to inquiry it, but OE shows an error of syntax.

Select * FROM USER.

Many thanks.
G.

 

[Cringer]

An error message would be a good start. When you say Access database, do you really mean Microsoft Access? How are you connecting OE to it?

There seems to be quite a lot of info missing in this question.

 

[TomBascom]

What is "this problem"?

Ok, your *first* problem is that you are attempting to use SQL... so now you have two problems

"OE shows an error of syntax" but Gasomma has not shared this error of syntax with us. So we are scratching our heads wondering what the problem is.

How are you connecting to the SQL database? "Access"? As in Microsoft Access? Microsoft Announces Retirement and Shutdown of Office 365 Access Web Apps and Web Databases - Imaginet

"OE shows" also leads me to worry that you are attempting to use the SQL-89 embedded in the 4gl. That is the road to pain and agony. Don't go there. If you are already on that road, stop, turn around and start over.

Proper SQL (not the SQL-89 embedded inside the 4gl) has lots of features that allow you to create aliases for table and field names. But you are going to have to share a few details about how you are connecting, what tools you are using and what errors you are seeing before we can be any more helpful.

 

[gasomma]

HI to All,
thanks for your reply. Below you can see the code:

DEFINE TEMP-TABLE ttEmpl NO-UNDO
    FIELD EmplID        AS INTEGER FORMAT "zzzzzz"
    FIELD EmplName      AS CHARACTER
    FIELD EmplLastName  AS CHARACTER .

chrQuery = "(Select * FROM  user)".

   Conn = NEW System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\OB1\JCA\MDB\ingress.mdb;Persist Security Info=False;Jet OLEDB:Database Password=dhjdfjhdfdhjf;").

   Conn:Open().

   Cmd = NEW System.Data.OleDb.OleDBCommand(chrQuery,conn).

   Reader = Cmd:ExecuteReader().

   DO WHILE Reader:Read():
     CREATE ttEmpl.
     ASSIGN
        ttEmpl.EmplName = Reader["name"]:ToString()
        ttEmpl.EmplLastName = Reader["lastname"]:ToString()
        ttEmpl.EmplID   = integer( Reader["userid"]:ToString()).
   END.

Thk in advance. If there are other or the best way please any suggestion is appreciate.
G.

MOD EDIT: Added Code tags and obfuscated password.

 

[TomBascom]

You are halfway there! Where is the error message?

 

[gasomma]

is in Italian.

syntax error in the query.
incomplete query proposition
Thx.

 

[TomBascom]

That error is saying that your SQL query is wrong from the point of view of the access database. It is not related to your Progress syntax.

I know next to nothing about access but the "(" and ")" seem strange to me. That doesn't look like normal SQL syntax. I would first try the query without those:

chrQuery = "select * from user".

Is there some query tool other than Progress that you can use to verify that you have the proper SQL syntax? For instance, I have heard that you can fetch Acccess data into Excel.

 

[Rob Fitzpatrick]

chrQuery = "select * from user".

Or maybe chrQuery = "select * from user;".

Is there some query tool other than Progress that you can use to verify that you have the proper SQL syntax? For instance, I have heard that you can fetch Acccess data into Excel.

Yes, Excel can retrieve data from a variety of sources, including ODBC, SQL Server, and Access. Look at the "Get External Data" section of the ribbon on the "Data" tab.

 

[TomBascom]

If this was an OpenEdge database and I was using sqlexp to query it the syntax would be:

select * from pub.user;

The semi-colon is the statement terminator. "pub" is the schema (but might be optional if the default schema has a table called "user").

 

[gasomma]

Tom,

if I change the name of the table, for example user_info, works properly. My problem is for the "user" table.
"user" is a special word for OE. I tried different solution without success.
Thx.
G.

 

[TomBascom]

"User" only appears inside your quoted string. OE doesn't care about that and it is not a conflict. Your error message is complaining about *syntax*, not about a keyword conflict.

What different solution id you try without success? Was the error the same?

If you send a query string to access that is plain old garbage:

chrQuery = "xyzzy".

do you still get the same error?

Have you tried your query from a non-Progress query tool (like Excel)?

 

[gasomma]

no the error is different: SQL command not valid.
Sorry user without inside the quote. Select * from user running in the OE editor doesn't work while Select * from user-info works.
AS your suggestion Select * from pub.user from OE editor works, not for Cmd = NEW System.Data.OleDb.OleDBCommand("Select * from pub.user",conn).
If I tried to insert a name of table wrong appears another kind of error.
I think problem is user word. I never tried from non-Progress query.
Thx.

 

[gasomma]

I'm using another way now. Connect Access DB with ODBC driver and I can inquiry the table.
many thanks.
G.

 

[TomBascom]

Querying from the OE editor is not the same as the sample code that you showed. Querying from the OE editor has *nothing* to do with querying an access database.

This is making no sense at all.

 

[TomBascom]

Please pick a single thing to focus on -- you either have an issue with the code that you posted and showed a related error message for or you have some OTHER issue that should be discussed in a thread of its own.

We cannot help you if you keep bouncing around between half disclosed constantly changing issues.

 

[gasomma]

Sorry Tom. Maybe I didn't express myself well. First of all my OE v. is 10.2b.
The code below works well for any table except 'user'.
I can only deduce that the problem is relative to the 'user' table. That's all. 'User' table exist in the access database. Infact the error displayed is different when table doesn't exist in the DB.

DEFINE TEMP-TABLE ttEmpl NO-UNDO
FIELD EmplID AS INTEGER FORMAT "zzzzzz"
FIELD EmplName AS CHARACTER
FIELD EmplLastName AS CHARACTER .

chrQuery = "(Select * FROM user-info)".

Conn = NEW System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\OB1\JCA\MDB\ingress.mdb;Persist Security Info=False;Jet OLEDBatabase Password=ingress;").

Conn:Open().

Cmd = NEW System.Data.OleDb.OleDBCommand(chrQuery,conn).

Reader = Cmd:ExecuteReader().

DO WHILE Reader:Read():
CREATE ttEmpl.
ASSIGN
ttEmpl.EmplName = Reader["name"]:ToString()
ttEmpl.EmplLastName = Reader["lastname"]:ToString()
ttEmpl.EmplID = integer( Reader["userid"]:ToString()).
END.

Another proof is when I write the code in progress editor e press RUN:

SELECT * from user-info. That's right.

Instead WHEN write.

SELECT * from user. I receive a message error.

I hope to be clear.
many thanks.
G.

 

[TomBascom]

Please actually show all of the actual code and all of the actual error messages if you are going to ask for help.

We cannot read your mind or look over your shoulder at your screen.

Your adventures running SELECT statements from the editor are irrelevant and pointless and do not show what you think they show. Aside from that I cannot make heads or tails out of what you are doing but I am happy that you got something to work.