tag:blogger.com,1999:blog-6527688743456205256.post3134968060601044280..comments2023-12-18T09:38:33.215+11:00Comments on The Blas from Pas: Using derby style table functions to load data into SQLFirePas Apicellahttp://www.blogger.com/profile/09389663166398991762noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-6527688743456205256.post-35655468051843667592012-07-20T21:35:09.427+10:002012-07-20T21:35:09.427+10:00Hi Pas,
I figured out what the problem is with my...Hi Pas,<br /><br />I figured out what the problem is with my CLASSPATH.<br /><br />The actual folder structure, where the class/ jar is stored, up to the start of the package name, is considered superfluous to the java class, but is important to the execution.<br /><br />In your example, you expose your function externally as: vmware.au.se.sqlfire.derby.DepartmentTable, with "DepartmentTable", as the class name. When this function is called, it relies on this fully qualified name, starting at the point where the folder structure stops, i.e. the CLASSPATH.<br /><br />Example: folder- c:\myClasses\vmware\au\se\sqlfire\derby. The actual jar is stored inside the folder "derby". Add the folder path: c:\myClasses to CLASSPATH. When the function is called, it looks at CLASSPATH, to find the starting point for the EXTERNAL NAME of the function, and follows the folder-structure via the name, to get to the jar that is executed.<br /><br />I would imagine, if the entire path, i.e. C:\myClasses\"vmware.au.se.sqlfire.derby" is added to CLASSPATH, the function-EXTERNAL-NAME should be, "DepartmentTable.read" only.<br /><br />I opted to put the short path (without jar file reference) in the CLASSPATH, and leave the fully qualified path in the EXTERNAL NAME of the function, while storing the jar in a folder structure that mimics the name. This works for me.<br /><br />Thanks for your time and effort.Drikushttps://www.blogger.com/profile/06185354607285415707noreply@blogger.comtag:blogger.com,1999:blog-6527688743456205256.post-70448558992669574332012-07-20T18:44:10.921+10:002012-07-20T18:44:10.921+10:00Pas, thanks for the follow-up, I appreciate it.
I...Pas, thanks for the follow-up, I appreciate it.<br /><br />I am on version 102 (trial).<br /><br />The class path has a reference to the correct location: C:\SQLFire\Java Classes\co\za\jse\trdSchema\GetPrivateTrades.jar, for the started server. <br /><br />I make the client connection from a machine (not related) via a Locator (not related) to the one on which the database is hosted, i.e. three separate computers. The table function is created on the database, and the jar is file in the folder location (as below), on the actual machine, where the database runs. I then attempt to run the data-harvest, from the first computer, via the Locator, on the database computer. <br /><br />I am able to log onto the external db, using sqlf, and query the database.<br /><br />Class Path:<br /> C:\SQLFire\vFabric_SQLFire_102\lib\sqlfire.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\sqlfiretools.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\sqlfireclient.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\jline.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\jna.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\commons-cli.jar<br /> .<br /> C:\Program Files (x86)\Java\jre7\lib\ext\<br /> C:\Data Drivers\jtds-1.2.5.jar<br /> C:\SQLFire\Java Classes\co\za\jse\trdSchema\GetPrivateTrades.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\ant\ant.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\lib\commons-beanutils-1.7.0.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\lib\commons-codec-1.3.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\lib\commons-collections-3.1.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\lib\commons-dbcp-1.2.1.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\lib\commons-digester-1.7.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\lib\commons-lang-2.1.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\lib\commons-logging-1.0.4.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\lib\commons-pool-1.2.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\lib\dom4j-1.4.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\lib\log4j-1.2.8.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\lib\stax-api-1.0.1.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\lib\wstx-asl-3.0.2.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\ddlutils\dist\DdlUtils-1.0.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\commons-logging.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\commons-modeler-2.0.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\mx4j.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\mx4j-remote.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\mx4j-tools.jar<br /> C:\SQLFire\vFabric_SQLFire_102\lib\mail.jar<br /><br />I have verified that the package name is correctly spelled throughout.<br /><br />Thank you for your time and effort.Drikushttps://www.blogger.com/profile/06185354607285415707noreply@blogger.comtag:blogger.com,1999:blog-6527688743456205256.post-55651233190220342102012-07-20T14:24:11.634+10:002012-07-20T14:24:11.634+10:00Drikus,
1. What version of SQLFire are you using ...Drikus,<br /><br />1. What version of SQLFire are you using please ensure it's 102 which is the latest version<br /><br />2. What does your classpath setting look like can you show me that?<br /><br />3. The SQLFire logs for each server should show the JAR file with your class being picked up if not then the JAR your using is not being picked up.<br /><br />Thanks..Pas Apicellahttps://www.blogger.com/profile/09389663166398991762noreply@blogger.comtag:blogger.com,1999:blog-6527688743456205256.post-63765275367631861422012-07-20T01:42:36.048+10:002012-07-20T01:42:36.048+10:00Thanks for the good example. I have a problem wit...Thanks for the good example. I have a problem with my own table function, i.e. where you have the reference vmware.au.se.sqlfire.derby.DepartmentTable.read, to the jar file. Mine is very similar. I have also added it to the classpath, with an explicit jar file refernece, but it simply won't see the package.<br /><br />I have even placed the jar file in a folder structure that resembles the package name.<br /><br />Creating the table function, does not fail [EXTERNAL NAME 'co.za.acme.trdSchema.GetPrivateTrades.read';], but when running the INSERT statement [FROM TABLE (EQUITYTRADE.externalTrades()) t;], it produces the 42X51 error. <br /><br />I am running on Windows, which is the only difference, it would seem.<br /><br />Your thoughts are appreciated.Drikushttps://www.blogger.com/profile/06185354607285415707noreply@blogger.comtag:blogger.com,1999:blog-6527688743456205256.post-29831231992500652592012-03-17T02:21:09.358+11:002012-03-17T02:21:09.358+11:00Pas, also check out sysj.install_jar as (IMO) a be...Pas, also check out sysj.install_jar as (IMO) a better alternative to setting the classpath.<br /><br />Example: "call sqlj.install_jar('/home/carter/examples.jar', 'TEST', 0)"<br /><br />There is also a remove_jar and you can update classes without bouncing the server.Carter Shanklinhttp://blogs.vmware.com/sqlfirenoreply@blogger.com