Paradox to Sybase SQL query problem.

[beanbrain]

I am using both Paradox 7.0 and Paradox 10.0 on a Windows NT 4.0 network. Lately I was asked to provide data to Sybase Adaptive Server AS 12.5. I perform this connection via an ODBC link which Paradox sees as an alias.

The problem is Paradox 7.0 runs this beautifully without error and Paradox 10.0 cannot.

Typically I get "An error was triggered in the executeSQL method on an object of SQL type." This is followed by "Parameter not set in query string." These two errors occur right at the executeSQL statement.

Of course I need this to run in Paradox 10 along with all the other automated processes that have been created.

Here is the script code that is executed. Note that the SQL calls are stored as SQL statements in Paradox.

[tt]
method run(var eventInfo Event)
var
q Query
s String
ar Array[] String
tbl Table
tc tCursor
dDb, myDb Database
s1 SQL
dbParmsDA DynArray[] AnyType
firstSes,
secondSes Session
endVar

disablePreviousError(Yes)
errorClear()

message("Updating SPersonnel&quot

tbl.attach(":busoff:SPersonnel&quot
if not tbl.empty() then
message("Unable to empty SPersonnel table.&quot
return
endIf
tbl.unAttach()

q = Query
ANSWER: RIV:ANSWER.DB

SPersonnel.db | Last Name | First Name | SSN | Employee ID |
Insert | _join1 | _join2 | _join3 | _join4 |

SPersonnel.db | ISI Employment Start Date | ISI Employment End Date | Email |
| _join5 | _join6 | _join8 |

SPersonnel.db | Supervisor | Division Number | Staff Category |
| _join7 | _join9 | _join10 |

SPersonnel.db | Visitor Number |
| blank |

ersonnel:Entity.db | EmplNo | FirstName |
| _join4, not X.., not N.., not V.. | _join2 |

ersonnel:Entity.db | LastName | EmpStart | EmpTerm |
| _join1 | _join5 | _join6, blank or >=7/1/2002 |

ersonnel:Entity.db | SSN | StaffCat | Division | PrjLdr | Email |
| _join3 | _join10 | _join9 | _join7 | _join8 |

EndQuery
if not q.executeQBE() then
message("Unable to insert Records to SPersonnel.&quot
return
endIf

q = Query
ANSWER: RIV:ANSWER.DB

SPersonnel.db | Last Name | First Name | SSN | Employee ID |
Insert | _join1 | _join2 | _join3 | blank |

SPersonnel.db | ISI Employment Start Date | ISI Employment End Date | Email |
| _join5 | _join6 | _join8 |

SPersonnel.db | Supervisor | Division Number | Staff Category |
| _join7 | _join9 | _join10 |

SPersonnel.db | Visitor Number |
| _join4 |

ersonnel:Entity.db | EmplNo | FirstName |
| _join4, V.., not X.., not N.. | _join2 |

ersonnel:Entity.db | LastName | EmpStart | EmpTerm |
| _join1 | _join5 | _join6, blank or >=7/1/2002 |

ersonnel:Entity.db | SSN | StaffCat | Division | PrjLdr | Email |
| _join3 | _join10 | _join9 | _join7 | _join8 |

EndQuery
if not q.executeQBE() then
message("Unable to insert Records to SPersonnel.&quot
return
endIf

message("Updating SAccount&quot

tbl.attach(":busoff:SAccount&quot
if not tbl.empty() then
message("Unable to empty SAccount table.&quot
return
endIf
tbl.unAttach()
; populate the intermediate table for Accounts
q = Query
ANSWER: RIV:ANSWER.DB

SAccount.DB | Account Number | Account Name | Burden Center | Award Number |
Insert | _join1 | _join2 | _join3 | _join4 |

SAccount.DB | Account Status ID | Account Start Date | Account End Date |
| _join5 | _join6 | _join7 |

SAccount.DB | Account Manager |
| _join8 |

:Contract:Chart.DB | AcctNo | AcctName | Location | StrtDate |
| _join1 | _join2 | _join3 | _join6 |

:Contract:Chart.DB | EndDate | ProjLdr | AwardNo | AcctStatus |
| _join7, >6/30/2002 | _join8 | _join4 | _join5 |

EndQuery
if not q.executeQBE() then
message("Unable to insert Records to SAccount.&quot
return
endIf

; using tCursor scan SAccount for multiple ProjMgr names in field and truncate at "/".
message("Reducing Project Manager name.&quot
tc.open(":busoff:SAccount.db&quot
tc.edit()
scan tc :
s = tc."Account Manager"
s.breakApart(ar,"/&quot
tc."Account Manager" = ar[1]
endScan
tc.endEdit()
tc.close()

if not exportASCIIVar ( "SPersonnel.db", "SPersonnel.txt", ",", "\"", False, True ) then
message("Error Export","Unable to Export SPersonnel.txt.&quot
return
endIf
if not exportASCIIVar ( "SAccount.db", "SAccount.txt", ",", "\"", False, True ) then
message("Error Exporting","Unable to Export SAccount.txt.&quot
return
endIf

message("Writing to SyBase P_Account.&quot

removeAllPasswords()
firstSes.open()
;firstSes.addPassword("Paradox&quot
dbParmsDA["OPEN MODE"] = "READ/WRITE"
dbParmsDA["Password"] = "Paradox"
dbParmsDA["SQLPASSTHRU"] = "SHARED AUTOCOMMIT"
if not dDb.open(":bo_personnel:",firstSes, dbParmsDA) then ; (remote is an alias defined elsewhere)
message("Unable to connect to :bo_personnel: database.&quot
return
endIf

message("Writing to SQL SyBase P_Personnel.&quot
s1.readFromFile("SPersonnel.sql&quot
if not executeSQL(dDb, s1) then
message("Unable to run SPersonnel SQL query.&quot
firstSes.close()
return
endIf

message("Writing to SQL SyBase P_Account.&quot
s1.readFromFile("SAccount.sql&quot
if not executeSQL(dDb, s1) then
message("Unable to run SAccount SQL query.&quot
firstSes.close()
return
endIf

dDb.close()
message("Done...&quot
exit()
tc.open("LastMsgDate.db&quot
tc.edit()
tc."LastDirUpdateDate" = today()
tc.endEdit()
tc.close()

endMethod
[/tt]

Finally I can also post the SQL statements called above if this is helpful.

Thanks in advance for any help you can provide.

 

[Langley]

Have you checked to make sure the s1 variable contains the information from the file? There is no error trapping there. maybe you should do an IF / NOT when you read it in to make sure it is getting assigned.

Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[beanbrain]

So Mac. Why would a readFromFile fail if I'm able to open the SQL statement and execute it from within Paradox? Also why would it work in Paradox 7.0 and not 10.0?

Also I did as you suggest and place an if not in front of the readFromFile like this.

if not s1.readFromFile("SPersonnel.sql&quot then
message("Unable to read from SPersonnel.sql&quot
return
endIf

The program breezed right past that and executed in Paradox 7.0. Still having problems with P10.

 

[Langley]

The reason I suggested it was that, since you are using two versions of Pdox, there might have been an issue with aliases or working directories. The readFromfile statement appears to try and locate SPersonnel.sql in the working directory only.

It was just a thought since I did not see anything else wrong.

Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[beanbrain]

Mac:

I'll try putting the alias in front of the file name*. The thing is the script and sql queries are all located in the :work: area.

Also, when I execute this in Paradox 10.0 I get the messgage "Parameter not set in Query String." This happens at the point of read but it does not fire the

if/not

. Strange!

It's almost as if Paradox 10.0 expects some other setting to be addressed either withing the SQL statement or when I initially open the db.

* This didn't make any difference.

 

[Langley]

Well, I don't do much SQL so I know I'm not much help.

However, I would still examine the contents of the s1 variable just to be absolutely sure everything is being read in properly.

I believe the error message is the same you'd get if you have an unassigned variable in a QBE statement. I don't know if the same holds true for SQL statements.

Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[beanbrain]

Thanks Mac. I'll keep looking. If I figure out I'll post it here.