I haven't tried this and don't know if it will work but have you tried specifying ftps:// and the port when using *web/ftp.

CALL "*web/ftp;receive", "ftps://vendorServer:990", user$, password$, 0, "someFile.ext", download_dir$, case$, result$

FTPS is just FTP+SSL and behind the scenes we use curl which supports FTPS so it may be as a simple as that. If that doesn't work likely we would need to update the utility to try to support it. In which case I recommend creating a wish list forum post so we can gauge interest.

Hi Thomas,

The examples in the POS() function documentation all look correct. An example in the documentation does not necessarily mean a code/program example. In the case of the POS() function and probably a lot of the other functions the example is just of use of the function. It shows you a specific call to the function and the example tells you what the result is. Obviously if you want to use the function in a code/program you need to do something with the output or you will get a error 20.

Making every example in the documentation actual code/program examples would possibly make the examples less concise.

By necessity the documentation must assume a basic understanding of PxPlus syntax. The basics are documented but all that info can't be on every page of the documentation. Beginners need to learn some of the basics like, that it is a syntax error to call a function and do nothing with the return value, and how line numbers work. If they do that they they should have no problem with the documentation.

*ufar should not be used with multi-segmented files or for file larger than 2GB. You should use *fixvlr instead.

*fixvlr is available in PxPlus 2016 or higher.

The PxPlus solution would be to:

OPEN (1,iol=*,opt="REC=PRODUCT,COMPANY,PRODCTDES,SKU")"[odb]mydsn;mytable"
SELECT * FROM 1 WHERE Product$='"+Prodct$+"' and company$='"+%C$+"""
...
NEXT RECORD

This would send the SQL "SELECT PRODUCT,COMPANY,PRODCTDES,SKU FROM mytable WHERE Product='myproduct' and company='mycompany'"

It returns a little more then just PRODCTDES,SKU but Pxplus needs to know those fields used in the WHERE clause are fields.

If the WHERE is the same for the entire open you can use a FORCE= on the open and then you wont have to include the PRODUCT and COMPANY in the REC= clause and it will only return the PRODUCTDES and SKU fields.

If this won't work for you then using your own SQL is the best option.

Yes you can specify a single field or multiple fields instead of just * for all fields.

The thing you have to keep in mind is the SELECT directive accepts an iolist which is a sequential list of field names. You can call the fields to return the data in whatever you want what matters is not the name but the order. You can use an * in the iolist to skip fields.

So if you had a table with the following fields ID$, Name$, Address$, YTDSales

You can do SELECTs like these

SELECT curID$ FROM "mytable" WHERE curID$="000042"
SELECT CurID$,*,*,YTDSales FROM "mytable" WHERE YTDSales > 100000

If you want to use a field in a WHERE you will need to include that field in the iolist.

There won't be a performance difference between SELECT * FROM "mytable" and SELECT id$ FROM "myTable"  in both cases the whole record is returned. The only difference is one the record is parsed into the files/tables iolist in the other it is parsed into the supplied iolist which may just be some of the fields.

If speed is your goal I would suggest making sure you are using PxPlus 2018 which has many SELECT optimizations that will make sure we will only read back the records we need instead of all of the records. If you are connecting to an external DB then you can also look at using WRITE RECORD (chan) "SQL Code". This why you can design the optimal SQL query for your purpose and use that. You get the results of your SQL query via a READ RECORD (chan) sql_results$ .

If PxPlus 2018 is closing without warning that means it or a library it has called has crashed. Since you are using a SELECT on a external database when it happens I suspect there is an issue with the optimized SELECT logic in PxPlus 2018.

I suggest you create a support ticket for this issue.

I also suggest that you include in the support ticket:

• Info about what external database you are connecting to.
• What PxPlus interface are you using to connect to it i.e. [ODB], [ADO], etc.
• A description of the table and keys you are doing the select on, maybe even a CREATE TABLE directive for the table if possible.
• The SELECT code it crashes on.

 

I am not to sure what you mean when you say "convert data files in the data dictionary to sql".

The PxPlus SQL ODBC driver and PxPlus SQL Server products are used to access PxPlus data file from third party applications like MS Excel using SQL statements i.e. "SELECT * FROM invoices".

The PxPlus SQL ODBC driver is used when the PxPlus data and the third party application are on the same machine. The PxPlus SQL ODBC driver and the PxPlus SQL Server are both used when the PxPlus data and the third party application are on different machines.

If this is what you are trying to do then you need to look at the documentation for instructions on how to configure and use:

https://manual.pvxplus.com/?odbc/configuration_procedures.htm
https://manual.pvxplus.com/?odbc/using_odbc_driver.htm

If what you want to do is convert your PxPlus data into relational database tables like for example MS SQL Server tables then you would not use those products at all. What you would do is use the [ODB] or [ADO] special file interface to connect to the relational database and you could create the table and copy in the data from your PxPlus data via a PxPlus program.

https://manual.pvxplus.com/?command_tags/odb.htm
https://manual.pvxplus.com/?command_tags/ADO.htm

To create the table you would do a WRITE RECORD (odb_chan) "CREATE TABLE ...". To make this easier you can from the data dictionary maintenance panel go to the utilities menu and select generate external and then select SQL Create Table. This will give you a SQL CREATE TABLE command for the current table selected in the data dictionary maintenance panel.

To copy the data in you could just do a loop that reads a record from the PxPlus data file and then does a WRITE RECORD (odb_chan) "INSERT INTO ..."

It is unlikely that the activation being for version 11 has anything to do with the PxPlus crash you are seeing. I would look at other differences between the working old server and the problem new server. It is possible that other differences between the two systems is at fault here. For example, firewall, permissions, or OS libraries.