IBM i Access ODBC driver connection strings

AS/400

Standard

Driver={IBM i Access ODBC Driver};System=mySystem;Uid=myUser;Pwd=myPassword;

Variant for reserved error (-7778)

Try this if you encounter a "runtime error 3000; reserved error (-7778); there is no message for this error" message.

Driver={IBM i Access ODBC Driver};System=mySystem;Uid=myUser;Pwd=myPassword;MGDSN=0;

Read-only connection

Enbales SELECT statements only. No UPDATE etc.

Driver={IBM i Access ODBC Driver};System=mySystem;UID=myUser;PWD=myPassword;CONNTYPE=2;

Default is CONNTYPE=0 meaning all SQL statements allowed. CONNTYPE=1 means SELECT and CALL statements is allowed.

Disable compression

Driver={IBM i Access ODBC Driver};System=mySystem;UID=myUser;PWD=myPassword;COMPRESSION=0;

Default is compression=1 meaning compression is enabled.

Disable SELECT prefetching

Use this connection string to disable prefetching for SELECT statements.

Driver={IBM i Access ODBC Driver};System=mySystem;UID=myUser;PWD=myPassword;PREFETCH=0;

Default behaviour is that prefetch is enabled (PREFETCH=1) which will increase performance for SELECT statements when accessing the initial rows.

Specify sorting language (collation)

Example using spanish (ESP) language for sorting.

Driver={IBM i Access ODBC Driver};System=mySystem;UID=myUser;PWD=myPassword;SORTTYPE=2;LANGUAGEID=ESP;

Language codes: AFR, ARA, BEL, BGR, CAT, CHS, CHT, CSY, DAN, DES, DEU, ELL, ENA, ENB, ENG, ENP, ENU, ESP, EST, FAR, FIN, FRA, FRB, FRC, FRS, GAE, HEB, HRV, HUN, ISL, ITA, ITS, JPN, KOR, LAO, LVA, LTU, MKD, NLB, NLD, NON, NOR, PLK, PTB, PTG, RMS, ROM, RUS, SKY, SLO, SQI, SRB, SRL, SVE, THA, TRK, UKR, URD, VIE

Case sensitive sorting (collation)

Driver={IBM i Access ODBC Driver};System=mySystem;UID=myUser;PWD=myPassword;SORTTYPE=2;LANGUAGEID=ESP;SORTWEIGHT=1;

Default SORTWEIGHT value is 0 meaning case insensitive sorting.