Very Computer

Howdy all. I'm encountering a problem using ~tilde variables in both
regular in-line code and SQL(executeSQL) queries.
For inline query, no records are returned but if run interactively, I
get records. If I remove the ~tilde variable from the code, it returns
records.
Similarly, the SQL query also runs interactively but when run using
~tilde variable it displays an error - "An error was triggered in the
executeSQL method of an object of SQl type; Type mismatch in
expression." If I provide the value, it returns records.

BTW, I'm using Pdox7/32, Win NT4 Workstation, 96Megs Ram, HAIDATA
alias points to an NT4 network drive.

I must be too close to problem/frustrated to see what I'm missing...
Appreciate your suggestion/comments. Thanks in advance.

Rey

Following is test code used.

method pushButton(var eventInfo Event)

VAR

   EffDate                              Date
   BatchNum                     String
   tvPostedTrans                        TableView
   Mini                         Query
   QBESQLtest                   SQL
   DBData                       Database

EndVAR

DoDefault

;EffDate = dateVal("03/14/2000")
;BatchNum = strVal(14666)
BatchNum = "14666"

{
Mini = Query

:haidata:unittran.DB | AutoIDNum  | UnitNumFK  | TranDate | TranBatch |
                           | CheckPlus    | CheckPlus   | _L1  | _L2 |

:haidata:unittran.DB | TranNum    | Participant | Amount     | DateTime |
                     | CheckPlus  | CheckPlus   | CheckPlus  | Check
|

:haipriv:useractv.DB | EfftvDate      | BatchNum   |
                            | _L1, ~EffDate| _L2, ~BatchNum |

EndQuery
; set mouse cursor to wait, persistent
setMouseShape(MouseWait,True)

message("Please wait...this takes time")

IF NOT mini.executeQbe(":PRIV:answer.db") THEN
        setMouseShape(MouseArrow,FALSE)
        msgStop("Problems", "Uable to run Miniqbe")
ELSE
        setMouseShape(MouseArrow,FALSE)
        tvPostedTrans.open(":PRIV:answer.db")
        tvPostedtrans.wait()
        tvPostedTrans.close()
EndIF

Quote:}

; testing SQL query to see if it returns hits

DBData.open(":HAIDATA:")

setMouseShape(MouseWait,True)

message("This will take some time....")

QBESQLtest=SQL

             Select tranbatch, autoIDNum, UnitNumFK, trandate,
participant,amount
             from unittran where Tranbatch="14666"

           endSQL

if QBESQLtest.executeSQL(dbdata) then
        setMouseShape(MouseArrow,False)
        msginfo("SQL Works", "SQL QBE did it")
endif

endMethod

The differences between SQL and QBE is about SQL syntaxe .

Here is an example :
---------------------------------------------------
method run(var eventInfo Event)
var
   BD        DataBase
   Q         SQL
   TV        TableView
   criteria  string
endVar
BD.open("TRAVAIL")
criteria = "'FB'"   ; notice the comma expected for a strinf SQL value
Q =
SQL

/*

Answer: :PRIV:ANSWER.DB
Type: PARADOX
Constrained: False
AuxTables: True
RunMode: Default
Alias: WORK
LiveAnswer: FALSE

*/

SELECT *
FROM Personne
WHERE
(CODE_PERS = ~criteria)

ENDSQL
Q.executeSQL(BD)
TV.open(":PRIV:ANSWER.DB")

endMethod

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

In a QBE query you can miss the double quote, in SQL you must place
the simple quote.
The same query in QBE would be :

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

criteria = "FB" ; or criteria = "\"FB\"" more efficient !!!
QRY =
Query

Personne.db | CODE_PERS | CODE_SITE | CODE_SRV | NOM_PERS | PRENOM_PERS
|
            | Check FB  | Check     | Check    | Check    | Check      
|

Personne.db | DATE_NAISSANCE_PERS | POSTE_TEL_PERS | E_MAIL_PERS |
            | Check               | Check          | Check       |

EndQuery
QRY.executeQBE(":PRIV:ANSWER.DB")

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

WHY more efficient ???

because if you have a person named OR, this first syntax of the query
must occur an error, because OR is a QBE query operator.
So remember to ever use comma in QBE queries !!!

Frederic BROUARD - AMARIS SA Ile de France
Paradox - Delphi - SGBDR / RDBMS - SQL
http://perso.club-internet.fr/brouardf

Rey Collazo a crit :

Quote:

> Howdy all. I'm encountering a problem using ~tilde variables in both
> regular in-line code and SQL(executeSQL) queries.
> For inline query, no records are returned but if run interactively, I
> get records. If I remove the ~tilde variable from the code, it returns
> records.
> Similarly, the SQL query also runs interactively but when run using
> ~tilde variable it displays an error - "An error was triggered in the
> executeSQL method of an object of SQl type; Type mismatch in
> expression." If I provide the value, it returns records.

> BTW, I'm using Pdox7/32, Win NT4 Workstation, 96Megs Ram, HAIDATA
> alias points to an NT4 network drive.

> I must be too close to problem/frustrated to see what I'm missing...
> Appreciate your suggestion/comments. Thanks in advance.

> Rey

> Following is test code used.

> method pushButton(var eventInfo Event)

> VAR

>    EffDate                              Date
>    BatchNum                     String
>    tvPostedTrans                        TableView
>    Mini                         Query
>    QBESQLtest                   SQL
>    DBData                       Database

> EndVAR

> DoDefault

> ;EffDate = dateVal("03/14/2000")
> ;BatchNum = strVal(14666)
> BatchNum = "14666"

> {
> Mini = Query

> :haidata:unittran.DB | AutoIDNum  | UnitNumFK  | TranDate | TranBatch |
>                            | CheckPlus    | CheckPlus   | _L1  | _L2 |

> :haidata:unittran.DB | TranNum    | Participant | Amount     | DateTime |
>                      | CheckPlus  | CheckPlus   | CheckPlus  | Check
> |

> :haipriv:useractv.DB | EfftvDate      | BatchNum   |
>                             | _L1, ~EffDate| _L2, ~BatchNum |

> EndQuery
> ; set mouse cursor to wait, persistent
> setMouseShape(MouseWait,True)

> message("Please wait...this takes time")

> IF NOT mini.executeQbe(":PRIV:answer.db") THEN
>         setMouseShape(MouseArrow,FALSE)
>         msgStop("Problems", "Uable to run Miniqbe")
> ELSE
>         setMouseShape(MouseArrow,FALSE)
>         tvPostedTrans.open(":PRIV:answer.db")
>         tvPostedtrans.wait()
>         tvPostedTrans.close()
> EndIF

> }

> ; testing SQL query to see if it returns hits

> DBData.open(":HAIDATA:")

> setMouseShape(MouseWait,True)

> message("This will take some time....")

> QBESQLtest=SQL

>              Select tranbatch, autoIDNum, UnitNumFK, trandate,
> participant,amount
>              from unittran where Tranbatch="14666"

>            endSQL

> if QBESQLtest.executeSQL(dbdata) then
>         setMouseShape(MouseArrow,False)
>         msginfo("SQL Works", "SQL QBE did it")
> endif

> endMethod

Quote:> EffDate Date
> ;EffDate = dateVal("03/14/2000")
> ;BatchNum = strVal(14666)
> BatchNum = "14666"

tilde variables should be strings.
try

datestring = "03/14/2000"

 :haipriv:useractv.DB | EfftvDate | BatchNum |
     | _L1, ~datestring   | _L2, ~BatchNum |

Richie

Sent via Deja.com http://www.deja.com/
Before you buy.