Show Posts
This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.
1
ODBC / Re: Dictionary definition for dates in YYMMDD format
« on: March 06, 2023, 09:42:40 AM »
You can define the class as DATE-YYMMDD
https://manual.pvxplus.com/?odbc/table_definitions/classes.htm
This will tell the ODBC driver that the field is a date. From excel you can just ignore the format the date is stored in and use the standard SQL date format YYYY-MM-DD in your SQL query.
So if using the query wizard to get data you can just define a filter for that field <= 2022-06-30.
Or if you use the Microsoft Query you can edit the SQL query directly it would be something like
SELECT * FROM myTable WHERE dateFld <= '2022-06-30'
https://manual.pvxplus.com/?odbc/table_definitions/classes.htm
This will tell the ODBC driver that the field is a date. From excel you can just ignore the format the date is stored in and use the standard SQL date format YYYY-MM-DD in your SQL query.
So if using the query wizard to get data you can just define a filter for that field <= 2022-06-30.
Or if you use the Microsoft Query you can edit the SQL query directly it would be something like
SELECT * FROM myTable WHERE dateFld <= '2022-06-30'
2
ODBC / Re: ODBC Date Issue
« on: March 06, 2023, 09:28:23 AM »
The PxPlus SQL ODBC driver supports that date class.
When using SQL though you use the SQL date format (YYYY-MM-DD) the driver does the conversion to how the date is stored in your file. So assuming your data dictionary has set the date class for the field correctly if you do a SQL query like
SELECT * FROM myTable WHERE datefld > '2022-09-01'
you should get what you want.
When using SQL though you use the SQL date format (YYYY-MM-DD) the driver does the conversion to how the date is stored in your file. So assuming your data dictionary has set the date class for the field correctly if you do a SQL query like
SELECT * FROM myTable WHERE datefld > '2022-09-01'
you should get what you want.
3
ODBC / Re: ODBC Date Issue
« on: March 03, 2023, 03:26:31 PM »
To answer your question I am only referring to the WHERE clause.
and the format is YYYY-MM-DD not YYYY/MM/DD dashes and not forward slashes.
and the format is YYYY-MM-DD not YYYY/MM/DD dashes and not forward slashes.
4
ODBC / Re: ODBC Date Issue
« on: March 03, 2023, 02:18:44 PM »
I am able to filter based on a date field in Excel just fine.
As I said earlier the issue may be the date format you are trying to use. The ODBC expects YYYY-MM-DD for date fields.
As I said earlier the issue may be the date format you are trying to use. The ODBC expects YYYY-MM-DD for date fields.
5
ODBC / Re: ODBC Date Issue
« on: March 03, 2023, 09:14:08 AM »
I can see a few possible issues:
1. your WHERE clause is not using the correct field name. It should be Order_Date not Order Date. You need the underscore and not a space. Then again maybe that is just an issue with typing it out for the forum but it is in both your examples so it could be the issue.
2. Use single quote not double quote.
2. If Order_date is defined with a DATE class you may need to use the date format yyyy-mm-dd in your SQL query.
WHERE Order_Date > '2022-09-01'
1. your WHERE clause is not using the correct field name. It should be Order_Date not Order Date. You need the underscore and not a space. Then again maybe that is just an issue with typing it out for the forum but it is in both your examples so it could be the issue.
2. Use single quote not double quote.
2. If Order_date is defined with a DATE class you may need to use the date format yyyy-mm-dd in your SQL query.
WHERE Order_Date > '2022-09-01'
6
ODBC / Re: Operator error
« on: February 28, 2023, 08:29:10 AM »
You must be using a older version of the ODBC driver. Since version 6 (released 2016) of the PxPlus SQL ODBC driver it supports dates with or without the date being escaped.
7
ODBC / Re: DATE field query
« on: February 28, 2023, 08:26:34 AM »
You can use a Scalar functions (https://manual.pvxplus.com/?odbc/using_odbc_driver/scalar_functions.htm) to modify what is returned by the select:
SELECT LEFT(SO_SalesOrderHeader.OrderDate, POSITION(" " IN SO_SalesOrderHeader.OrderDate) - 1) From myTable
SELECT LEFT(SO_SalesOrderHeader.OrderDate, POSITION(" " IN SO_SalesOrderHeader.OrderDate) - 1) From myTable
8
ODBC / Re: Views names showing up, but no data
« on: February 24, 2023, 09:09:06 AM »
The ViewLib= setting should be to the directory where you can find libpvx.so file. So if your PxPlus is installed at /opt/myapp and the libpvx.so is /opt/myapp/libpvx.so then the ViewLib= configuration setting should be ViewLib=/opt/myapp
Another issue could be the activation/license of the PxPlus where we are using the view library from is not active. Views won't work if the PxPlus where the view lib is from is expired or has no activation.
PxPlus products do not automatically update so something else on the system must have changed. If you can figure out what changed that will help identify the issue.
Maybe the activation expired?
Maybe someone made a configuration change and moved files or changed permissions etc?
New security or antivirus software/rules preventing something from working?
Another issue could be the activation/license of the PxPlus where we are using the view library from is not active. Views won't work if the PxPlus where the view lib is from is expired or has no activation.
PxPlus products do not automatically update so something else on the system must have changed. If you can figure out what changed that will help identify the issue.
Maybe the activation expired?
Maybe someone made a configuration change and moved files or changed permissions etc?
New security or antivirus software/rules preventing something from working?
9
Language / Re: DD numeric definition for SQL validation
« on: February 15, 2023, 02:21:38 PM »
I also tried 55.12999 and it becomes 55.13 in the database so SQL Server is rounding to the 2 decimal places.
10
Language / Re: DD numeric definition for SQL validation
« on: February 15, 2023, 02:07:28 PM »
I Just tried updating a field in a SQL Server database with a non PxPlus program. The field was defined as decimal(10,2) and I set it as 55.123456789. It is now in the DB as 55.12.
I think this confirms that this is SQL Server behavior or more likely SQL behavior.
I think this confirms that this is SQL Server behavior or more likely SQL behavior.
11
Programming / Re: Drag and Drop
« on: January 24, 2023, 08:05:24 AM »
You can allow external applications to drop file names onto a list box by using this directive.
DROP FILE ON dest_ctl_id RETURN new_ctl_id
Whenever a file is dropped onto the dest_ctl_id control, the system will generate the CTL event specified by new_ctl_id.
The application can get a list of the file pathnames being dropped from the FIN(0, "DROPFILES") function call. All pathnames will be fully expanded and separated by a SEP character.
This can be found in the docs here: https://manual.pvxplus.com/?directives/drop_on.htm#filedrop
DROP FILE ON dest_ctl_id RETURN new_ctl_id
Whenever a file is dropped onto the dest_ctl_id control, the system will generate the CTL event specified by new_ctl_id.
The application can get a list of the file pathnames being dropped from the FIN(0, "DROPFILES") function call. All pathnames will be fully expanded and separated by a SEP character.
This can be found in the docs here: https://manual.pvxplus.com/?directives/drop_on.htm#filedrop
12
ODBC / Re: sorry for topic where is the catalog on odbc
« on: January 09, 2023, 09:44:35 AM »
When defining a catalog you can specify just a data dictionary. You can leave the ini field blank if you have no INI defining files.
What you specify in the data dictionary field is the path to the providex.ddf file that got created when you created a dictionary. This will be in your application directory likely with your other program files and data files.
For example if the DDF is located at C:\myapp\data\providex.ddf, then use C:\myapp\data.
The catalog is just telling the PxPlus SQL server where the data is. Defining it on the server means clients don't have to know where the data is and just point to a catalog to access the data defined in it.
Make sure the PxPlus SQL ODBC driver is the same version as the PxPlus SQL Server for compatibility. Older versions did not use the catalog and won't have the field.
What you specify in the data dictionary field is the path to the providex.ddf file that got created when you created a dictionary. This will be in your application directory likely with your other program files and data files.
For example if the DDF is located at C:\myapp\data\providex.ddf, then use C:\myapp\data.
The catalog is just telling the PxPlus SQL server where the data is. Defining it on the server means clients don't have to know where the data is and just point to a catalog to access the data defined in it.
Make sure the PxPlus SQL ODBC driver is the same version as the PxPlus SQL Server for compatibility. Older versions did not use the catalog and won't have the field.
13
Programming / Re: Server parameter in CALL "*web/sftp;receive"
« on: January 09, 2023, 09:34:24 AM »
Using ; should always work.
What is the error you got when using ;?
What is the error you got when using ;?
14
ODBC / Re: join left and right join tables
« on: January 09, 2023, 08:54:10 AM »
Some descriptions of the different joins are found here: https://manual.pvxplus.com/?odbc/using_odbc_driver.htm
Some examples are foudn here: https://manual.pvxplus.com/?odbc/using_odbc_driver/example_sql.htm
My guess is this is what you want.
select * from { OJ (IM1_inventoryMasterFile left outer join IMA_ProductLine on IMA_ProductLine.ProductLineCode=IM1_inventoryMasterFile.productLine) left outer join AP1_VendorMaster on AP1_VendorMaster.VendorNumber=IM1_inventoryMasterFile.PrimaryVendorNumber } where IM1_inventoryMasterFile.itemnumber=:pprod
Not that if you are using the PxPlus SQL ODBC driver version 6 or higher you can simplify the syntax to remove the { oJ }
select * from (IM1_inventoryMasterFile left outer join IMA_ProductLine on IMA_ProductLine.ProductLineCode=IM1_inventoryMasterFile.productLine) left outer join AP1_VendorMaster on AP1_VendorMaster.VendorNumber=IM1_inventoryMasterFile.PrimaryVendorNumber where IM1_inventoryMasterFile.itemnumber=:pprod
Some examples are foudn here: https://manual.pvxplus.com/?odbc/using_odbc_driver/example_sql.htm
My guess is this is what you want.
select * from { OJ (IM1_inventoryMasterFile left outer join IMA_ProductLine on IMA_ProductLine.ProductLineCode=IM1_inventoryMasterFile.productLine) left outer join AP1_VendorMaster on AP1_VendorMaster.VendorNumber=IM1_inventoryMasterFile.PrimaryVendorNumber } where IM1_inventoryMasterFile.itemnumber=:pprod
Not that if you are using the PxPlus SQL ODBC driver version 6 or higher you can simplify the syntax to remove the { oJ }
select * from (IM1_inventoryMasterFile left outer join IMA_ProductLine on IMA_ProductLine.ProductLineCode=IM1_inventoryMasterFile.productLine) left outer join AP1_VendorMaster on AP1_VendorMaster.VendorNumber=IM1_inventoryMasterFile.PrimaryVendorNumber where IM1_inventoryMasterFile.itemnumber=:pprod
15
Programming / Re: Passing a COM Object to a CMD
« on: November 18, 2022, 09:55:59 AM »
The pvxtlb.exe is a separate download: https://home.pvxplus.com/downloads/misc/PVXTLB.EXE
For information on it see here:
https://manual.pvxplus.com/PXPLUS/PxPlus%20User%20Guide/External%20Components/PxPlus%20Type%20Library%20Browser/Overview.htm
For information on it see here:
https://manual.pvxplus.com/PXPLUS/PxPlus%20User%20Guide/External%20Components/PxPlus%20Type%20Library%20Browser/Overview.htm