Ms SQL

[Jure Zorko]

I need to communicate with some other software, which uses MsSQL.

I use standard DB tables in paradox.

Idea is to use some intermediate tables in SQL. So I would put data in them, other SW will read from them. And vice-versa.

They put some data in temp tables, I read from them (and do something in my app).

1. I use odbcad.exe to create System DNS of type "SQL Server".

2. I add it to "databases" in bdeadmin.exe

3. i create alias in paradox with 
     addAlias("SQLxy","SQL Server",AliasInfo)

       ( AliasInfo  is array with "DATABASE NAME","LANGDRIVER","ODBCDSN","USER NAME" and "PASSWORD")

Up to here everything is working. I can see tables in File-Open-Table in paradox.

And now problems:

1. I can open tables, but some show no data (just empty lines), some I cen see but not edit, some I can edit.

I presume problem is in field types.

Which field types from MsSQL can Paradox work with?

char(10) is OK, I think.

nchar(10) not OK (after some quick testing)

nvarcahr(10) not OK?

What to use for numbers and dates? "date", "datetime"?

What for numbers? "float", "real" ?

And for integers? "smallint", "int", "bigint" ?

2.  How to manipulate data in those SQL tables?

I have never used that localSQL language in paradox. I have always used QBE and TCursors.

For this project I do not need QBE, just TCursors.

* open TC:  if not TC.Open(":SQLalias:test") then errorshow() return  endIf

* insert:   if not TC.InsertRecord() then errorshow() return endIf

* data:          TC:"ID" = "XY"        TC."Num" = 123

* store:         TC.ĆUnlockRecord()   TC.Close()

From some quick testing I think "valid" SQL types are char, varchar, datetime,  float, int, bigint. Anything else?

3. I do not need transactions.

4. Can all TCursor functions be used on SQL table? 

qLocate doesn't work? How abput SetRange, SwitchIndex, LockRecord , CopyFromArray, CopyToArray,?

5. Can I do scan TC on SQL table? Is there a speed problem?

Thx Jure

[Kevin Zawicki]

When I have done this, data typing can be an issue and there are always new SQL datatypes, etc.

 

I have created temp tables in SQL where all the datatypes are char (plain text).

Then SQL query from Paradox and retrieve all data into a local Paradox temp table, all A255 field types.

 

Then in OPAL code transform that data into a second Paradox temp table with proper data types.

 

Basically, use plan data types in the middle, as a bridge.

 

Then same to go back. Send in plain Alpha data into SQL temp tables and then have receiving system transform as needed. Or have a stored proc in SQL that transforms that data into a properly typed SQL table.

 

>For this project I do not need QBE, just TCursors.

You might be better off moving the data as a listed above, then acting on in Paradox with OPAL, then send back.

 

I would not use TCursors to access SQL table directly.

[Mark Bannister]

I work with Postgresql and it's been a while since I've played with anything else so I can't help much.  Postgresql sounds like it works better than MsSQL.

I have few problems using local sql queries on tables but there are several caveats. I actually pass my queries through a opal method that "cleans" them so they work.  If you want to see that let me know.  

On Tuesday, December 16, 2025 at 10:14:26 AM UTC-6 Jure Zorko wrote:

[Jure Zorko]

>> I would not use TCursors to access SQL table directly.

Why not?  Is it known to break something? (like fmemo fields :-)  )

[Jure Zorko]

Yes, you can post your method.

Although I zhink I will not need it, because process will be ver y simple, pssing some IDs and numbers. All parameters u7nder my control, no user interaction.

[Mark Bannister]

General note

Build queries piece by piece.  You will sparse clues on why a query isn't working.

--

Quote marks:

There are issues with " vs ' vs nothing.

Example Update  table A when table B contains ID's we want to update  

UPDATE ':manager:R0DOCISSUED.DB'
SET  IssuedStatusFkey = CAST (2 as INTEGER)
WHERE  id IN
 (SELECT t1.'id' FROM ':priv:__sql_run65.db' t1)

Note That:  No table alias for the update table and no quotes around it's field names.
Where table does have a table alias and can have quotes around field names but use the single quote '

Things seem to work better using single quote over double.

If a table column name has spaces you will run into issues.

---

Do not use quote after return values AS clause

  YES: Select a.'field' AS NewFieldName   

  NO: Select a.'field' AS 'NewFieldName'   

This means no spaces in the AS name 

--

One big issue with local SQL is BDE aliases .  With this method I remove the default alias and change the other alias to their full paths instead of an alias.

 

method Text_ReplaceQueryAlias ( mytxt   String ;// query string
                               , stalias String  ;// this alias is the default and will be removed
                                                 ;/// , other aliases will be changed to full path
                               , var  newtext  String ) Logical
 
      var
           answer                       Logical
           mysettings dynarlo
           stParams                     String
           st1
              , stvalue
              , st3
              , sthold                  String
           ts                           TextStream
           sindex
              , Eindex
              , currIndex               LongInt
           stNewHOld                    String
           loLoop
              , loEOL                   Logical

              dbparams sql_RecType
 
      endVar
 
      try
           openlibs ( )
           lib[L_common].Settings ( mySettings , true )
           ;SetMouseShape(Mousewait,true)
           ;DelayScreenUpdates(yes)
           ErrorTraponwarnings ( yes )
           ; CODE
           sthold = mytxt
           newtext = ""
           stvalue = ""
           st3 = ""
           ignorecaseinStringCompares ( false )
 
           if sthold.sizeex ( ) > 255 then
                ;//  this match takes FOREVER sometimes if a long string
                ;//  so save it off to a text file and run the matches
                ;//  much faster
                ts.open ( LIB[ L_MISC ].FIleNameUniquePrivAny ( "__ReplaceTokens.txt" ) , "NW" )
                ts.writestring ( sthold )
                sindex = 1
                currindex = 1
                stNewHOld = ""

                while ts.advmatch ( sindex , eindex , ":[A-Za-z0-9]*:" )

                     if sindex - 1 > currindex then
                          ts.setposition ( currindex )
                          ts.readchars ( st1 , ( sindex ) - currindex )
 
                     else
                          st1 = ""
 
                     endIf
                     ts.setposition ( sindex )
                     ts.readchars ( stvalue , eindex - sindex )
                     ;// remove the brackets
                     stvalue = LIB[ L_STRINGS ].cmchopst ( stvalue ) ;// end bracket
                     stvalue = LIB[ L_STRINGS ].removeFirstChar ( stvalue )
                     ;//
                     switch
                      case LIB[L_MISC].cmIsAliasRemote( stvalue) :
                          ;// do not remove remote aliases
                        newtext = newtext + st1 + ":"
                          eindex = sindex + 1

      case  LIB[ L_MISC ].cmIsAlias ( stvalue ) :
 
                          if stvalue.lower ( ) = stalias.lower ( ) then
                               ;// an alias is not required  leave it out
                               newtext = newtext + st1
 
                          else
                               newtext = newtext + st1 + getaliaspath ( stvalue ) + "\\"
 
                          endIf
 
   otherwise :
                          ;// put the colon back cause not an alias
                          newtext = newtext + st1 + ":"
                          eindex = sindex + 1
 
                     endswitch

                     stNewHOld = LIB[ L_STRINGS ].removeFirstChars ( sthold , eindex - 1 )
                     currindex = eindex
                     sindex = currindex
 
                endWhile
                stHOld = stNewHOld
                ts.close ( )
 
           else
                ;// find text within {..}   on strings < 255 chars
                ;//              ([^:]*)    match zero or more NOT ":"
                ;//              :         followed by a colon
                ;//             ([A-Za-z0-9]+)     match one or more letters+numbers
                ;//               :        followed by a closing colon
                ;//              (..)       followed by anything
                while sthold.advmatch ( "([^:]*):([A-Za-z0-9]+):(..)" , st1 , stValue , st3 )
                     sthold = st3 ; all that is left
                     ;//convert stvalue to text
                     ;   stvalue = LIB[ L_STRINGS ].removeFirstChar( stvalue)

                     switch

                      case LIB[L_MISC].cmIsAliasRemote( stvalue) :
                        ;// do not replace this alias
                           newtext = newtext+ st1 +":"+ stvalue +":"

                           ;// @TODO FIX THIS 3/292/2020  should be
                           ;///  doing something smarter here
                           ;///   assuming we are using the manager alias that
                           ;///   will run locally
                            dbparams. USERNAME ="pdoxsuper"
                      dbparams.PASSWORD =SQL_SUPERUSER
                            dbparams.stDatabaseAlias = stvalue
                            try
                            SQL_OpenDatabase( dbparams )
                            onfail
                            errorclear()
                            endtry

                      case LIB[ L_MISC ].cmIsAlias ( stvalue ) :
 
                          if stvalue.lower ( ) = stalias.lower ( ) then
                               ;// an alias is not required  leave it out
                               newtext = newtext + st1
 
                          else
                               newtext = newtext + st1 + getaliaspath ( stvalue ) + "\\"
 
                          endIf

   otherwise :

                        ;// either bad alias or not an alias
                        ;// put the colons back   assume it is not an alias
                        newtext = st1 +":"+ stvalue +":"

 
                     endswitch
 
                endWhile
 
           endIf
 
           if newtext = "" then
                newtext = mytxt
 
           else
 
                if sthold <> "" then
                     newtext = newtext + sthold
 
                endIf
 
           endIf
           ;ENDCODE^^^^^^
           answer = True
 
      onFail
           lib[L_common].Errorlogshow ( lib[L_common].ErrorloggerAddMsg ( FormNameGL + "Text_ReplaceQueryAlias" ) )
           ; libCommon.Errorclosetc(,answer)
           answer = false
 
      endTry
      lib[L_common].Settings ( mySettings , false )
         ;SetMouseShape(MouseArrow,false)
         ;DelayScreenUpdates(No)
 
      return ( answer )
 
EndMethod