Most Liked Content

you can set the dbname for sqldefault in the connection string in the format sqlusername/sqlpassword/dbname

Yay, bravo Rob & Kat.  I'm too old to cope with games but I have pointed my teenagers at this.

Hi Rob,

 

Thanks for the information. No, we run on Windows which I think is OK for Python. I will get one of our .NET developers to take a look.

 

Thanks

Ross

Hi Rob,

 

To add 0.02:

 

C-ISAM certainly is more dependable.  Using Pro-ISAM only for ProIV's own bootstrap files is a rational objective.  And you might be able to exploit the more-well-known nature of C-ISAM to get SQL access direct to your data, if indeed you're not doing that already.   (Although I'm not sure if there are 'free' solutions out there for that)

 

ProIV actually had support for C-ISAM with variable length records (external file type CISAMV), however, in C-ISAM that worked by putting data into the index (.idx) and so compromised the reliability/recoverability.  In any case I think that support might have been a specially licenced option.

 

It's been a long time since I looked at this stuff but I am mildly concerned about your choice of C-FLT as a data type, expecially for financial data.

 

C-FLT directly represents the C programming 'float' type, which is a 'low' precision binary floating point type.

Because it's a binary floating point type it doesn't accurately represent decimal fractions (e.g. cents)

The maximum precision is likely to be 9 decimal digits total (maybe OK, but wouldn't be in banking etc.)

The storage representation will be machine-specific, whereas C-ISAM can store data independently of chip architectures.

 

For these reasons, I think C-ISAM has always suggested/recommended using the DECIMAL (C-DEC) type and, personally, I would recommend that too.

 

Cheers,  Richard

 

PS.  I noticed something seems slightly wrong with some links on your own main www page - stuff is linked to localhost:10080 ?

ALPHA and C-FLT we settled on . because we have been using c-flt for a while in our invoicing files.

How old is it?  Well, very old... feel free to laugh:

 04/26/16              McDonnell Information Systems                CAT//PTS_TR
                                                                         *
                                                      **    *          **
   pppppppppppppp   nnnnnnnnnnnnn     oooooooooooooo  **   **         **
   PPPPPPPPPPPPPPPP RRRRRRRRRRRRRRR  OOOOOOOOOOOOOOOO **   **        **
   PPP          PPP RRR         RRR  OOO          OOO **   **       **
   PPPppppppppppPPP RRRnnnnnnnnnRRR  OOO          OOO **   **      **
   PPPPPPPPPPPPPP   RRRRRRRRRRRRR    OOO          OOO **   **     **
   PPP              RRR        RRR   OOO          OOO **   **    **
   PPP              RRR         RRR  OOOooooooooooOOO **   **   **
   PPP              RRR          RRR  OOOOOOOOOOOOOO  **   ** **
                                                      *     **

 Version : 2.2000  Revision : r02.3.0 May 09, 94 Bootstrap Revision : 2.2.007

Hi Joy,

 

It's quite simple. This is what I would do for each file.

 

1. Copy the ProISAM ProIV filedef to a file with say a small 's' appended to the name. E.g. GL_MSTR to GL_MSTRs.

2. In the file definition header change GL_MSTRs to file type SQLSERVE. Set the physical name as GL_MSTR. You will also have to go into the storage tab and set the Ext Type, format and alternate name for each field. Alpha fields are ALPHA and numeric fields are NUMBER. For NUMBER the format is <total field length including precision>.<precision>. If the number can be a negative append the format with an "S". E.g. S8.2 (can store a max of 999999.99-)

3. In the file definition window use the SQL icons to create and save the script to a text file for GL_MSTRs.

4. In SQL Server Management Studio open the script previously created and execute it in the right database to create the table.

5. Create an update function that reads all of GL_MSTR in look mode and adds to GL_MSTRs in add mode.

6. Delete file definition GL_MSTR.

7. Rename file definition GL_MSTRs as GL_MSTR.

8. Regen functions that use GL_MSTR.

 

You will need to update your c:\windows\pro4v8.ini file with the appropriate SQL database settings. You will also need to create an ODBC driver on the server that has the ProIV kernel; it needs to point to the SQL Server database.

 

When I did this for our system in 2003 I automated the process by writing a series of functions that used the filedef bootstrap files to create one SQL table creation script file. I also wrote a function that automated the file renaming. If your system is large you may want to consider this route.

 

A word of caution with moving to SQL Server. Depending on how your system is written/structured you may encounter a lot of table locking. This is especially true for control files where you say get the next available invoice number. In ProISAM you can have a logical update that gets and increments the number. After the record is written it is unlocked and other sessions can read and update it. This is not true for SQL tables. Typically you will have to either exit the function to commit the records or execute a commit command in logic. Executing the commit command can have unwanted consequences with other tables you are processing. Because of this we continue to maintain our control files in ProISAM.

 

Hope this helps!

 

Regards

Ross

Agreed, but the values that can be selected for both the variables (which are given meaningful names for the purpose of display) and the selection values are table driven and are validated. The only way that SQL injection could occur is if the DBA did it himself.

Obligatory Little Bobby Tables reference:

 

https://xkcd.com/327/

 

The serious point to remember is that using dynamic SQL enables SQL injection attacks, so you should always put in place appropriate sanitization of the variable text you are splicing into your SQL, otherwise bad things can happen to good people.

Company: Sertec Group Holdings - an automotive supplier.

Location: Coleshill (near Birmingham), UK

Position: System Developer (Permanent) to join a team of 3, developing and supporting our in-house ERP System

We develop in ProIV Developer (VIP) in ProIV version 7 over CIsam files on a Linux platform
Knowledge of Java would be an advantage but is not essential
Competitive salary based on experience

Please email your CV to kelvin.rapley@sertec.co.uk

test

Along the lines of what Bill is saying:

Here is a global logic that makes dealing with csv files much nicer

PARSE

A PARMS($$STRING, $DELIMITER)
   
   #DELIMIT = INDEX($$STRING, $DELIMITER)
   IF #DELIMIT = 0 THEN
      $$STRING = ''
      $$PARSED_STRING = ''
      STEP 1
   ENDIF
   $$PARSED_STRING = $$STRING(1, #DELIMIT - 1)
   $$STRING = $$STRING(#DELIMIT + LEN($DELIMITER), LEN($$STRING))
   STEP 1

1  RETURN($$PARSED_STRING)

PARSE is destructive. Assuming that your file looks like "name", "address 1", "address 2"

You could simply code as follows

$NAME = PARSE($$ASCII_TEXT, ',')
$$ADDRESS1 = PARSE($$ASCII_TEXT, ',')
$$ADDRESS2 = PARSE($$ASCII_TEXT, ',')

We also have a separate global logic REPLACE for character substitution

So, to finish the job,

REPLACE($NAME, '"','')
REPLACE($$ADDRESS1, '"', '')
REPLACE($$ADDRESS2, '"', '')

hth,

Joseph

Hi Stef,

 

The maximum ProISAM record size is now -r16000 with a maximum file size of 1.9 GB. We started using this size in version 7. See help topic 720111 in the 8.2.9.58 online documentation.

 

Regards

Ross