ODBC, DBI Column Types Won't Bind? 
Author Message
 ODBC, DBI Column Types Won't Bind?

I can't seem to get the column types to bind.  Everything from the
originating source wants to be a varchar.  I have to use
MSSQL 'convert' feature to convert the strings back into float.

Works fine otherwise.

Thanks in advance.

===================================================

use DBI;

$db2 = DBI->connect('dbi:ODBC:MSSQL', 'user', 'password', { RaiseError
=> 1});

$db2->do("DROP TABLE SIS");

$db2->do(qq{CREATE TABLE "dbo"."SIS" (
"STUDENT_ID" varchar (9) NOT NULL ,
"NAME" varchar (32) NULL ,
"RESIDENCY" varchar (2) NULL ,
"INFO_RELEASE_FLAG" varchar (2) NULL ,
"TERM_CODE" varchar (3) NULL ,
"GENDER" varchar (1) NULL ,
"FOREIGN_STUDENT_CODE" varchar (1) NULL ,
"DORM" varchar (5) NULL ,
"BIRTH_DT" varchar (8) NULL ,
"CITIZENSHIP" varchar (2) NULL ,
"ETHNIC_ORIGIN" varchar (1) NULL ,
"COLLEGE" varchar (2) NULL ,
"PRIMARY_MAJ1" varchar (4) NULL ,
"PRIMARY_MAJ2" varchar (4) NULL ,
"PRIMARY_MNR1" varchar (4) NULL ,
"CLASS_CODE" varchar (3) NULL ,
"CUMGPA" "float" NULL ,
CONSTRAINT "PrimaryKey" PRIMARY KEY  NONCLUSTERED
(
  "STUDENT_ID"
)
)

Quote:
});

$db1 = DBI->connect('dbi:ODBC:UIS', 'user', 'password', { RaiseError =>
1});
$st1 = $db1->prepare("SELECT ZSIS_AA1.STUDENT_ID, ZSIS_AA1.NAME,
ZSIS_AA1.RESIDENCY, ZSIS_AA1.INFO_RELEASE_FLAG, ZSIS_RT1.TERM_CODE,
ZSIS_AA1.GENDER, ZSIS_AA1.FOREIGN_STUDENT_CODE, ZSIS_AA1.DORM,
ZSIS_AA1.BIRTH_DT, ZSIS_AA1.CITIZENSHIP, ZSIS_AA1.ETHNIC_ORIGIN,
ZSIS_RT1.COLLEGE,ZSIS_RT1.PRIMARY_MAJ1, ZSIS_RT1.PRIMARY_MAJ2,
ZSIS_RT1.PRIMARY_MNR1, ZSIS_RT1.CLASS_CODE, ZSIS_RP3.GPA FROM
ZSIS_AA1,ZSIS_RT1,ZSIS_RP1,ZSIS_RP3 WHERE ZSIS_AA1.STUDENT_ID =
ZSIS_RT1.STUDENT_ID AND ZSIS_AA1.STUDENT_ID = ZSIS_RP1.STUDENT_ID AND
ZSIS_AA1.STUDENT_ID = ZSIS_RP3.STUDENT_ID AND ZSIS_RT1.CAREER =
ZSIS_RP3.CAREER AND ZSIS_RT1.TERM_CODE ='$TERM_CODE' AND
ZSIS_AA1.HONORS_ACTIVE='Y' AND ZSIS_RT1.CAREER='U'");
$st1->execute();
my
($f01,$f02,$f03,$f04,$f05,$f06,$f07,$f08,$f09,$f10,$f11,$f12,$f13,$f14,$
 f15,$f16,$f17);
$rv = $st1->bind_columns
(\($f01,$f02,$f03,$f04,$f05,$f06,$f07,$f08,$f09,$f10,$f11,$f12,$f13,$f14
 ,$f15,$f16,$f17));

my $st2 = $db2->prepare(qq!INSERT INTO SIS (
STUDENT_ID, NAME, RESIDENCY, INFO_RELEASE_FLAG, TERM_CODE, GENDER,
FOREIGN_STUDENT_CODE, DORM, BIRTH_DT, CITIZENSHIP, ETHNIC_ORIGIN,
COLLEGE, PRIMARY_MAJ1, PRIMARY_MAJ2, PRIMARY_MNR1, CLASS_CODE, CUMGPA )
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,convert(float,?))!);

while($st1->fetch()) {
print "$f01\n";
$st2->execute
($f01,$f02,$f03,$f04,$f05,$f06,$f07,$f08,$f09,$f10,$f11,$f12,$f13,$f14,$
 f15,$f16,$f17);

Quote:
}

$db1->disconnect;
$db2->disconnect;

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. MS Access, Linux, DBI, DBD::ODBC, Easysoft's ODBC-ODBC Bridge

2. Fwd: DBI Problem getting column data type

3. DBI Problem getting column data type

4. DBI how to determine column type

5. DBI-ODBC-Problem: Binding parameters to SELECT

6. DBI-ODBC-Problem: Binding parameters to SELECT

7. DBI::ODBC, Win32 Access97 and Data Types

8. DBD:DBI won\t connect to ODBC db when script run from web server

9. Problem DBI+ODBC on Win NT

10. Bind won't take variables

11. MSSQL, ODBC Bind & Column Types?

12. Odbc won't work for database on network

 

 
Powered by phpBB® Forum Software