ODBC iSeries DSN SQL String Format

jamestieman
jamestieman used Ask the Experts™
on
I have a VB.NET application that uses the ODBCConnection object to connect to a DSN that is defined using the iSeries Access ODBC driver.  Right now, I can connect  using the DSN name "AS400", like this: dsn=AS400 as the connection string.  If a connection using the DSN is  established in Excel first, Excel prompts for the credentials and then I can connect to the AS400.  (How to provide credentials from my app is another question in my questions)
 I need to create an update query and I don't know how to format it because I don't know what is considered the table name?  Is this correct?
UPDATE Z3ODSPL SET DLUSPLIST = 1 WHERE DLUSPLIC = 300001002
Am I supposed to use the Database Library - TR1491AFWM, somewhere?
The AS400 people created a table and gave me this information.
Database file name - Z3ODSPL
Database Library - TR1491AFWM
Field Name - DLUSPLIC
FMT - P
Start -1
Lngth - 10
Dec - 00
Key -
Field Description - Plate Number
Field Name - DLUSPLINO
FMT - P
Start -7
Lngth - 4
Dec - 00
Key -
Field Description - Light Number
Field Name - DLUSPLIST
FMT - P
Start -10
Lngth - 1
Dec - 00
Key -
Field Description - Status
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Application Consultant
Commented:
Hi James,

The format for update is :

Update TR1491AFWM/Z3ODSPL SET DLUSPLIST = 1 WHERE DLUSPLIC = 300001002

or

Update TR1491AFWM.Z3ODSPL ........

Depending on the setting you use,

Connecting to the AS400 with DSN name "AS400", is only possible if the DNS name is "AS400". Try to "Ping AS400" if you get response, it's the right name, otherwise, ask the opperator or an other technical guy, the name of the 400.


Good luck.
Murph
Gary PattersonVP Technology / Senior Consultant

Commented:
I think murph has confused dns and dsn. When you set up a dsn in windows odic administrator, you can name it whatever you like. The dsn name has nothing to do with dns. Of course you have to have a valid dns name or ip address set up as the system name in the odic dns entry.
Theo KouwenhovenApplication Consultant

Commented:
Hi Gary,

Yep you are right, I was to quick and so my assumption was wrong

Thanks for correcting me,
Murph
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Author

Commented:
Bottom line is, I have to use the Library Name + File Name using a "." or "/" where I normally just used a table name and  the field names don't require anything other than the field name in the rest of the query.

I'm going to remote into my client in Memphis and give it a try.

Author

Commented:
I'm getting a Token Error in an UPDATE command.  Here is the ODBC log.  
SQL.LOG
What I read in SQL.LOG is that there is a semi-colon (";") at the end of the statement. Try removing the semi-colon and let us know the result.

A semi-colon is normally the statement terminator when a series of statements is being processed in a script or by the SQL compiler. Single statements generally have no need of a statement terminator.

If the error goes away, it's possible that a new error will appear due to other elements such as an inability to locate the table without a library qualifier.

Tom

Author

Commented:
I don't think I handled this question very well.  I think I should have asked about the Token in a separate question.  I didn't think about a second person answering.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial