Updating Oracle Database with VFP9 2

[hbk2]

Hi, I can't update my 'client' table in oracle database with VFP 9, this is my code :

cSQLCommand = "UPDATE admin.CLIENT SET raison ='blablaa' where cod_cli='4000'"
nRet = SQLEXEC(gnConnHandle ,cSQLCommand)
IF nRet = 1
MESSAGEBOX("Successful Execution")
ELSE
MESSAGEBOX(" NOT Successful Execution ")

ENDIF

Please help me

 

[Olaf Doschke]

So I assume the connection finally could have been established, fine.

You may find out the error(s) this way:

cSQLCommand = "UPDATE admin.CLIENT SET raison ='blablaa' where cod_cli='4000'"
nRet = SQLEXEC(gnConnHandle ,cSQLCommand)
IF nRet <0
   AERROR(laError)
   ? laError[1,1],laError[1,2], laError[1,3],laError[1,4], laError[1,5], laError[1,6]
   MESSAGEBOX(" NOT Successful Execution ")
ELSE
   MESSAGEBOX("Successful Execution")
ENDIF

The error messages about ODBC errors can be quite repetitive, don't be confused about that. The message of VFP typically is the same as the ODBC message.
By the way nRet=0 only can happen in asynchronous mode, and if a query takes longer. Code to do asynchronous querying would need a different approach, as you'd need to wait for the result, but it's surely not a topic for this case, so I' assume nRet=0 also to be OK.

Bye, Olaf.

 

[Mike Lewis]

Rather than [tt]IF nRet = 1[/tt], you need to do [tt]IF nRet >= 1[/tt].

The point is that any positive integer (not just 1) means that the operation was successful. If the reply from SQLEXEC() is positive, the number indicates the number of cursors returned.

Better still, test for IF [tt]nRet < 0[/tt], then use AERROR() to determine the error message, as per Olaf's code.

Mik

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Indeed. And AFAIR only VFP8+ allow sending over sql causing a batch command processing in the sense you could receive multiple result cursors from multiple SELECT queries. It might also depend on the driver used, but not long ago I failed to do two Selects in an old VFP7 application.

The <0 comparison also is exaggerating possibilities, as all errors mean a return value of -1 and only AERROR tells you the details. You never know, though, what some driver might cause.

Bye, Olaf.

 

[hbk2]

It works perfectly thanks for your reply.

 

[hbk2]

Hi
I'm using this code to connect and select data from a local database, but when i try with the distant one, I'm able to connect succefully to the database but I can't select any data.
I'm getting this msg : "NOT Successful Execution! "


vfpcon1="DRIVER={Oracle dans OraDb11g_home1};UID=user;PWD=psw;Server=@ip;DRIVER ={Oracle dans OraDb11g_home1};PORT=1158; SERVICENAME=ORACLE"
gnConnHandle= SQLSTRINGCONNECT(vfpcon1)
IF gnConnHandle < 0
MESSAGEBOX("Cannot make connection", 16, "SQL Connect Error")
ELSE
messagebox("connect to oracle")
cSQLCommand= "select * from ADMIN.ARTICLES"
nRet = SQLEXEC(gnConnHandle ,cSQLCommand,'Result')
IF nRet >0

SELECT Result
go top
thisform.grid1.recordsource='Result'
ELSE
MESSAGEBOX("NOT Successful Execution! ")
ENDIF
ENDIF

Please help me.

 

[Mike Lewis]

It's the same answer that we gave you earlier: You need to use AERROR() to obtain the actual error message returned by Oracle. It's that message that will tell you what went wrong.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[hbk2]

when I use AERROR() i got this message error :
1526 Erreur de connectivite :[Oracle][ODBC][ORA][ORA-00942 : Table ou vue inexistante

I'm going to corect what I said before, I thonk I have a probleme with the connection to the server in the first place.
I'm sure that the table exist in the server and I deleted from the local database.
Thank you for your help.

 

[Mike Lewis]

If the problem was with the original connection, then SQLSTRINGCONNECT() would have returned a negative number, and you would never have got as far as seeing your "NOT Successful Execution!" message.

You say you are sure the table exists on the server.? Are you sure you are using the correct syntax to address it? I am bit rusty on Oracle syntax, but I know that in SQL Server, referring to a table as ADMIN.ARTICLES would mean that the ARTICLES is the name of the table, and ADMIN is the name of the schema - NOT the database. If that's the same in Oracle, then you would need to address at as <database name>.ADMIN.ARTICLES. This is particularly relevant, as you are not specifying the database name in the connection string.

If that doesn't help, try calling VFP SQLTABLES() function. That will give you a definitive list of the tables on the server.

vfpcon1="DRIVER={Oracle dans OraDb11g_home1};UID=user;PWD=psw;Server=@ip;DRIVER ={Oracle dans OraDb11g_home1};PORT=1158; SERVICENAME=ORACLE"
gnConnHandle= SQLSTRINGCONNECT(vfpcon1)
IF gnConnHandle < 0
  MESSAGEBOX("Cannot make connection", 16, "SQL Connect Error")
ELSE
  lnReply = SQLTABLES(gnConnHandle, 'TABLE', csrTables)
  IF lnReply > 0
    SELECT csrTables
    BROWSE
  ENDIF
ENDIF

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Notice: Every error you get from a not executing SQL Statement comes in with the VFP error message part "Erreur de connectivite", it does not mean it is a connection problem, it just is a missing table in your case. Double check your databases.

Look into the error topic Connectivity error: "name" (Error 1526). It really is just the VFP name for all the errors happening through this connection.

You might have an ARTICLES table in the ADMIN schema, but the query syntax of oracle might differ from a dot as separator of schema and tablename, I don't know how you'd address that in Oracles PLSQL dialect.

Anyway, the error message part about error ORA-00942 is very specific. And Google is your friend:

http://www.techonthenet.com/oracle/errors/ora00942.php

So, why don't you query this:

SELECT *
FROM all_objects
WHERE object_type IN ('TABLE','VIEW')
AND object_name = 'ARTICLES';

also try ADMIN.ARTICLES or without the object_name condition. That should work, even if Mikes sggestion of using SQLTABLES() does not work.

Bye, Olaf.

 

[hbk2]

Hi,
Thank you all for your response,

I tried Mike code and it works just fine but I noticed that I'm accessing only Local liste tables.
I tried also Olaf code but I got the same error, so I assume it is a connection problem.

 

[Olaf Doschke]

It's not my code, but code from Oracle.

If you have a connnection problem, it will show here in the code you took from Mike, only here:

IF gnConnHandle < 0
 MESSAGEBOX("Cannot make connection", 16, "SQL Connect Error")
...

What is AERROR() telling about the all_objects query? Is this not existing? I'm no Oracle expert, so is this perhaps differing with the different Oracle versions?

Please don't reject knowledge. The first part of the error message is misleading. A problem connecting to the database server results in having no connection handle and you can't execute anything. But you did execute your query.

And even if your connection is immediately closed server side or there are network outages, you don't get that specific error message. Then you'd get other ODBC errors from your Oracle driver, the connection handle then became invalid and that would be the message.

You can verify you have a connection by first doing some never failing query. Again said, I'm no Oracle expert, but in regard of MSSQL Server this could be a query like "SELECT getdate() as ServerDatetime". This works always, even if a database is empty and has no tables.

You might have a permissions problem, too. You may have so low privileges, that you even are not allowed to view a list of existing tables.

Bye, Olaf.

 

[hbk2]

Hi,
Thank you for your quick reply.
I'm connected to the server database, but when I excute the query I got data from the local database.
I mean that the data that I got don't even exists on the server database.
How do you explain that ?

 

[Mike Lewis]

When you say "local database", do you mean a VFP database, that is, DBC and DBF files? If so, there is no way that you could get at that data using SQLEXEC() with a connection handle to an Oracle database. The only explanation is that you are looking at the wrong data. Your SQLEXEC() will return its result set in a cursor, which you have named Result. Are you sure you are looking at that cursor when you are seeing the local data?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[hbk2]

This is the local DataBase (table : cheques and client ):

and this is the server DataBase (table :article):

vfpcon="DRIVER={Oracle dans OraDb11g_home1};UID=admin;PWD=admin;Server=x.x.x.x ; SID=ORCL; DATABASE=ORCL ;DRIVER ={Oracle dans OraDb11g_home1};PORT=1158;"
gnConnHandle= SQLSTRINGCONNECT(vfpcon)
IF gnConnHandle < 0
	MESSAGEBOX("Cannot make connection", 16, "SQL Connect Error")
ELSE
	MESSAGEBOX("")
	cSQLCommand = "SELECT * FROM ADMIN.article"
	nRet = SQLEXEC(gnConnHandle ,cSQLCommand,'Result')
	IF nRet >0
		SELECT Result
		BROWSE
	ELSE
                   AERROR(laError)
   		? laError[1,1],laError[1,2], laError[1,3],laError[1,4], laError[1,5], laError[1,6]
		MESSAGEBOX("NOT Successful Execution! ")
	ENDIF
endif

when I execute this code I obtain these messages :
1) "Connected to oracle"
2) "NOT Successful Execution!"
3) "1526 Errur de connectivité :[oracle][ODBC][ora][ORA-00942]:Table ou vue inexistante
S0002 942 12"

 

[Mike Lewis]

Which takes us no further forward.

The message is quite clear. Either you have specified the wrong table name; or the wrong database name; or the correct table in the wrong database. Or, as Olaf suggested, it might be a permissions issue.

You still haven't explained what you mean by the "local database". I'm afraid your screen shot doesn't help at all (if only because it the text is illegible).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Again said:

1. Do a query, that can't fail because of missing table or permmissions, alone to test the connection
2. Try and show the results of SQLTables and "Select * From all_objects"

Then:
3. Your screenshots are unreadable, they don't show anything, please simp0ly crop to the part interesting, use Snipping Tool for example.
4. Wild guesses without Oracle knowledge: Is ADMIN.article the right way to address this table? Try "Select * From article", try DATABASE=ADMIN in the connectiomn string.

Bye, Olaf.