Oracle via ADO/ODBC

[lucasdebeltran]

Hello,

Anyone is using it?.

Any particular issue?. For example, SQLite has its own format for dates, different tan MSACCESS/MYSQL.

Thank you very much.

[Rick Lipkin]

Lucas

If you are using a recent build of xHarbour DateTime fields now are defined as ValType "T" and NOT "D" .. I recommend you convert them back to ValType "D" something like this :

Code: Select all  Expand view

dDate := TtoDate( oRs:Fields("Date"):Value)

...

//--------------------------
Function TtoDate( tDate )

If empty( tDate)
   Return( ctod("00/00/00"))
Endif

If ValType( tDate ) = "D"
   Return(tDate )
Endif

Return( stod( substr( ttos( tDate ), 1, 8 ) ))

 

As far as ADO .. the code is the same AddNew(), Update(), MoveNext(), etc. One thing you will have to know .. when you create your Sql statements .. Ms Access uses a # sign to delimit the Date variable and Sql Server and Oracle just use "'"

In my Code I assign a Public variable xDatabase to "A" for ms Access and everything else covers Sql server and Oracle.

Hope this helps.

Rick Lipkin

Code: Select all  Expand view

If xDatabase = "A" // ms access
   cSQL := "SELECT [AactivEid],[Formno],[TravDate],[Code], "
   cSql += "[Desc] as Descrip,[Meals],[Mileage],[Perdiem], "
   cSql += "[Air],[Other],[Misc],[Regist],[Lodging] "
   cSql += "From [Aactiv] where [Formno] = "+ltrim(str(nFormno))
   cSql += " and [TravDate] = #"+dtoc(dDate)+"# Order by [TravDate]"

Else

   // sql server or oracle

   cSQL := "SELECT [AactivEid],[Formno],[TravDate],[Code], "
   cSql += "[Desc] as Descrip,[Meals],[Mileage],[Perdiem], "
   cSql += "[Air],[Other],[Misc],[Regist],[Lodging] "
   cSql += "From [Aactiv] where [Formno] = "+ltrim(str(nFormno))
   cSql += " and [TravDate] = '"+dtoc(dDate)+"' Order by [TravDate]"
Endif
 

[lucasdebeltran]

Thank you.

And what about logical fields?.

For example, with Access I use this filter:

cCondicion := "DEVUELTO = 'False'" // revisar MySQL
oRs:Filter = cCondicion


It Works perfect. Maybe it will cause trouble with Oracle?.

It is quite funny, Access and MSSQL do not behavie the same, but they sould.

[Enrico Maria Giordano]

Lucas,

Thank you.

And what about logical fields?.

For example, with Access I use this filter:

cCondicion := "DEVUELTO = 'False'" // revisar MySQL

If you use INT fields for logical then you can use

cCondicion := "DEVUELTO = 0"

at least for Jet, MSSQL and MySQL.

EMG

[nageswaragunupudi]

Mr Lucas

At present, FWH Sql functions support Oracle also.

FWH 13.07 version's ADO and SQL functions fully support SQLite and Oracle also suitably taking care of the field types and formatting appropriate to the RDBMS in addition to MSACCESS, MSSQL and MySQL.

In the next versions, we propose to add FireBird and Postgresql. I suppose with that we would be covering adequate range of RDBMSs.

Even now, for example:
FW_DateToSQL( dDateTime ) returns formatted date or datetime literal suitable to the RDMS, including Oracle. Please test the function.

Similarly FW_ValToSQL( uVal ) returns appropriately formatted literal
If uVal is an array the function returns comma delimited literals enclosed in parenthesis, which can be readily used in insert statements.

The purpose of all these functions is that you write the same code for all RDBMSs in the manner you know to write in Harbour and these functions take care of all the conversions required.

And our programmers need not refer to any one or any manual how to handle different RDBMSs

As mentioned above, Oracle does not provide native support for Boolean fields.
The most prevalent practice among Oracle programmers is to use Character field (width 1 ) and use 'Y' or 'N' to represent true or false. I have seen this practice with 99% of the programmers including well known authorities on Oracle.

Still I support using NUMBER field with values 1 or 0 ( as EMG suggested above ). When we do cross-platform programming this approach is very useful.
Our FWH SQL functions assume this approach for Oracle also.

For all others including SQLite, you can use BIT field type.
Recordset field value returns .T. or .F. and we can assign .T. or .F..

If you use BIT fields ( MSACCESS, MSSQL, MYSQL, SQLITE ) we can use 1/0 or TRUE/FALSE.
oRs:Filter := "MARRIED=TRUE"
or
oRs:Filter := "MARRIED=1"
But for Oracle
oRs:Filter := "MARRIED=1" only.

If we adopt the later kind of filters, we can be sure of covering Oracle also.



Oracle does not provide for AutoIncrement fields also. We need to create a Sequence and then a Trigger to insert the sequence values in the primary key field.

In version 13.07, the function FW_AdoCreateTable() for Oracle creates the table, sequence and a trigger. All this is done automatically and for our programmers it is as simple as creating auto inc field with any other RDBMS.

I continue usage of some of these functions in the next posting

[nageswaragunupudi]

For inter conversion of Date to/from DateTime variables, FWH provides
FW_DTOT() and FW_TTOD() functions. These functions are written in 'C' for speed.

[nageswaragunupudi]

Mr Lucas

Please note that there is a difference in formatting dates for ADO and for SQL.

For setting filters in ADO we need to format dates as #YYYY-MM-DD#.
The function FW_DateToADO( dDate ) returns a string literal in this format.

Example:
oRs:Filter := "HIREDATE > " + FW_DateToADO( dMyDate )
You can use this date format in ADO irrespective of the RDBMS. (Not in SQL statements executed in ADO)

For SQL statements, ( example WHERE clauses, UPDATE and INSERT statements) we need to format the dates in one of the formats recoginized by the respective RDBMS. Each RDBMS accepts some alternative formats.

We are more interested in multi-platform programming and therefore we need to reduce the number of formats to use.

Oracle: DATE 'YYYY-MM-DD' and TIMESTAMP 'YYYY-MM-DD HH:MI:SS'
MSSQL: 'YYYY-MM-DD' and 'YYYY-MM-DDTHH:MM:SS'
Others: 'YYYY-MM-DD' and 'YYYY-MM-DD HH:MM:SS'

MS Access accepts #YYYY-MM-DD#. But also accepts 'YYYY-MM-DD'

By using FW functions all these formatting needs are automatically taken care of. You can confidently write the same code which works on any RDBMS

[Mulyadi]

Lucas,

for now I using Oracle 11g.
in Oracle not existing limit size a database file.
but for the all depend from Aplication Architecture have we do.

right there a difference architecture between Oracle and other RDBMS.
of all things, you can review the manual architectur.
but basically all function to process the data.

to save a data in format dates, in Oracle we can use Internal Oracle Function
like TO_DATE() or to do otherwise TO_CHAR()

de facto prescriptive from Mr.Nages and Mr.Rick is good information for the case.


Hope this helps.

Mulyadi

[nageswaragunupudi]

TO_DATE()

This was the only way in very old versions, i.e., prior to 9i and I still remember thos days.
Now its simpler as I suggested in my posting above.

[lucasdebeltran]

Thank you.

SO in this case:

dDesde := date()

cCondicion := "FECHA >= "+DTOC(dDesde) +" AND FECHA <= "+DTOC(dHasta) // REVISAR MYSQL
oRs:Filter = cCondicion


Instead of Dtoc, I should use FW_DateToSQL( dDesde )?.


Thanks.

[nageswaragunupudi]

Lucas

I advise you to use

Code: Select all  Expand view

  cCond := "FECHA >= "+ FW_DateToADO( dDate ) +" AND FECHA <= " + FW_DateToADO( dDate )
   ? cCond
oRs:Filter := cCond
 

for filter conditions in ADO, irrespective of the RDBMS ( Access, MSSql, MySql, SqLite, Oracle )

The resultant expressions is:

Code: Select all  Expand view

FECHA >= #2013-07-24# AND FECHA <= #2013-07-24#

Sure some other formats also work with some providers/RDBMS.
But the format I suggested works with all providers/RDBMSs in ADO/ODBC.

For using as a part of SQL statements like in WHERE clauses, use FW_DateToSQL( dDate )

Example:

Code: Select all  Expand view

oCn := FW_OpenAdoConnection( <connstring> )
oRs := FW_OpenRecordSet( oCn, "SELECT * FROM MYTABLE WHERE DOCDATE >= " + FW_DateToSQL( dDate. 'D' ) )

// OR
oRs := FW_OpenRecordSet( <connstring>, "SELECT * FROM MYTABLE WHERE DOCDATE >= " + FW_DateToSQL( dDate, 'D' ) )
 

In case of Oracle, the above expression is resolved as
SELECT * FROM MY TABLE WHERE DOCDATE >= DATE 'YYYY-MM-DD'

You can confidently use the same code for any RDBMS, without change.
Once you open the connection, you can even forget the RDBMS if you use these functions.

Notes:
DTOC( dDate ) format may be acceptable in some situations, but please avoid this at all costs. Please always rely on "YYYY-MM-DD" format which is unambiguous.

[nageswaragunupudi]

I have a few suggestions while using Date values in the where clauses for querying SQL databases.

We should not assume that the date fields do not contain time-part also.
We should also not assume that the Date variable we are using does not contain time-part.

Let us consider this case:

Code: Select all  Expand view

dDate := oRs:Fields( n ):Value
cSql := "SELECT * FROM DELIVERIES WHERE DELIVERYDATE = " + FW_DateToSQL( dDate )
 

This query does not give the expected results if either dDate contains a time=part or the field DELIVERYDATE contains datetime values with time-part.

The following query is sure to give the correct results in all cases.

Code: Select all  Expand view

cSql := "SELECT * FROM DELIVERIES WHERE DELIVERYDATE >= " + FW_DateToSQL( dDate, 'D' ) AND DELIVERYDATE < FW_DateToSQL( dDate + 1, 'D' )
 

Second parameter 'D' is optional. If specified, it truncates the time-part of the date variable if any. If we are sure that the date variable is of ValType 'D', we can omit the second parameter.

Similarly if we are querying transactions in a range of dates ( say d1, d2 ) let us not be tempted to use a construct like "FLDDATE BETWEEN d1 AND d2". Even if d1 and d2 are truncated dates, if FIDDATE has datetime values with time-part, we miss all transactions on d2.

Again the safest query is :

Code: Select all  Expand view

cSql := "SELECT * FROM DELIVERIES WHERE DELIVERYDATE >= " + FW_DateToSQL( d1, 'D' ) AND DELIVERYDATE < FW_DateToSQL( d2 + 1, 'D' )
 

[lucasdebeltran]

Mr. Nages,

Thank you very much.

About using logical fields, I do for example:

if oRs:Fields( "PRESTA" ):Value = .T.
[..]

But you advise me to use 1 or 0 for logicals as to work with Oracle.

But if I change .t. to 1 I get an error.

Thank you very much.

[Enrico Maria Giordano]

Lucas,

About using logical fields, I do for example:

if oRs:Fields( "PRESTA" ):Value = .T.
[..]

But you advise me to use 1 or 0 for logicals as to work with Oracle.

But if I change .t. to 1 I get an error.

You have to use 0 or 1 in SQL statements and .F. or .T. in ADO statements.

EMG

[nageswaragunupudi]

Mr. Nages,

Thank you very much.

About using logical fields, I do for example:

if oRs:Fields( "PRESTA" ):Value = .T.
[..]

But you advise me to use 1 or 0 for logicals as to work with Oracle.

But if I change .t. to 1 I get an error.

Thank you very much.

Mr Lucas
You are right.

Here our aim to use the same code for all RDBMSs fails.

We can use BIT fieldtype in MSACCESS, SQLSERVER, MYSQL, SQLITE3 ( probably POSTGRE SQL which has a Boolean field type ). ADO recognizes BIT fields as adBoolean type. Value of this field is read as .T. or .F., though actually the data stored in the database table is 1 or 0.
We can use expressions like "if oRs:Fields(n):Value" or "if oRs:fields(n):Value == .t.". etc.

While assigning value to these fields in ADO, we can assign either a logical value ( .T. of .F. ) or a numeric value ( 1 or 0 ). Both work.

But with Oracle and Firebird, we have no choice but to use either NUMBER/NUMERIC(1) or CHAR(1). For obvious reasons we prefer NUMBER(1) with values 1 or 0.

ADO reads them as numeric values only and not logical values. We can not use these field values like logical values for comparisons.

Only compatible way appears to be
if Empty( oRs:Fields( "booleanfield" ):Value ) or ! Empty( ... )
But this usage is ugly and not intutively clear.

In my personal case, I always use Wrapper classes which take care of the difference between oracle and others. So I use them like normal logical fields.

As far as assignment goes, assigning 1 or 0 (even in ADO) works uniformly with all databases.

Here, Mr. EMG's approach is worth mentioning. If we see his code for creation of tables, he uses INT for logical values. ( we can change it as NUMBER(1) for Oracle)
Using this approach we never deal with Logical values in our ADO code and instead we compare the field value with 1 or 0.