Difference between revisions of "Oracle"
From Free Pascal wiki
Jump to navigationJump to search (→OOP access to Oracle: tnsnames.ora info) |
|||
Line 9: | Line 9: | ||
== OOP access to Oracle == | == OOP access to Oracle == | ||
Built over the low-level interface, the SQLDB framework supplied with FPC supports accessing Oracle (using TOracleConnection); see also [[Lazarus_Database_Overview#Lazarus_and_Oracle]] | Built over the low-level interface, the SQLDB framework supplied with FPC supports accessing Oracle (using TOracleConnection); see also [[Lazarus_Database_Overview#Lazarus_and_Oracle]] | ||
+ | |||
+ | Lazarus also has a component: | ||
+ | |||
+ | [[Image:sqldbcomponents.png]] | ||
* Hostname: as with other sqldb connectors, use hostname or IP address. Leave empty if you use a TNSNNAMES.ORA net service name in DatabaseName | * Hostname: as with other sqldb connectors, use hostname or IP address. Leave empty if you use a TNSNNAMES.ORA net service name in DatabaseName | ||
Line 119: | Line 123: | ||
Go back to [[Package_List|Packages List]] | Go back to [[Package_List|Packages List]] | ||
+ | |||
+ | {{LCL Components Footer |TPQTEventMonitor|TODBCConnection}} | ||
+ | {{LCL Components}} | ||
+ | |||
+ | [[Category:Databases]] | ||
+ | [[Category:Components]] | ||
[[Category:FPC]] | [[Category:FPC]] | ||
− | [[Category: | + | [[Category:LCL]] |
+ | [[Category:Tutorials]] |
Revision as of 18:54, 24 May 2014
Low level Oracle server interface
The low level Oracle server interface exists of one unit, oraoci, which is a straight translation of the Oracle interface header files.
There are 2 example programs:
- oraclew contains some utility routines for the oracle interface, for easier management of result sets. Needs the classes unit from the FCL.
- test01 a simple test program to demonstrate the interface.
OOP access to Oracle
Built over the low-level interface, the SQLDB framework supplied with FPC supports accessing Oracle (using TOracleConnection); see also Lazarus_Database_Overview#Lazarus_and_Oracle
Lazarus also has a component:
- Hostname: as with other sqldb connectors, use hostname or IP address. Leave empty if you use a TNSNNAMES.ORA net service name in DatabaseName
- Username/password: same as with other sqldb connectors
- DatabaseName:
- instance/SID of the Oracle server you want to connect to or
- net service name in a TNSNAMES.ORA file
Note: Released FPC/Lazarus x64 versions on Windows do not include an Oracle connector. If you enable it and test successfully, please submit a patch with the changes so it can be included
Troubleshooting
Client and server character sets
To get info about what character set/NLS settings are active, you can run the following program.
Please don't forget to fill out correct server name, username, password and database name before compiling.
program oracharset;
{ Shows client and server character set/NLS info}
{ PLEASE EDIT PASSWORDS ETC BELOW. }
{$mode objfpc}{$H+}
uses {$IFDEF UNIX} {$IFDEF UseCThreads}
cthreads, {$ENDIF} {$ENDIF}
Classes,
SysUtils,
sqldb,
oracleconnection;
var
Col: integer;
Conn: TOracleConnection;
Tran: TSQLTransaction;
Q: TSQLQuery;
begin
Conn := TOracleConnection.Create(nil);
Tran := TSQLTransaction.Create(nil);
Q := TSQLQuery.Create(nil);
try
// * EDIT IDENTIFYING INFO AS NEEDED*
Conn.HostName := '';
Conn.UserName := 'system';
Conn.Password := '';
Conn.DatabaseName := 'XE';
// *END IDENTIFIYING INFO*
Conn.Transaction := Tran;
Q.DataBase := Conn;
Conn.Open;
Tran.Active := true;
writeln('Server character set info:');
Q.SQL.Text := 'SELECT value$ FROM sys.props$ WHERE name like ''NLS_%'' ';
Q.Open;
Q.First;
while not (Q.EOF) do
begin
writeln('*****************');
for Col := 0 to Q.Fields.Count - 1 do
begin
try
writeln(Q.Fields[Col].DisplayLabel + ':');
writeln(Q.Fields[Col].AsString);
except
writeln('Error retrieving field ', Col);
end;
end;
Q.Next;
end;
Q.Close;
writeln('');
writeln('Client character set info:');
Q.SQL.Text := 'SELECT * FROM NLS_SESSION_PARAMETERS ';
Q.Open;
Q.First;
while not (Q.EOF) do
begin
writeln('*****************');
for Col := 0 to Q.Fields.Count - 1 do
begin
try
writeln(Q.Fields[Col].DisplayLabel + ':');
writeln(Q.Fields[Col].AsString);
except
writeln('Error retrieving field ', Col);
end;
end;
Q.Next;
end;
Q.Close;
// *END EXAMPLE BUG TESTING CODE*
Conn.Close;
finally
Q.Free;
Tran.Free;
Conn.Free;
end;
writeln('Program complete. Press a key to continue.');
readln;
end.
ORA-00911 : invalid character
If you see this error message, you might want to try
- removing a trailing semicolon - ; - if you have it in a SELECT statement
- adding a trailing semicolon (e.g. in CALL or EXECUTE statements)
See this thread which applies to .Net but may apply to SQLDB as well: [1]
Go back to Packages List
Return To: LCL Components | — Previous: TPQTEventMonitor | Next: TODBCConnection |