Help setting up DSN using Jade 6.3 ODBC

[IanBayly]

I have no Jade background at all, although I am currently working through the excellent tutorials. We have a need to extract data from a Jade system to a third party product. We envisage the simple queries will be handled direct by ODBC and the more complex data sets will be exported from Jade to SQL Server, Access or Excel. There are a number of images (held in Jade as blobs) which we need as jpg’s in the data we need to export.

I am having difficulty setting up the DSN for Jade ODBC driver 6.3.
I assume “Directory” is that which holds all files pertaining to an application.
Which “Jade.ini” file do I point to, I assume the one held in the above folder.
What schema name do I use? All attempts so far return an ODBC message “Database not found”, which of the many files is actually the database file?

It’s probably obvious I have a Microsoft background because I’m sure these are very basic questions to a Jade person.

Any help much appreciated.

Ian B

[BeeJay]

I assume “Directory” is that which holds all files pertaining to an application.

The "directory" is the fully qualified path to the database's "system" folder. If you are connecting in multi-user mode, this is the path as view by the DbServer process that opened the database ready to receive connections. The "Directory" may therefore be referring to a drive that does not exist on the machine on which you're defining the DSN.

Which “Jade.ini” file do I point to, I assume the one held in the above folder.

You need to point to a Jade.ini file that is "visible" to the machine on which you're defining the DSN, I normally use a Jade.ini in the "bin" directory I have installed on the machine on which I'm setting up the DSN. In the case of a multi-user connection to a DbServer on another machine you may not even have access to the "above folder" that you mention. This jade.ini file needs to have the appropriate details for connecting to the target DbServer using the ServerNodeSpecifications ini file setting in the [JadeClient] section of the ini file. This all assumes you're referring to the standard client version of the Jade ODBC driver, not a thin client version of the Jade ODBC driver.

What schema name do I use?

You need to use the Schema name and App name combination that refers to a valid schema and application as defined in the Jade database to which you're connecting. Further to this, the View Name specifies the relational view that was created in this Jade database that determines what classes and properties are being exposed for use from ODBC connections into your database.

ie: You only get to see the classes and properties that have been mapped into the relational view to which you are connecting.

All attempts so far return an ODBC message “Database not found”

This suggests that either the Directory does not match the path as seen by the database server, or that the ini file you are using does not have the correct ServerNodeSpecifications settings to connect to the DbServer.

which of the many files is actually the database file?

For most Jade databases, the various classes are generally mapped across a number of different .dat files to improve performance, reduce reorg times, etc. Suffice to say that all of the *.dat files in the "system" folder are part of your "database". The _control.dat file is the database control file that keeps track of allocating the various *.dat files that make up your "database", handles database restart recovery points, etc.

Cheers,
BeeJay.

[IanBayly]

Thanks BeeJay

Your reply explained a lot of mysteries!

Not there yet!
The exact connection error message is
"[Jade Software Corporation][JADE ODBC Driver] Database is unavailable (#1049)[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectattr failed IM006 0[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectattr failed (#0)"
A tutorial walked through creating a LibrarySchema and I think the application name is also LibrarySchema, and that is what I am entering in DSN setup screen.
I assume the view name is optional and I have left it blank.
Here is the content of ini file I point to (D:\Jade63\system\jade.ini". Any further pointers much appreciated:
~~~~~~~~~~~
[Jade]
Server=MultiUser
ShowSplashScreen=true
SplashScreenFile=<default>
AviFile=<default>
AviPos=<default>
StatusPos=<default>
StatusFontColor=<default>
ApplicationPos=<default>
ApplicationFontColors=<default>
SchemaPos=<default>
SchemaFontColors=<default>
ServerPos=<default>
ServerFontColors=<default>
PathPos=<default>
PathFontColors=<default>
VersionPos=<default>
VersionFontColors=<default>
ShowUserInterrupt=true
UseSystemTrayIcon=true
FormsUseDefaultSchemaLocale=false

[JadeClient]
ServerNodeSpecifications=TcpIp,LocalHost,6005
ThreadPriority=Normal
Language=eng
ObjectCacheSizeLimit=8M
TransientCacheSizeLimit=5M
InitializationHandlerLibrary=jomsupp
SamplingNode=false
AutomaticCacheCoherency=ServerDefault
ReadOnlySchema=false
ReadOnlySystemSchema=false
NotifyQueueDepthWarningThreshold=10000
PlatformOptions=<default>
MaxWaitAppStart=0
TransientDbPath=D:\Jade63\examples\erewhon

[JadeServer]
NodeName=LocalHost
NetworkSpecification1=TcpIp,Enabled,6005
DoubleDeadlockException=false
MaxWaitAppStop=45000
RunAsService=false
DisableUserSignOn=false
ServerTimeout=10000



[PersistentDb]
RetryResourceErrors=true
ResourceErrorRetryDelay=1000
ResourceErrorRetryLimit=0
ObjectTrace=false
PrintStatistics=false
ActivityLogDirectory=<default>
OpenAllFilesOnDbOpen=false
MaxWaitForQuietPoint=0
SystemFileDirectory=<default>
JournalRootDirectory=<default>
JournalArchiveDirectory=<default>
StateOverflowDirectory=<default>
SuppressDialogs=false
BackupStats=false
BackupBuffers=4
BackupBlockSize=64K
BackupThreadPriority=Normal
BackupCompressionLevel=Normal
EnableArchivalRecovery=false
ReorgWorkDirectory=<default>
ReorgBackupDirectory=<default>
DiskCacheEnabled=false
IndexCacheSizeLimit=128K
ObjectCacheSizeLimit=16M
PercentOverlayGoal=5
JournalSwitchInterval=0
DefInitialFileSize=16K
DefFileGrowthIncrement=64K
SpaceReuseOption=true
IndexCompressionRatio=95
EnableDeltaLogging=true
JournalMaxSize=64M
JournalMinSize=0
UseJournalDescriptions=false
JournalCloseAction=None
VerifyTransactionLog=false
ReorgWorkerThreads=1
[TransientDb]
RetryResourceErrors=true
ResourceErrorRetryDelay=1000
ResourceErrorRetryLimit=0
ObjectTrace=false
PrintStatistics=false
ActivityLogDirectory=<default>
SuppressDialogs=false
IndexCacheSizeLimit=128K
SpaceReuseOption=true
IndexCompressionRatio=95
DefInitialFileSize=16K
DefFileGrowthIncrement=64K
[JadeEnvironment]
ProgramDataDirectory=<default>
JadeWorkDirectory=<default>
[FaultHandling]
StandardExitValues=false
CrashLogDirectory=CrashLogs
ProcessDumpDirectory=ProcessDumps
SuppressProcessDump=false
SuppressFaultLog=false
[SyncDbService]
AuditCauseEvents=false
[JadeReorg]
ReorgWorkDirectory=<default>
ReorgSortDirectory=<default>
ReorgBackupDirectory=<default>
ReorgWorkerThreads=1
WorkerSortMemory=50M
[JadeInterpreter]
MethodCache=Multiple
MethodCacheLimit=512K
StringPoolLimit=5M
WriteEnabled=true
[JadeSecurity]
DevelopmentSecurityLibrary=<none>
SchemaEncryptionHookLibrary=<default>
[JadePrinting]
PrintFileFormat=SVG
PrintDataType=GDI
GeneratedImageResolution=300
[JadeCompiler]
CompilerLockTimeout=10000
NonIDECompilerLockTimeout=0
[JadeOdbc]
MaximumResultSetSize=0
[JadeHelp]
ConnectSleepRetries=5,1
[JadeInterpreterOutputViewer]
FileCapture=false
CaptureFileName=JadeOut.log
WindowAlwaysOnTop=false
WindowPos=500,50,300,400
[JadeLog]
LogDirectory=logs

[BeeJay]

The exact connection error message is
"[Jade Software Corporation][JADE ODBC Driver] Database is unavailable (#1049)[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectattr failed IM006 0[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectattr failed (#0)"

I'll assume for the remainder of this posting that you're running this all on the one machine in my replies. The 1049 Database Unavailable error occurs when you try to open a Jade Database that is already opened. This could be by a Jade DbServer, a Jade AppServer running in singleUser mode, or a Jade.exe running in single user mode.

What other Jade processes did you have running on your machine at the time that could have had that database in use?

I suspect, if you're just working through the tutorials, that you're probably just running the Jade IDE via a shortcut that launches the jade.exe in singleUser mode and that you've setup your DSN to also open the database in singleUser mode. If that suspicion is correct, you will need to either shutdown the Jade IDE before you try to connect using your DSN, or you need to setup your system to have a DbServer opening the database and then connect both your Jade IDE and your DSN in multi-user mode to the DbServer.

A tutorial walked through creating a LibrarySchema and I think the application name is also LibrarySchema, and that is what I am entering in DSN setup screen.
I assume the view name is optional and I have left it blank.

The View name is mandatory and you need to have setup a relational view using the Jade IDE to specify which classes and properties you want to expose to ODBC connections. You do this through the Browse -> Relational Views menu option inside the Jade IDE to open the Relational Views Browser. From there, you use the Relational -> Add menu option to launch the relational view wizard to create your new relational view. The wizard will guide you through selecting which classes and properties you want exposed in this relational view.

Cheers,
BeeJay.

[IanBayly]

Again thanks BeeJay.

All is on one PC, running from jade.exe shortcut

I did in fact have Jade running and have now retried with Jade shutdown, message has changed to read "Invalid application" (#1214).
I'll set up a view and see if that provides a fix.

Thanks

Ian B

[BeeJay]

I did in fact have Jade running and have now retried with Jade shutdown, message has changed to read "Invalid application" (#1214).
I'll set up a view and see if that provides a fix.

That error is pretty specific to the application name as specified not being found in the schema being specified. I'm not sure what error you'll actually get when there is no view is specified as I've never actually tried connecting without a view defined.

One thing that can catch you out is that both the Schema and Application names are case-sensitive.

Cheers,
BeeJay.